How to postpone firing a trigger

19 janeiro 2022, 17:33 • Francisco Regateiro

In order to help with project development (regarding the implementation of mandatory specialization), the example below follows the exercise 3 c) from Lab. 7, and shows how to postpone firing trigger tg_verify_account: 

CREATE OR REPLACE FUNCTION verify_account()
RETURNS TRIGGER AS
$$
BEGIN
  IF NEW.customer_name NOT IN (SELECT customer_name FROM depositor) THEN
      RAISE EXCEPTION 'The client % does not have an account in the bank.', new.customer_name;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

 

DROP TRIGGER IF EXISTS tg_verify_account ON borrower;

CREATE CONSTRAINT TRIGGER tg_verify_account
AFTER INSERT ON borrower DEFERRABLE
FOR EACH ROW EXECUTE PROCEDURE verify_account();

INSERT INTO customer VALUES('Alberto','Rua 1','Cascais');

START TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
-- inserting in borrower before depositor would not be possible without deferring
INSERT INTO borrower VALUES('Alberto', 'L-23');
INSERT INTO depositor VALUES('Alberto', 'A-222');
COMMIT; -- trigger will be fired here

DELETE FROM borrower WHERE customer_name = 'Alberto';
DELETE FROM depositor WHERE customer_name = 'Alberto';
DELETE FROM customer WHERE customer_name = 'Alberto';