CREATE TRIGGER — define a new trigger


    ON table_name
    [ FROM referenced_table_name ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

where event can be one of:

    UPDATE [ OF column_name [, ... ] ]


CREATE TRIGGER creates a new trigger. The trigger will be associated with the specified table, view, or foreign table and will execute the specified function function_name when certain operations are performed on that table.





The following table summarizes which types of triggers may be used on tables, views, and foreign tables:

BEFOREINSERT/UPDATE/DELETETables and foreign tablesTables, views, and foreign tables
AFTERINSERT/UPDATE/DELETETables and foreign tablesTables, views, and foreign tables




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 feature lets the trigger see a global view of what the statement did, not just one row at a time. This option is only allowed for an AFTER trigger that is not a constraint trigger; also, if the trigger is an UPDATE trigger, it must not specify a column_name list. OLD TABLE may only be specified once, and only for a trigger that can fire on UPDATE or DELETE; it creates a transition relation containing the before-images of all rows updated or deleted by the statement. Similarly, NEW TABLE may only be specified once, and only for a trigger that can fire on UPDATE or INSERT; it creates a transition relation containing the after-images of all rows updated or inserted by the statement.

SELECT does not modify any rows so you cannot create SELECT triggers. Rules and views may provide workable solutions to problems that seem to need SELECT triggers.

Refer to Chapter 38 for more information about triggers.







One of INSERT, UPDATE, DELETE, or TRUNCATE; this specifies the event that will fire the trigger. Multiple events can be specified using OR, except when transition relations are requested.


UPDATE OF column_name1 [, column_name2 ... ]

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command or if one of the listed columns is a generated column that depends on a column that is the target of the UPDATE.

INSTEAD OF UPDATE events do not allow a list of columns. A column list cannot be specified when requesting transition relations, either.


The name (optionally schema-qualified) of the table, view, or foreign table the trigger is for.






This keyword immediately precedes the declaration of one or two relation names that provide access to the transition relations of the triggering statement.


This clause indicates whether the following relation name is for the before-image transition relation or the after-image transition relation.


The (unqualified) name to be used within the trigger for this transition relation.





INSTEAD OF triggers do not support WHEN conditions.

Currently, WHEN expressions cannot contain subqueries.




In the syntax of CREATE TRIGGER, the keywords FUNCTION and PROCEDURE are equivalent, but the referenced function must in any case be a function, not a procedure. The use of the keyword PROCEDURE here is historical and deprecated.





Use DROP TRIGGER to remove a trigger.


There are a few built-in trigger functions that can be used to solve common problems without having to write your own trigger code; see Section 9.28.



In some cases it is possible for a single SQL command to fire more than one kind of trigger. For instance an INSERT with an ON CONFLICT DO UPDATE clause may cause both insert and update operations, so it will fire both kinds of triggers as needed. The transition relations supplied to triggers are specific to their event type; thus an INSERT trigger will see only the inserted rows, while an UPDATE trigger will see only the updated rows.

Row updates or deletions caused by foreign-key enforcement actions, such as ON UPDATE CASCADE or ON DELETE SET NULL, are treated as part of the SQL command that caused them (note that such actions are never deferred). Relevant triggers on the affected table will be fired, so that this provides another way in which a SQL command might fire triggers not directly matching its type. In simple cases, triggers that request transition relations will see all changes caused in their table by a single original SQL command as a single transition relation. However, there are cases in which the presence of an AFTER ROW trigger that requests transition relations will cause the foreign-key enforcement actions triggered by a single SQL command to be split into multiple steps, each with its own transition relation(s). In such cases, any statement-level triggers that are present will be fired once per creation of a transition relation set, ensuring that the triggers see each affected row in a transition relation once and only once.

Statement-level triggers on a view are fired only if the action on the view is handled by a row-level INSTEAD OF trigger. If the action is handled by an INSTEAD rule, then whatever statements are emitted by the rule are executed in place of the original statement naming the view, so that the triggers that will be fired are those on tables named in the replacement statements. Similarly, if the view is automatically updatable, then the action is handled by automatically rewriting the statement into an action on the view's base table, so that the base table's statement-level triggers are the ones that are fired.

Creating a row-level trigger on a partitioned table will cause identical triggers to be created in all its existing partitions; and any partitions created or attached later will contain an identical trigger, too. If the partition is detached from its parent, the trigger is removed. Triggers on partitioned tables may not be INSTEAD OF.

Modifying a partitioned table or a table with inheritance children fires statement-level triggers attached to the explicitly named table, but not statement-level triggers for its partitions or child tables. In contrast, row-level triggers are fired on the rows in affected partitions or child tables, even if they are not explicitly named in the query. If a statement-level trigger has been defined with transition relations named by a REFERENCING clause, then before and after images of rows are visible from all affected partitions or child tables. In the case of inheritance children, the row images include only columns that are present in the table that the trigger is attached to. Currently, row-level triggers with transition relations cannot be defined on partitions or inheritance child tables.



CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    EXECUTE FUNCTION check_account_update();


CREATE TRIGGER check_update
    BEFORE UPDATE OF balance ON accounts
    EXECUTE FUNCTION check_account_update();


CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
    EXECUTE FUNCTION check_account_update();


    AFTER UPDATE ON accounts
    EXECUTE FUNCTION log_account_update();


CREATE TRIGGER view_insert
    EXECUTE FUNCTION view_insert_row();

Execute the function check_transfer_balances_to_zero for each statement to confirm that the transfer rows offset to a net of zero:

CREATE TRIGGER transfer_insert
    AFTER INSERT ON transfer
    EXECUTE FUNCTION check_transfer_balances_to_zero();

Execute the function check_matching_pairs for each row to confirm that changes are made to matching pairs at the same time (by the same statement):

CREATE TRIGGER paired_items_update
    AFTER UPDATE ON paired_items
    EXECUTE FUNCTION check_matching_pairs();

Section 38.4 contains a complete example of a trigger function written in C.


The CREATE TRIGGER statement in PostgreSQL implements a subset of the SQL standard. The following functionalities are currently missing:

  • While transition table names for AFTER triggers are specified using the REFERENCING clause in the standard way, the row variables used in FOR EACH ROW triggers may not be specified in a REFERENCING clause. They are available in a manner that is dependent on the language in which the trigger function is written, but is fixed for any one language. Some languages effectively behave as though there is a REFERENCING clause containing OLD ROW AS OLD NEW ROW AS NEW.

  • The standard allows transition tables to be used with column-specific UPDATE triggers, but then the set of rows that should be visible in the transition tables depends on the trigger's column list. This is not currently implemented by PostgreSQL.






CREATE CONSTRAINT TRIGGER is a PostgreSQL extension of the SQL standard.