Schema with triggers

schema.sql

CREATE SEQUENCE IF NOT EXISTS ids
      START 1;


CREATE TABLE IF NOT EXISTS payments (
  id BIGINT NOT NULL PRIMARY KEY DEFAULT nextval('ids'),
  amount BIGINT NOT NULL CHECK((amount > 0) AND (amount <= 100)),
  status TEXT DEFAULT 'failed' CONSTRAINT status_validity_check CHECK (type IN ('failed', 'succeeded', 'pending'))
);

CREATE TABLE IF NOT EXISTS policies (
  id BIGINT NOT NULL PRIMARY KEY DEFAULT nextval('ids'),
  payment_id BIGINT  REFERENCES payments(id),
  type TEXT DEFAULT 'monthly' CONSTRAINT policy_type_check CHECK (type IN ('monthly', 'quarterly', 'annual'))
);

CREATE OR REPLACE FUNCTION check_payment_status() RETURNS trigger language plpgsql as $$
  BEGIN
      IF (NEW.payment_id IS NULL)
        RETURN NEW;
      ELSE IF ((SELECT status FROM payments WHERE id = NEW.payment_id) <> 'succeeded') 
        RAISE EXCEPTION 'can only bind succeeded payments';
      ELSE
        RETURN NEW;
      END IF;
  END $$;
  
  CREATE TRIGGER check_payment_status
    BEFORE UPDATE OF payment_id ON policies
    FOR EACH ROW
    EXECUTE PROCEDURE check_payment_status();