CREATE TRIGGER

​

Syntax

create_trigger ::= CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER trigger_name
                   { BEFORE | AFTER | INSTEAD OF } trigger_event [ OR ... ]
                   ON table_name
                   [ NOT DEFERRABLE | DEFERRABLE [ INITIALLY { IMMEDIATE | DEFERRED } ] ]
                   [ REFERENCING { OLD TABLE [ AS ] old_table_name | NEW TABLE [ AS ] new_table_name } [...] ]
                   [ FOR [ EACH ] { ROW | STATEMENT } ]
                   [ WHEN ( condition ) ]
                   EXECUTE FUNCTION function_name ( )

trigger_name ::= name

trigger_event ::= INSERT | UPDATE [ OF column_name [, ...] ] | DELETE | TRUNCATE

table_name ::= [schema.]name

condition ::= expression

function_name ::= [schema.]name

• Trigger timing:
  • BEFORE - executes before the triggering event
  • AFTER - executes after the triggering event
  • INSTEAD OF - replaces the triggering event (views only)
• Trigger events:
  • INSERT, UPDATE, DELETE, TRUNCATE
  • Multiple events can be combined with OR
  • UPDATE OF column_name - fires only when specific columns are updated
• Trigger level:
  • FOR EACH ROW - fires once for each affected row (default)
  • FOR EACH STATEMENT - fires once for the entire SQL statement
• WHEN condition: Optional condition that must be true for trigger to fire
  • Supports IS DISTINCT FROM / IS NOT DISTINCT FROM comparisons
  • Can reference OLD and NEW row values
• Constraint triggers:
  • CREATE CONSTRAINT TRIGGER for triggers that can be deferred
  • DEFERRABLE with INITIALLY IMMEDIATE or INITIALLY DEFERRED
• Transition tables:
  • REFERENCING OLD TABLE AS name - captures deleted/old rows for statement-level triggers
  • REFERENCING NEW TABLE AS name - captures inserted/new rows for statement-level triggers
• Schema-qualified names: Tables and functions can be in specific schemas
• Function execution: Triggers execute functions that return TRIGGER type

​

Canonical Format

CREATE OR REPLACE TRIGGER trigger_name
    { BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE | TRUNCATE } [ OR ... ] ON [schema.]table_name
    FOR EACH { ROW | STATEMENT }
    [ WHEN (condition) ]
    EXECUTE FUNCTION function_name();

• Always uses CREATE OR REPLACE TRIGGER for modifications
• Events are ordered consistently: INSERT, UPDATE, DELETE, TRUNCATE
• Multi-line formatting with proper indentation for readability
• Explicit FOR EACH clause (ROW is default if not specified)
• WHEN conditions are properly parenthesized
• Function names include parentheses even when no arguments
• Schema qualifiers only included when table is in different schema than target
• For DROP operations: DROP TRIGGER IF EXISTS trigger_name ON table_name;