Postgresql create trigger deferrable not working with referencing new table

Ali Elkhateeb 0
Viewed 28

I am trying to create a statement-level trigger that is deferred, and also reference the new transition table

So far I have tried this (on table rows):

CREATE CONSTRAINT TRIGGER check_rows
AFTER INSERT OR UPDATE OR DELETE 
ON rows 
REFERENCING NEW TABLE AS newtable 
DEFERRABLE INITIALLY DEFERRED 
FOR EACH STATEMENT 
EXECUTE FUNCTION check_rows();

But I'm getting the error syntax error at or near "REFERENCING"

What are my eyes missing here?

triggers postgresql
1 Answers

DEFERRABLE must be before REFERENCING, but that is academic. As the documentation says,

The REFERENCING option enables collection of transition relations, which are row sets that include all of the rows inserted, deleted, or modified by the current SQL statement. [...] This option is only allowed for an AFTER trigger that is not a constraint trigger [...]

So you cannot use transition tables in a constraint trigger.

Laurenz Albe0
Related