In SQL coding can DEFERRABLE be used in TRIGGER ? How does DEFERRABLE work?

Question

Can the DEFERRABLE be used in a trigger ?

And how does the concept of DEFERRABLE works ?

It's during the whole transaction then it checks the constraint at the end of all the operations ?

Answer 1

It looks like you mean PostgresSql. The concept of a DEFERRABLE CONSTRAINT is one which may be temporarily invalidated during the course of a transaction (with the SET CONSTRAINTS ... DEFERRED; command), but the constraints must again be valid by the time the transaction is committed.

For example, below, there is a FOREIGN KEY constraint enforcing referential integrity from Table2.Table1ID to Table1.ID:

CREATE TABLE Table1
(
  ID INT NOT NULL,
  Name VARCHAR(50),
  CONSTRAINT PK_Table1 PRIMARY KEY(ID)
);

CREATE TABLE Table2
(
  ID INT NOT NULL,
  Table1ID INT NOT NULL,
  Name VARCHAR(50),
  CONSTRAINT PK_Table2 PRIMARY KEY(ID),
  CONSTRAINT FK_Table2_Table1 FOREIGN KEY(Table1ID) REFERENCES Table1(ID) DEFERRABLE
);

Normally, the below insert would fail, if there is no row with Table1.ID = 2:

INSERT INTO TABLE2(ID, Table1Id, Name) VALUES (2, 2, 'Foreign Key Violation');

However, because the CONSTRAINT is defined as DEFERRABLE (and we've allowed deferred constraints in the transaction, below), it means we may temporarily violate the constraint, provided that the constraint is met by the time the transaction is committed. In the example below, we address the foreign key violation by inserting an additional row into table 1 to pacify the referential integrity constraint.

BEGIN TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO Table2(ID, Table1Id, Name) VALUES (2, 2, 'Foreign Key Violation Allowed');
INSERT INTO Table1(ID, Name) VALUES (2, 'Fix the Violation');
COMMIT TRANSACTION;

SqlFiddle of a DEFERRED RI insert here

With regards to Triggers, DEFERRED can only be used with CONSTRAINT TRIGGERS, but the same principal above applies.