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?

CodePudding user response：

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.