1CREATE TRIGGER(7)        PostgreSQL 13.4 Documentation       CREATE TRIGGER(7)
2
3
4

NAME

6       CREATE_TRIGGER - define a new trigger
7

SYNOPSIS

9       CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
10           ON table_name
11           [ FROM referenced_table_name ]
12           [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
13           [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
14           [ FOR [ EACH ] { ROW | STATEMENT } ]
15           [ WHEN ( condition ) ]
16           EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )
17
18       where event can be one of:
19
20           INSERT
21           UPDATE [ OF column_name [, ... ] ]
22           DELETE
23           TRUNCATE
24

DESCRIPTION

26       CREATE TRIGGER creates a new trigger. The trigger will be associated
27       with the specified table, view, or foreign table and will execute the
28       specified function function_name when certain operations are performed
29       on that table.
30
31       The trigger can be specified to fire before the operation is attempted
32       on a row (before constraints are checked and the INSERT, UPDATE, or
33       DELETE is attempted); or after the operation has completed (after
34       constraints are checked and the INSERT, UPDATE, or DELETE has
35       completed); or instead of the operation (in the case of inserts,
36       updates or deletes on a view). If the trigger fires before or instead
37       of the event, the trigger can skip the operation for the current row,
38       or change the row being inserted (for INSERT and UPDATE operations
39       only). If the trigger fires after the event, all changes, including the
40       effects of other triggers, are “visible” to the trigger.
41
42       A trigger that is marked FOR EACH ROW is called once for every row that
43       the operation modifies. For example, a DELETE that affects 10 rows will
44       cause any ON DELETE triggers on the target relation to be called 10
45       separate times, once for each deleted row. In contrast, a trigger that
46       is marked FOR EACH STATEMENT only executes once for any given
47       operation, regardless of how many rows it modifies (in particular, an
48       operation that modifies zero rows will still result in the execution of
49       any applicable FOR EACH STATEMENT triggers).
50
51       Triggers that are specified to fire INSTEAD OF the trigger event must
52       be marked FOR EACH ROW, and can only be defined on views.  BEFORE and
53       AFTER triggers on a view must be marked as FOR EACH STATEMENT.
54
55       In addition, triggers may be defined to fire for TRUNCATE, though only
56       FOR EACH STATEMENT.
57
58       The following table summarizes which types of triggers may be used on
59       tables, views, and foreign tables:
60
61       ┌───────────┬──────────────────────┬────────────────┬─────────────────┐
62When       Event                Row-level      Statement-level 
63       ├───────────┼──────────────────────┼────────────────┼─────────────────┤
64       │           │ INSERT/UPDATE/DELETE │ Tables and     │ Tables, views,  │
65       │           │                      │ foreign tables │ and foreign     │
66       │  BEFORE   │                      │                │ tables          │
67       │           ├──────────────────────┼────────────────┼─────────────────┤
68       │           │       TRUNCATE       │       —        │     Tables      │
69       ├───────────┼──────────────────────┼────────────────┼─────────────────┤
70       │           │ INSERT/UPDATE/DELETE │ Tables and     │ Tables, views,  │
71       │           │                      │ foreign tables │ and foreign     │
72       │  AFTER    │                      │                │ tables          │
73       │           ├──────────────────────┼────────────────┼─────────────────┤
74       │           │       TRUNCATE       │       —        │     Tables      │
75       ├───────────┼──────────────────────┼────────────────┼─────────────────┤
76       │           │ INSERT/UPDATE/DELETE │     Views      │        —        │
77       │INSTEAD OF ├──────────────────────┼────────────────┼─────────────────┤
78       │           │       TRUNCATE       │       —        │        —        │
79       └───────────┴──────────────────────┴────────────────┴─────────────────┘
80
81       Also, a trigger definition can specify a Boolean WHEN condition, which
82       will be tested to see whether the trigger should be fired. In row-level
83       triggers the WHEN condition can examine the old and/or new values of
84       columns of the row. Statement-level triggers can also have WHEN
85       conditions, although the feature is not so useful for them since the
86       condition cannot refer to any values in the table.
87
88       If multiple triggers of the same kind are defined for the same event,
89       they will be fired in alphabetical order by name.
90
91       When the CONSTRAINT option is specified, this command creates a
92       constraint trigger. This is the same as a regular trigger except that
93       the timing of the trigger firing can be adjusted using SET CONSTRAINTS
94       (SET_CONSTRAINTS(7)). Constraint triggers must be AFTER ROW triggers on
95       plain tables (not foreign tables). They can be fired either at the end
96       of the statement causing the triggering event, or at the end of the
97       containing transaction; in the latter case they are said to be
98       deferred. A pending deferred-trigger firing can also be forced to
99       happen immediately by using SET CONSTRAINTS. Constraint triggers are
100       expected to raise an exception when the constraints they implement are
101       violated.
102
103       The REFERENCING option enables collection of transition relations,
104       which are row sets that include all of the rows inserted, deleted, or
105       modified by the current SQL statement. This feature lets the trigger
106       see a global view of what the statement did, not just one row at a
107       time. This option is only allowed for an AFTER trigger that is not a
108       constraint trigger; also, if the trigger is an UPDATE trigger, it must
109       not specify a column_name list.  OLD TABLE may only be specified once,
110       and only for a trigger that can fire on UPDATE or DELETE; it creates a
111       transition relation containing the before-images of all rows updated or
112       deleted by the statement. Similarly, NEW TABLE may only be specified
113       once, and only for a trigger that can fire on UPDATE or INSERT; it
114       creates a transition relation containing the after-images of all rows
115       updated or inserted by the statement.
116
117       SELECT does not modify any rows so you cannot create SELECT triggers.
118       Rules and views may provide workable solutions to problems that seem to
119       need SELECT triggers.
120
121       Refer to Chapter 38 for more information about triggers.
122

PARAMETERS

124       name
125           The name to give the new trigger. This must be distinct from the
126           name of any other trigger for the same table. The name cannot be
127           schema-qualified — the trigger inherits the schema of its table.
128           For a constraint trigger, this is also the name to use when
129           modifying the trigger's behavior using SET CONSTRAINTS.
130
131       BEFORE
132       AFTER
133       INSTEAD OF
134           Determines whether the function is called before, after, or instead
135           of the event. A constraint trigger can only be specified as AFTER.
136
137       event
138           One of INSERT, UPDATE, DELETE, or TRUNCATE; this specifies the
139           event that will fire the trigger. Multiple events can be specified
140           using OR, except when transition relations are requested.
141
142           For UPDATE events, it is possible to specify a list of columns
143           using this syntax:
144
145               UPDATE OF column_name1 [, column_name2 ... ]
146
147           The trigger will only fire if at least one of the listed columns is
148           mentioned as a target of the UPDATE command or if one of the listed
149           columns is a generated column that depends on a column that is the
150           target of the UPDATE.
151
152           INSTEAD OF UPDATE events do not allow a list of columns. A column
153           list cannot be specified when requesting transition relations,
154           either.
155
156       table_name
157           The name (optionally schema-qualified) of the table, view, or
158           foreign table the trigger is for.
159
160       referenced_table_name
161           The (possibly schema-qualified) name of another table referenced by
162           the constraint. This option is used for foreign-key constraints and
163           is not recommended for general use. This can only be specified for
164           constraint triggers.
165
166       DEFERRABLE
167       NOT DEFERRABLE
168       INITIALLY IMMEDIATE
169       INITIALLY DEFERRED
170           The default timing of the trigger. See the CREATE TABLE
171           (CREATE_TABLE(7)) documentation for details of these constraint
172           options. This can only be specified for constraint triggers.
173
174       REFERENCING
175           This keyword immediately precedes the declaration of one or two
176           relation names that provide access to the transition relations of
177           the triggering statement.
178
179       OLD TABLE
180       NEW TABLE
181           This clause indicates whether the following relation name is for
182           the before-image transition relation or the after-image transition
183           relation.
184
185       transition_relation_name
186           The (unqualified) name to be used within the trigger for this
187           transition relation.
188
189       FOR EACH ROW
190       FOR EACH STATEMENT
191           This specifies whether the trigger function should be fired once
192           for every row affected by the trigger event, or just once per SQL
193           statement. If neither is specified, FOR EACH STATEMENT is the
194           default. Constraint triggers can only be specified FOR EACH ROW.
195
196       condition
197           A Boolean expression that determines whether the trigger function
198           will actually be executed. If WHEN is specified, the function will
199           only be called if the condition returns true. In FOR EACH ROW
200           triggers, the WHEN condition can refer to columns of the old and/or
201           new row values by writing OLD.column_name or NEW.column_name
202           respectively. Of course, INSERT triggers cannot refer to OLD and
203           DELETE triggers cannot refer to NEW.
204
205           INSTEAD OF triggers do not support WHEN conditions.
206
207           Currently, WHEN expressions cannot contain subqueries.
208
209           Note that for constraint triggers, evaluation of the WHEN condition
210           is not deferred, but occurs immediately after the row update
211           operation is performed. If the condition does not evaluate to true
212           then the trigger is not queued for deferred execution.
213
214       function_name
215           A user-supplied function that is declared as taking no arguments
216           and returning type trigger, which is executed when the trigger
217           fires.
218
219           In the syntax of CREATE TRIGGER, the keywords FUNCTION and
220           PROCEDURE are equivalent, but the referenced function must in any
221           case be a function, not a procedure. The use of the keyword
222           PROCEDURE here is historical and deprecated.
223
224       arguments
225           An optional comma-separated list of arguments to be provided to the
226           function when the trigger is executed. The arguments are literal
227           string constants. Simple names and numeric constants can be written
228           here, too, but they will all be converted to strings. Please check
229           the description of the implementation language of the trigger
230           function to find out how these arguments can be accessed within the
231           function; it might be different from normal function arguments.
232

NOTES

234       To create a trigger on a table, the user must have the TRIGGER
235       privilege on the table. The user must also have EXECUTE privilege on
236       the trigger function.
237
238       Use DROP TRIGGER (DROP_TRIGGER(7)) to remove a trigger.
239
240       A column-specific trigger (one defined using the UPDATE OF column_name
241       syntax) will fire when any of its columns are listed as targets in the
242       UPDATE command's SET list. It is possible for a column's value to
243       change even when the trigger is not fired, because changes made to the
244       row's contents by BEFORE UPDATE triggers are not considered.
245       Conversely, a command such as UPDATE ... SET x = x ...  will fire a
246       trigger on column x, even though the column's value did not change.
247
248       There are a few built-in trigger functions that can be used to solve
249       common problems without having to write your own trigger code; see
250       Section 9.28.
251
252       In a BEFORE trigger, the WHEN condition is evaluated just before the
253       function is or would be executed, so using WHEN is not materially
254       different from testing the same condition at the beginning of the
255       trigger function. Note in particular that the NEW row seen by the
256       condition is the current value, as possibly modified by earlier
257       triggers. Also, a BEFORE trigger's WHEN condition is not allowed to
258       examine the system columns of the NEW row (such as ctid), because those
259       won't have been set yet.
260
261       In an AFTER trigger, the WHEN condition is evaluated just after the row
262       update occurs, and it determines whether an event is queued to fire the
263       trigger at the end of statement. So when an AFTER trigger's WHEN
264       condition does not return true, it is not necessary to queue an event
265       nor to re-fetch the row at end of statement. This can result in
266       significant speedups in statements that modify many rows, if the
267       trigger only needs to be fired for a few of the rows.
268
269       In some cases it is possible for a single SQL command to fire more than
270       one kind of trigger. For instance an INSERT with an ON CONFLICT DO
271       UPDATE clause may cause both insert and update operations, so it will
272       fire both kinds of triggers as needed. The transition relations
273       supplied to triggers are specific to their event type; thus an INSERT
274       trigger will see only the inserted rows, while an UPDATE trigger will
275       see only the updated rows.
276
277       Row updates or deletions caused by foreign-key enforcement actions,
278       such as ON UPDATE CASCADE or ON DELETE SET NULL, are treated as part of
279       the SQL command that caused them (note that such actions are never
280       deferred). Relevant triggers on the affected table will be fired, so
281       that this provides another way in which a SQL command might fire
282       triggers not directly matching its type. In simple cases, triggers that
283       request transition relations will see all changes caused in their table
284       by a single original SQL command as a single transition relation.
285       However, there are cases in which the presence of an AFTER ROW trigger
286       that requests transition relations will cause the foreign-key
287       enforcement actions triggered by a single SQL command to be split into
288       multiple steps, each with its own transition relation(s). In such
289       cases, any statement-level triggers that are present will be fired once
290       per creation of a transition relation set, ensuring that the triggers
291       see each affected row in a transition relation once and only once.
292
293       Statement-level triggers on a view are fired only if the action on the
294       view is handled by a row-level INSTEAD OF trigger. If the action is
295       handled by an INSTEAD rule, then whatever statements are emitted by the
296       rule are executed in place of the original statement naming the view,
297       so that the triggers that will be fired are those on tables named in
298       the replacement statements. Similarly, if the view is automatically
299       updatable, then the action is handled by automatically rewriting the
300       statement into an action on the view's base table, so that the base
301       table's statement-level triggers are the ones that are fired.
302
303       Creating a row-level trigger on a partitioned table will cause
304       identical triggers to be created in all its existing partitions; and
305       any partitions created or attached later will contain an identical
306       trigger, too. If the partition is detached from its parent, the trigger
307       is removed. Triggers on partitioned tables may not be INSTEAD OF.
308
309       Modifying a partitioned table or a table with inheritance children
310       fires statement-level triggers attached to the explicitly named table,
311       but not statement-level triggers for its partitions or child tables. In
312       contrast, row-level triggers are fired on the rows in affected
313       partitions or child tables, even if they are not explicitly named in
314       the query. If a statement-level trigger has been defined with
315       transition relations named by a REFERENCING clause, then before and
316       after images of rows are visible from all affected partitions or child
317       tables. In the case of inheritance children, the row images include
318       only columns that are present in the table that the trigger is attached
319       to. Currently, row-level triggers with transition relations cannot be
320       defined on partitions or inheritance child tables.
321

EXAMPLES

323       Execute the function check_account_update whenever a row of the table
324       accounts is about to be updated:
325
326           CREATE TRIGGER check_update
327               BEFORE UPDATE ON accounts
328               FOR EACH ROW
329               EXECUTE FUNCTION check_account_update();
330
331       The same, but only execute the function if column balance is specified
332       as a target in the UPDATE command:
333
334           CREATE TRIGGER check_update
335               BEFORE UPDATE OF balance ON accounts
336               FOR EACH ROW
337               EXECUTE FUNCTION check_account_update();
338
339       This form only executes the function if column balance has in fact
340       changed value:
341
342           CREATE TRIGGER check_update
343               BEFORE UPDATE ON accounts
344               FOR EACH ROW
345               WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
346               EXECUTE FUNCTION check_account_update();
347
348       Call a function to log updates of accounts, but only if something
349       changed:
350
351           CREATE TRIGGER log_update
352               AFTER UPDATE ON accounts
353               FOR EACH ROW
354               WHEN (OLD.* IS DISTINCT FROM NEW.*)
355               EXECUTE FUNCTION log_account_update();
356
357       Execute the function view_insert_row for each row to insert rows into
358       the tables underlying a view:
359
360           CREATE TRIGGER view_insert
361               INSTEAD OF INSERT ON my_view
362               FOR EACH ROW
363               EXECUTE FUNCTION view_insert_row();
364
365       Execute the function check_transfer_balances_to_zero for each statement
366       to confirm that the transfer rows offset to a net of zero:
367
368           CREATE TRIGGER transfer_insert
369               AFTER INSERT ON transfer
370               REFERENCING NEW TABLE AS inserted
371               FOR EACH STATEMENT
372               EXECUTE FUNCTION check_transfer_balances_to_zero();
373
374       Execute the function check_matching_pairs for each row to confirm that
375       changes are made to matching pairs at the same time (by the same
376       statement):
377
378           CREATE TRIGGER paired_items_update
379               AFTER UPDATE ON paired_items
380               REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
381               FOR EACH ROW
382               EXECUTE FUNCTION check_matching_pairs();
383
384       Section 38.4 contains a complete example of a trigger function written
385       in C.
386

COMPATIBILITY

388       The CREATE TRIGGER statement in PostgreSQL implements a subset of the
389       SQL standard. The following functionalities are currently missing:
390
391       •   While transition table names for AFTER triggers are specified using
392           the REFERENCING clause in the standard way, the row variables used
393           in FOR EACH ROW triggers may not be specified in a REFERENCING
394           clause. They are available in a manner that is dependent on the
395           language in which the trigger function is written, but is fixed for
396           any one language. Some languages effectively behave as though there
397           is a REFERENCING clause containing OLD ROW AS OLD NEW ROW AS NEW.
398
399       •   The standard allows transition tables to be used with
400           column-specific UPDATE triggers, but then the set of rows that
401           should be visible in the transition tables depends on the trigger's
402           column list. This is not currently implemented by PostgreSQL.
403
404       •   PostgreSQL only allows the execution of a user-defined function for
405           the triggered action. The standard allows the execution of a number
406           of other SQL commands, such as CREATE TABLE, as the triggered
407           action. This limitation is not hard to work around by creating a
408           user-defined function that executes the desired commands.
409
410       SQL specifies that multiple triggers should be fired in
411       time-of-creation order.  PostgreSQL uses name order, which was judged
412       to be more convenient.
413
414       SQL specifies that BEFORE DELETE triggers on cascaded deletes fire
415       after the cascaded DELETE completes. The PostgreSQL behavior is for
416       BEFORE DELETE to always fire before the delete action, even a cascading
417       one. This is considered more consistent. There is also nonstandard
418       behavior if BEFORE triggers modify rows or prevent updates during an
419       update that is caused by a referential action. This can lead to
420       constraint violations or stored data that does not honor the
421       referential constraint.
422
423       The ability to specify multiple actions for a single trigger using OR
424       is a PostgreSQL extension of the SQL standard.
425
426       The ability to fire triggers for TRUNCATE is a PostgreSQL extension of
427       the SQL standard, as is the ability to define statement-level triggers
428       on views.
429
430       CREATE CONSTRAINT TRIGGER is a PostgreSQL extension of the SQL
431       standard.
432

SEE ALSO

434       ALTER TRIGGER (ALTER_TRIGGER(7)), DROP TRIGGER (DROP_TRIGGER(7)),
435       CREATE FUNCTION (CREATE_FUNCTION(7)), SET CONSTRAINTS
436       (SET_CONSTRAINTS(7))
437
438
439
440PostgreSQL 13.4                      2021                    CREATE TRIGGER(7)
Impressum