1CREATE TRIGGER(7) PostgreSQL 9.2.24 Documentation CREATE TRIGGER(7)
2
6 CREATE_TRIGGER - define a new trigger
7
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 [ FOR [ EACH ] { ROW | STATEMENT } ]
14 [ WHEN ( condition ) ]
15 EXECUTE PROCEDURE function_name ( arguments )
16 where event can be one of:
18 INSERT
20 UPDATE [ OF column_name [, ... ] ]
21 DELETE
22 TRUNCATE
23
25 CREATE TRIGGER creates a new trigger. The trigger will be associated
26 with the specified table or view and will execute the specified
27 function function_name when certain events occur.
28 The trigger can be specified to fire before the operation is attempted
30 on a row (before constraints are checked and the INSERT, UPDATE, or
31 DELETE is attempted); or after the operation has completed (after
32 constraints are checked and the INSERT, UPDATE, or DELETE has
33 completed); or instead of the operation (in the case of inserts,
34 updates or deletes on a view). If the trigger fires before or instead
35 of the event, the trigger can skip the operation for the current row,
36 or change the row being inserted (for INSERT and UPDATE operations
37 only). If the trigger fires after the event, all changes, including the
38 effects of other triggers, are “visible” to the trigger.
39 A trigger that is marked FOR EACH ROW is called once for every row that
41 the operation modifies. For example, a DELETE that affects 10 rows will
42 cause any ON DELETE triggers on the target relation to be called 10
43 separate times, once for each deleted row. In contrast, a trigger that
44 is marked FOR EACH STATEMENT only executes once for any given
45 operation, regardless of how many rows it modifies (in particular, an
46 operation that modifies zero rows will still result in the execution of
47 any applicable FOR EACH STATEMENT triggers).
48 Triggers that are specified to fire INSTEAD OF the trigger event must
50 be marked FOR EACH ROW, and can only be defined on views. BEFORE and
51 AFTER triggers on a view must be marked as FOR EACH STATEMENT.
52 In addition, triggers may be defined to fire for TRUNCATE, though only
54 FOR EACH STATEMENT.
55 The following table summarizes which types of triggers may be used on
57 tables and views:
58 ┌───────────┬──────────────────────┬───────────┬──────────────────┐
60 │When │ Event │ Row-level │ Statement-level │
61 ├───────────┼──────────────────────┼───────────┼──────────────────┤
62 │ │ INSERT/UPDATE/DELETE │ Tables │ Tables and views │
63 │ BEFORE ├──────────────────────┼───────────┼──────────────────┤
64 │ │ TRUNCATE │ — │ Tables │
65 ├───────────┼──────────────────────┼───────────┼──────────────────┤
66 │ │ INSERT/UPDATE/DELETE │ Tables │ Tables and views │
67 │ AFTER ├──────────────────────┼───────────┼──────────────────┤
68 │ │ TRUNCATE │ — │ Tables │
69 ├───────────┼──────────────────────┼───────────┼──────────────────┤
70 │ │ INSERT/UPDATE/DELETE │ Views │ — │
71 │INSTEAD OF ├──────────────────────┼───────────┼──────────────────┤
72 │ │ TRUNCATE │ — │ — │
73 └───────────┴──────────────────────┴───────────┴──────────────────┘
74 Also, a trigger definition can specify a Boolean WHEN condition, which
76 will be tested to see whether the trigger should be fired. In row-level
77 triggers the WHEN condition can examine the old and/or new values of
78 columns of the row. Statement-level triggers can also have WHEN
79 conditions, although the feature is not so useful for them since the
80 condition cannot refer to any values in the table.
81 If multiple triggers of the same kind are defined for the same event,
83 they will be fired in alphabetical order by name.
84 When the CONSTRAINT option is specified, this command creates a
86 constraint trigger. This is the same as a regular trigger except that
87 the timing of the trigger firing can be adjusted using SET CONSTRAINTS
88 (SET_CONSTRAINTS(7)). Constraint triggers must be AFTER ROW triggers.
89 They can be fired either at the end of the statement causing the
90 triggering event, or at the end of the containing transaction; in the
91 latter case they are said to be deferred. A pending deferred-trigger
92 firing can also be forced to happen immediately by using SET
93 CONSTRAINTS. Constraint triggers are expected to raise an exception
94 when the constraints they implement are violated.
95 SELECT does not modify any rows so you cannot create SELECT triggers.
97 Rules and views are more appropriate in such cases.
98 Refer to Chapter 36, Triggers, in the documentation for more
100 information about triggers.
101
103 name
104 The name to give the new trigger. This must be distinct from the
105 name of any other trigger for the same table. The name cannot be
106 schema-qualified — the trigger inherits the schema of its table.
107 For a constraint trigger, this is also the name to use when
108 modifying the trigger's behavior using SET CONSTRAINTS.
109 BEFORE, AFTER, INSTEAD OF
111 Determines whether the function is called before, after, or instead
112 of the event. A constraint trigger can only be specified as AFTER.
113 event
115 One of INSERT, UPDATE, DELETE, or TRUNCATE; this specifies the
116 event that will fire the trigger. Multiple events can be specified
117 using OR.
118 For UPDATE events, it is possible to specify a list of columns
120 using this syntax:
121 UPDATE OF column_name1 [, column_name2 ... ]
123 The trigger will only fire if at least one of the listed columns is
125 mentioned as a target of the UPDATE command.
126 INSTEAD OF UPDATE events do not support lists of columns.
128 table_name
130 The name (optionally schema-qualified) of the table or view the
131 trigger is for.
132 referenced_table_name
134 The (possibly schema-qualified) name of another table referenced by
135 the constraint. This option is used for foreign-key constraints and
136 is not recommended for general use. This can only be specified for
137 constraint triggers.
138 DEFERRABLE, NOT DEFERRABLE, INITIALLY IMMEDIATE, INITIALLY DEFERRED
140 The default timing of the trigger. See the CREATE TABLE
141 (CREATE_TABLE(7)) documentation for details of these constraint
142 options. This can only be specified for constraint triggers.
143 FOR EACH ROW, FOR EACH STATEMENT
145 This specifies whether the trigger procedure should be fired once
146 for every row affected by the trigger event, or just once per SQL
147 statement. If neither is specified, FOR EACH STATEMENT is the
148 default. Constraint triggers can only be specified FOR EACH ROW.
149 condition
151 A Boolean expression that determines whether the trigger function
152 will actually be executed. If WHEN is specified, the function will
153 only be called if the condition returns true. In FOR EACH ROW
154 triggers, the WHEN condition can refer to columns of the old and/or
155 new row values by writing OLD.column_name or NEW.column_name
156 respectively. Of course, INSERT triggers cannot refer to OLD and
157 DELETE triggers cannot refer to NEW.
158 INSTEAD OF triggers do not support WHEN conditions.
160 Currently, WHEN expressions cannot contain subqueries.
162 Note that for constraint triggers, evaluation of the WHEN condition
164 is not deferred, but occurs immediately after the row update
165 operation is performed. If the condition does not evaluate to true
166 then the trigger is not queued for deferred execution.
167 function_name
169 A user-supplied function that is declared as taking no arguments
170 and returning type trigger, which is executed when the trigger
171 fires.
172 arguments
174 An optional comma-separated list of arguments to be provided to the
175 function when the trigger is executed. The arguments are literal
176 string constants. Simple names and numeric constants can be written
177 here, too, but they will all be converted to strings. Please check
178 the description of the implementation language of the trigger
179 function to find out how these arguments can be accessed within the
180 function; it might be different from normal function arguments.
181
183 To create a trigger on a table, the user must have the TRIGGER
184 privilege on the table. The user must also have EXECUTE privilege on
185 the trigger function.
186 Use DROP TRIGGER (DROP_TRIGGER(7)) to remove a trigger.
188 A column-specific trigger (one defined using the UPDATE OF column_name
190 syntax) will fire when any of its columns are listed as targets in the
191 UPDATE command's SET list. It is possible for a column's value to
192 change even when the trigger is not fired, because changes made to the
193 row's contents by BEFORE UPDATE triggers are not considered.
194 Conversely, a command such as UPDATE ... SET x = x ... will fire a
195 trigger on column x, even though the column's value did not change.
196 In a BEFORE trigger, the WHEN condition is evaluated just before the
198 function is or would be executed, so using WHEN is not materially
199 different from testing the same condition at the beginning of the
200 trigger function. Note in particular that the NEW row seen by the
201 condition is the current value, as possibly modified by earlier
202 triggers. Also, a BEFORE trigger's WHEN condition is not allowed to
203 examine the system columns of the NEW row (such as oid), because those
204 won't have been set yet.
205 In an AFTER trigger, the WHEN condition is evaluated just after the row
207 update occurs, and it determines whether an event is queued to fire the
208 trigger at the end of statement. So when an AFTER trigger's WHEN
209 condition does not return true, it is not necessary to queue an event
210 nor to re-fetch the row at end of statement. This can result in
211 significant speedups in statements that modify many rows, if the
212 trigger only needs to be fired for a few of the rows.
213 In PostgreSQL versions before 7.3, it was necessary to declare trigger
215 functions as returning the placeholder type opaque, rather than
216 trigger. To support loading of old dump files, CREATE TRIGGER will
217 accept a function declared as returning opaque, but it will issue a
218 notice and change the function's declared return type to trigger.
219
221 Execute the function check_account_update whenever a row of the table
222 accounts is about to be updated:
223 CREATE TRIGGER check_update
225 BEFORE UPDATE ON accounts
226 FOR EACH ROW
227 EXECUTE PROCEDURE check_account_update();
228 The same, but only execute the function if column balance is specified
230 as a target in the UPDATE command:
231 CREATE TRIGGER check_update
233 BEFORE UPDATE OF balance ON accounts
234 FOR EACH ROW
235 EXECUTE PROCEDURE check_account_update();
236 This form only executes the function if column balance has in fact
238 changed value:
239 CREATE TRIGGER check_update
241 BEFORE UPDATE ON accounts
242 FOR EACH ROW
243 WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
244 EXECUTE PROCEDURE check_account_update();
245 Call a function to log updates of accounts, but only if something
247 changed:
248 CREATE TRIGGER log_update
250 AFTER UPDATE ON accounts
251 FOR EACH ROW
252 WHEN (OLD.* IS DISTINCT FROM NEW.*)
253 EXECUTE PROCEDURE log_account_update();
254 Execute the function view_insert_row for each row to insert rows into
256 the tables underlying a view:
257 CREATE TRIGGER view_insert
259 INSTEAD OF INSERT ON my_view
260 FOR EACH ROW
261 EXECUTE PROCEDURE view_insert_row();
262 Section 36.4, “A Complete Trigger Example”, in the documentation
264 contains a complete example of a trigger function written in C.
265
267 The CREATE TRIGGER statement in PostgreSQL implements a subset of the
268 SQL standard. The following functionality is currently missing:
269 · SQL allows you to define aliases for the “old” and “new” rows or
271 tables for use in the definition of the triggered action (e.g.,
272 CREATE TRIGGER ... ON tablename REFERENCING OLD ROW AS somename NEW
273 ROW AS othername ...). Since PostgreSQL allows trigger procedures
274 to be written in any number of user-defined languages, access to
275 the data is handled in a language-specific way.
276 · PostgreSQL only allows the execution of a user-defined function for
278 the triggered action. The standard allows the execution of a number
279 of other SQL commands, such as CREATE TABLE, as the triggered
280 action. This limitation is not hard to work around by creating a
281 user-defined function that executes the desired commands.
282 SQL specifies that multiple triggers should be fired in
284 time-of-creation order. PostgreSQL uses name order, which was judged
285 to be more convenient.
286 SQL specifies that BEFORE DELETE triggers on cascaded deletes fire
288 after the cascaded DELETE completes. The PostgreSQL behavior is for
289 BEFORE DELETE to always fire before the delete action, even a cascading
290 one. This is considered more consistent. There is also nonstandard
291 behavior if BEFORE triggers modify rows or prevent updates during an
292 update that is caused by a referential action. This can lead to
293 constraint violations or stored data that does not honor the
294 referential constraint.
295 The ability to specify multiple actions for a single trigger using OR
297 is a PostgreSQL extension of the SQL standard.
298 The ability to fire triggers for TRUNCATE is a PostgreSQL extension of
300 the SQL standard, as is the ability to define statement-level triggers
301 on views.
302 CREATE CONSTRAINT TRIGGER is a PostgreSQL extension of the SQL
304 standard.
305
307 CREATE FUNCTION (CREATE_FUNCTION(7)), ALTER TRIGGER (ALTER_TRIGGER(7)),
308 DROP TRIGGER (DROP_TRIGGER(7)), SET CONSTRAINTS (SET_CONSTRAINTS(7))
309PostgreSQL 9.2.24 2017-11-06 CREATE TRIGGER(7)