Search code examples
postgresqlplpgsql

How to create "CONSTRAINT TRIGGER" Postgres on BEFORE event

Postgres Version: PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit

I read PostgreSQL documentation about how to create a CONSTRAINT TRIGGER.

and this my query:

CREATE CONSTRAINT TRIGGER my_table_trigger
BEFORE INSERT 
ON my_table
DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW 
EXECUTE FUNCTION my_table_trigger_function();

when im run above query, it produce error says:

SQL Error [42601]: ERROR: syntax error at or near "BEFORE"

After looking for solution i found this old documentation. On that doc it says that CONSTRAINT TRIGGER only support AFTER event. Then i modify my query change BEFORE keyword to AFTER and query run successfully.

My qestion is did CONSTRAINT TRIGGER only support AFTER event, and did not support BEFORE event ?

Solution

  • The documentation for CREATE TRIGGERS in version 15 (the current one, which you're using) also says:

    Constraint triggers must be AFTER ROW triggers on plain tables (not foreign tables).

    It also says (around BEFORE, AFTER, INSTEAD OF):

    A constraint trigger can only be specified as AFTER.

    So, it doesn't support BEFORE triggers.