1CREATE TRIGGER(7)        PostgreSQL 11.6 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 39 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.
149
150           INSTEAD OF UPDATE events do not allow a list of columns. A column
151           list cannot be specified when requesting transition relations,
152           either.
153
154       table_name
155           The name (optionally schema-qualified) of the table, view, or
156           foreign table the trigger is for.
157
158       referenced_table_name
159           The (possibly schema-qualified) name of another table referenced by
160           the constraint. This option is used for foreign-key constraints and
161           is not recommended for general use. This can only be specified for
162           constraint triggers.
163
164       DEFERRABLE
165       NOT DEFERRABLE
166       INITIALLY IMMEDIATE
167       INITIALLY DEFERRED
168           The default timing of the trigger. See the CREATE TABLE
169           (CREATE_TABLE(7)) documentation for details of these constraint
170           options. This can only be specified for constraint triggers.
171
172       REFERENCING
173           This keyword immediately precedes the declaration of one or two
174           relation names that provide access to the transition relations of
175           the triggering statement.
176
177       OLD TABLE
178       NEW TABLE
179           This clause indicates whether the following relation name is for
180           the before-image transition relation or the after-image transition
181           relation.
182
183       transition_relation_name
184           The (unqualified) name to be used within the trigger for this
185           transition relation.
186
187       FOR EACH ROW
188       FOR EACH STATEMENT
189           This specifies whether the trigger function should be fired once
190           for every row affected by the trigger event, or just once per SQL
191           statement. If neither is specified, FOR EACH STATEMENT is the
192           default. Constraint triggers can only be specified FOR EACH ROW.
193
194       condition
195           A Boolean expression that determines whether the trigger function
196           will actually be executed. If WHEN is specified, the function will
197           only be called if the condition returns true. In FOR EACH ROW
198           triggers, the WHEN condition can refer to columns of the old and/or
199           new row values by writing OLD.column_name or NEW.column_name
200           respectively. Of course, INSERT triggers cannot refer to OLD and
201           DELETE triggers cannot refer to NEW.
202
203           INSTEAD OF triggers do not support WHEN conditions.
204
205           Currently, WHEN expressions cannot contain subqueries.
206
207           Note that for constraint triggers, evaluation of the WHEN condition
208           is not deferred, but occurs immediately after the row update
209           operation is performed. If the condition does not evaluate to true
210           then the trigger is not queued for deferred execution.
211
212       function_name
213           A user-supplied function that is declared as taking no arguments
214           and returning type trigger, which is executed when the trigger
215           fires.
216
217           In the syntax of CREATE TRIGGER, the keywords FUNCTION and
218           PROCEDURE are equivalent, but the referenced function must in any
219           case be a function, not a procedure. The use of the keyword
220           PROCEDURE here is historical and deprecated.
221
222       arguments
223           An optional comma-separated list of arguments to be provided to the
224           function when the trigger is executed. The arguments are literal
225           string constants. Simple names and numeric constants can be written
226           here, too, but they will all be converted to strings. Please check
227           the description of the implementation language of the trigger
228           function to find out how these arguments can be accessed within the
229           function; it might be different from normal function arguments.
230

NOTES

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

EXAMPLES

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

COMPATIBILITY

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

SEE ALSO

433       ALTER TRIGGER (ALTER_TRIGGER(7)), DROP TRIGGER (DROP_TRIGGER(7)),
434       CREATE FUNCTION (CREATE_FUNCTION(7)), SET CONSTRAINTS
435       (SET_CONSTRAINTS(7))
436
437
438
439PostgreSQL 11.6                      2019                    CREATE TRIGGER(7)
Impressum