2 doc/src/sgml/ref/create_trigger.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-CREATETRIGGER">
8 <refentrytitle>CREATE TRIGGER</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>CREATE TRIGGER</refname>
15 <refpurpose>define a new trigger</refpurpose>
18 <indexterm zone="sql-createtrigger">
19 <primary>CREATE TRIGGER</primary>
24 CREATE [ CONSTRAINT ] TRIGGER <replaceable class="PARAMETER">name</replaceable> { BEFORE | AFTER | INSTEAD OF } { <replaceable class="PARAMETER">event</replaceable> [ OR ... ] }
25 ON <replaceable class="PARAMETER">table</replaceable>
26 [ FROM <replaceable class="parameter">referenced_table_name</replaceable> ]
27 { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
28 [ FOR [ EACH ] { ROW | STATEMENT } ]
29 [ WHEN ( <replaceable class="parameter">condition</replaceable> ) ]
30 EXECUTE PROCEDURE <replaceable class="PARAMETER">function_name</replaceable> ( <replaceable class="PARAMETER">arguments</replaceable> )
32 <phrase>where <replaceable class="parameter">event</replaceable> can be one of:</phrase>
35 UPDATE [ OF <replaceable class="parameter">column_name</replaceable> [, ... ] ]
42 <title>Description</title>
45 <command>CREATE TRIGGER</command> creates a new trigger. The
46 trigger will be associated with the specified table or view and will
47 execute the specified function <replaceable
48 class="parameter">function_name</replaceable> when certain events occur.
52 The trigger can be specified to fire before the
53 operation is attempted on a row (before constraints are checked and
54 the <command>INSERT</command>, <command>UPDATE</command>, or
55 <command>DELETE</command> is attempted); or after the operation has
56 completed (after constraints are checked and the
57 <command>INSERT</command>, <command>UPDATE</command>, or
58 <command>DELETE</command> has completed); or instead of the operation
59 (in the case of inserts, updates or deletes on a view).
60 If the trigger fires before or instead of the event, the trigger can skip
61 the operation for the current row, or change the row being inserted (for
62 <command>INSERT</command> and <command>UPDATE</command> operations
63 only). If the trigger fires after the event, all changes, including
64 the effects of other triggers, are <quote>visible</quote>
69 A trigger that is marked <literal>FOR EACH ROW</literal> is called
70 once for every row that the operation modifies. For example, a
71 <command>DELETE</command> that affects 10 rows will cause any
72 <literal>ON DELETE</literal> triggers on the target relation to be
73 called 10 separate times, once for each deleted row. In contrast, a
74 trigger that is marked <literal>FOR EACH STATEMENT</literal> only
75 executes once for any given operation, regardless of how many rows
76 it modifies (in particular, an operation that modifies zero rows
77 will still result in the execution of any applicable <literal>FOR
78 EACH STATEMENT</literal> triggers).
82 Triggers that are specified to fire <literal>INSTEAD OF</> the trigger
83 event must be marked <literal>FOR EACH ROW</>, and can only be defined
84 on views. <literal>BEFORE</> and <literal>AFTER</> triggers on a view
85 must be marked as <literal>FOR EACH STATEMENT</>.
89 In addition, triggers may be defined to fire for
90 <command>TRUNCATE</command>, though only
91 <literal>FOR EACH STATEMENT</literal>.
95 The following table summarizes which types of triggers may be used on
99 <informaltable id="supported-trigger-types">
105 <entry>Row-level</entry>
106 <entry>Statement-level</entry>
111 <entry align="center" morerows="1"><literal>BEFORE</></entry>
112 <entry align="center"><command>INSERT</>/<command>UPDATE</>/<command>DELETE</></entry>
113 <entry align="center">Tables</entry>
114 <entry align="center">Tables and views</entry>
117 <entry align="center"><command>TRUNCATE</></entry>
118 <entry align="center">—</entry>
119 <entry align="center">Tables</entry>
122 <entry align="center" morerows="1"><literal>AFTER</></entry>
123 <entry align="center"><command>INSERT</>/<command>UPDATE</>/<command>DELETE</></entry>
124 <entry align="center">Tables</entry>
125 <entry align="center">Tables and views</entry>
128 <entry align="center"><command>TRUNCATE</></entry>
129 <entry align="center">—</entry>
130 <entry align="center">Tables</entry>
133 <entry align="center" morerows="1"><literal>INSTEAD OF</></entry>
134 <entry align="center"><command>INSERT</>/<command>UPDATE</>/<command>DELETE</></entry>
135 <entry align="center">Views</entry>
136 <entry align="center">—</entry>
139 <entry align="center"><command>TRUNCATE</></entry>
140 <entry align="center">—</entry>
141 <entry align="center">—</entry>
148 Also, a trigger definition can specify a Boolean <literal>WHEN</>
149 condition, which will be tested to see whether the trigger should
150 be fired. In row-level triggers the <literal>WHEN</> condition can
151 examine the old and/or new values of columns of the row. Statement-level
152 triggers can also have <literal>WHEN</> conditions, although the feature
153 is not so useful for them since the condition cannot refer to any values
158 If multiple triggers of the same kind are defined for the same event,
159 they will be fired in alphabetical order by name.
163 When the <literal>CONSTRAINT</> option is specified, this command creates a
164 <firstterm>constraint trigger</>. This is the same as a regular trigger
165 except that the timing of the trigger firing can be adjusted using
166 <xref linkend="SQL-SET-CONSTRAINTS">.
167 Constraint triggers must be <literal>AFTER ROW</> triggers. They can
168 be fired either at the end of the statement causing the triggering event,
169 or at the end of the containing transaction; in the latter case they are
170 said to be <firstterm>deferred</>. A pending deferred-trigger firing can
171 also be forced to happen immediately by using <command>SET CONSTRAINTS</>.
172 Constraint triggers are expected to raise an exception when the constraints
173 they implement are violated.
177 <command>SELECT</command> does not modify any rows so you cannot
178 create <command>SELECT</command> triggers. Rules and views are more
179 appropriate in such cases.
183 Refer to <xref linkend="triggers"> for more information about triggers.
188 <title>Parameters</title>
192 <term><replaceable class="parameter">name</replaceable></term>
195 The name to give the new trigger. This must be distinct from
196 the name of any other trigger for the same table.
197 The name cannot be schema-qualified — the trigger inherits the
198 schema of its table. For a constraint trigger, this is also the name to
199 use when modifying the trigger's behavior using
200 <command>SET CONSTRAINTS</>.
206 <term><literal>BEFORE</literal></term>
207 <term><literal>AFTER</literal></term>
208 <term><literal>INSTEAD OF</literal></term>
211 Determines whether the function is called before, after, or instead of
212 the event. A constraint trigger can only be specified as
219 <term><replaceable class="parameter">event</replaceable></term>
222 One of <literal>INSERT</literal>, <literal>UPDATE</literal>,
223 <literal>DELETE</literal>, or <literal>TRUNCATE</literal>;
224 this specifies the event that will fire the trigger. Multiple
225 events can be specified using <literal>OR</literal>.
229 For <literal>UPDATE</literal> events, it is possible to
230 specify a list of columns using this syntax:
232 UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</replaceable> ... ]
234 The trigger will only fire if at least one of the listed columns
235 is mentioned as a target of the <command>UPDATE</> command.
238 <para><literal>INSTEAD OF UPDATE</> events do not support lists of columns.
244 <term><replaceable class="parameter">table</replaceable></term>
247 The name (optionally schema-qualified) of the table or view the trigger
254 <term><replaceable class="PARAMETER">referenced_table_name</replaceable></term>
257 The (possibly schema-qualified) name of another table referenced by the
258 constraint. This option is used for foreign-key constraints and is not
259 recommended for general use. This can only be specified for
266 <term><literal>DEFERRABLE</literal></term>
267 <term><literal>NOT DEFERRABLE</literal></term>
268 <term><literal>INITIALLY IMMEDIATE</literal></term>
269 <term><literal>INITIALLY DEFERRED</literal></term>
272 The default timing of the trigger.
273 See the <xref linkend="SQL-CREATETABLE"> documentation for details of
274 these constraint options. This can only be specified for constraint
281 <term><literal>FOR EACH ROW</literal></term>
282 <term><literal>FOR EACH STATEMENT</literal></term>
286 This specifies whether the trigger procedure should be fired
287 once for every row affected by the trigger event, or just once
288 per SQL statement. If neither is specified, <literal>FOR EACH
289 STATEMENT</literal> is the default. Constraint triggers can only
290 be specified <literal>FOR EACH ROW</>.
296 <term><replaceable class="parameter">condition</replaceable></term>
299 A Boolean expression that determines whether the trigger function
300 will actually be executed. If <literal>WHEN</> is specified, the
301 function will only be called if the <replaceable
302 class="parameter">condition</replaceable> returns <literal>true</>.
303 In <literal>FOR EACH ROW</literal> triggers, the <literal>WHEN</>
304 condition can refer to columns of the old and/or new row values
305 by writing <literal>OLD.<replaceable
306 class="parameter">column_name</replaceable></literal> or
307 <literal>NEW.<replaceable
308 class="parameter">column_name</replaceable></literal> respectively.
309 Of course, <literal>INSERT</> triggers cannot refer to <literal>OLD</>
310 and <literal>DELETE</> triggers cannot refer to <literal>NEW</>.
313 <para><literal>INSTEAD OF</> triggers do not support <literal>WHEN</>
318 Currently, <literal>WHEN</literal> expressions cannot contain
323 Note that for constraint triggers, evaluation of the <literal>WHEN</>
324 condition is not deferred, but occurs immediately after the row update
325 operation is performed. If the condition does not evaluate to true then
326 the trigger is not queued for deferred execution.
332 <term><replaceable class="parameter">function_name</replaceable></term>
335 A user-supplied function that is declared as taking no arguments
336 and returning type <literal>trigger</>, which is executed when
343 <term><replaceable class="parameter">arguments</replaceable></term>
346 An optional comma-separated list of arguments to be provided to
347 the function when the trigger is executed. The arguments are
348 literal string constants. Simple names and numeric constants
349 can be written here, too, but they will all be converted to
350 strings. Please check the description of the implementation
351 language of the trigger function to find out how these arguments
352 can be accessed within the function; it might be different from
353 normal function arguments.
360 <refsect1 id="SQL-CREATETRIGGER-notes">
364 To create a trigger on a table, the user must have the
365 <literal>TRIGGER</literal> privilege on the table. The user must
366 also have <literal>EXECUTE</literal> privilege on the trigger function.
370 Use <xref linkend="sql-droptrigger"> to remove a trigger.
374 A column-specific trigger (one defined using the <literal>UPDATE OF
375 <replaceable>column_name</replaceable></literal> syntax) will fire when any
376 of its columns are listed as targets in the <command>UPDATE</>
377 command's <literal>SET</> list. It is possible for a column's value
378 to change even when the trigger is not fired, because changes made to the
379 row's contents by <literal>BEFORE UPDATE</> triggers are not considered.
380 Conversely, a command such as <literal>UPDATE ... SET x = x ...</>
381 will fire a trigger on column <literal>x</>, even though the column's
382 value did not change.
386 In a <literal>BEFORE</> trigger, the <literal>WHEN</> condition is
387 evaluated just before the function is or would be executed, so using
388 <literal>WHEN</> is not materially different from testing the same
389 condition at the beginning of the trigger function. Note in particular
390 that the <literal>NEW</> row seen by the condition is the current value,
391 as possibly modified by earlier triggers. Also, a <literal>BEFORE</>
392 trigger's <literal>WHEN</> condition is not allowed to examine the
393 system columns of the <literal>NEW</> row (such as <literal>oid</>),
394 because those won't have been set yet.
398 In an <literal>AFTER</> trigger, the <literal>WHEN</> condition is
399 evaluated just after the row update occurs, and it determines whether an
400 event is queued to fire the trigger at the end of statement. So when an
401 <literal>AFTER</> trigger's <literal>WHEN</> condition does not return
402 true, it is not necessary to queue an event nor to re-fetch the row at end
403 of statement. This can result in significant speedups in statements that
404 modify many rows, if the trigger only needs to be fired for a few of the
409 In <productname>PostgreSQL</productname> versions before 7.3, it was
410 necessary to declare trigger functions as returning the placeholder
411 type <type>opaque</>, rather than <type>trigger</>. To support loading
412 of old dump files, <command>CREATE TRIGGER</> will accept a function
413 declared as returning <type>opaque</>, but it will issue a notice and
414 change the function's declared return type to <type>trigger</>.
418 <refsect1 id="SQL-CREATETRIGGER-examples">
419 <title>Examples</title>
422 Execute the function <function>check_account_update</> whenever
423 a row of the table <literal>accounts</> is about to be updated:
426 CREATE TRIGGER check_update
427 BEFORE UPDATE ON accounts
429 EXECUTE PROCEDURE check_account_update();
432 The same, but only execute the function if column <literal>balance</>
433 is specified as a target in the <command>UPDATE</> command:
436 CREATE TRIGGER check_update
437 BEFORE UPDATE OF balance ON accounts
439 EXECUTE PROCEDURE check_account_update();
442 This form only executes the function if column <literal>balance</>
443 has in fact changed value:
446 CREATE TRIGGER check_update
447 BEFORE UPDATE ON accounts
449 WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
450 EXECUTE PROCEDURE check_account_update();
453 Call a function to log updates of <literal>accounts</>, but only if
457 CREATE TRIGGER log_update
458 AFTER UPDATE ON accounts
460 WHEN (OLD.* IS DISTINCT FROM NEW.*)
461 EXECUTE PROCEDURE log_account_update();
464 Execute the function <function>view_insert_row</> for each row to insert
465 rows into the tables underlying a view:
468 CREATE TRIGGER view_insert
469 INSTEAD OF INSERT ON my_view
471 EXECUTE PROCEDURE view_insert_row();
476 <xref linkend="trigger-example"> contains a complete example of a trigger
477 function written in C.
481 <refsect1 id="SQL-CREATETRIGGER-compatibility">
482 <title>Compatibility</title>
485 The <command>CREATE TRIGGER</command> statement in
486 <productname>PostgreSQL</productname> implements a subset of the
487 <acronym>SQL</> standard. The following functionality is currently missing:
492 SQL allows you to define aliases for the <quote>old</quote>
493 and <quote>new</quote> rows or tables for use in the definition
494 of the triggered action (e.g., <literal>CREATE TRIGGER ... ON
495 tablename REFERENCING OLD ROW AS somename NEW ROW AS othername
496 ...</literal>). Since <productname>PostgreSQL</productname>
497 allows trigger procedures to be written in any number of
498 user-defined languages, access to the data is handled in a
499 language-specific way.
504 <para><productname>PostgreSQL</productname> only allows the execution
505 of a user-defined function for the triggered action. The standard
506 allows the execution of a number of other SQL commands, such as
507 <command>CREATE TABLE</command>, as the triggered action. This
508 limitation is not hard to work around by creating a user-defined
509 function that executes the desired commands.
517 SQL specifies that multiple triggers should be fired in
518 time-of-creation order. <productname>PostgreSQL</productname> uses
519 name order, which was judged to be more convenient.
523 SQL specifies that <literal>BEFORE DELETE</literal> triggers on cascaded
524 deletes fire <emphasis>after</> the cascaded <literal>DELETE</> completes.
525 The <productname>PostgreSQL</productname> behavior is for <literal>BEFORE
526 DELETE</literal> to always fire before the delete action, even a cascading
527 one. This is considered more consistent. There is also nonstandard
528 behavior if <literal>BEFORE</literal> triggers modify rows or prevent
529 updates during an update that is caused by a referential action. This can
530 lead to constraint violations or stored data that does not honor the
531 referential constraint.
535 The ability to specify multiple actions for a single trigger using
536 <literal>OR</literal> is a <productname>PostgreSQL</> extension of
541 The ability to fire triggers for <command>TRUNCATE</command> is a
542 <productname>PostgreSQL</> extension of the SQL standard, as is the
543 ability to define statement-level triggers on views.
547 <command>CREATE CONSTRAINT TRIGGER</command> is a
548 <productname>PostgreSQL</productname> extension of the <acronym>SQL</>
555 <title>See Also</title>
557 <simplelist type="inline">
558 <member><xref linkend="sql-createfunction"></member>
559 <member><xref linkend="sql-altertrigger"></member>
560 <member><xref linkend="sql-droptrigger"></member>
561 <member><xref linkend="sql-set-constraints"></member>