Debian Codegraph @

doc/src/sgml/ref/create_trigger.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_trigger.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createtrigger">
7 <indexterm zone="sql-createtrigger">
8 <primary>CREATE TRIGGER</primary>
9 </indexterm>
10
11 <indexterm>
12 <primary>transition tables</primary>
13 <seealso>ephemeral named relation</seealso>
14 </indexterm>
15
16 <refmeta>
17 <refentrytitle>CREATE TRIGGER</refentrytitle>
18 <manvolnum>7</manvolnum>
19 <refmiscinfo>SQL - Language Statements</refmiscinfo>
20 </refmeta>
21
22 <refnamediv>
23 <refname>CREATE TRIGGER</refname>
24 <refpurpose>define a new trigger</refpurpose>
25 </refnamediv>
26
27 <refsynopsisdiv>
28 <synopsis>
29 CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable> { BEFORE | AFTER | INSTEAD OF } { <replaceable class="parameter">event</replaceable> [ OR ... ] }
30 ON <replaceable class="parameter">table_name</replaceable>
31 [ FROM <replaceable class="parameter">referenced_table_name</replaceable> ]
32 [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
33 [ REFERENCING { { OLD | NEW } TABLE [ AS ] <replaceable class="parameter">transition_relation_name</replaceable> } [ ... ] ]
34 [ FOR [ EACH ] { ROW | STATEMENT } ]
35 [ WHEN ( <replaceable class="parameter">condition</replaceable> ) ]
36 EXECUTE { FUNCTION | PROCEDURE } <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">arguments</replaceable> )
37
38 <phrase>where <replaceable class="parameter">event</replaceable> can be one of:</phrase>
39
40 INSERT
41 UPDATE [ OF <replaceable class="parameter">column_name</replaceable> [, ... ] ]
42 DELETE
43 TRUNCATE
44 </synopsis>
45 </refsynopsisdiv>
46
47 <refsect1>
48 <title>Description</title>
49
50 <para>
51 <command>CREATE TRIGGER</command> creates a new trigger.
52 <command>CREATE OR REPLACE TRIGGER</command> will either create a
53 new trigger, or replace an existing trigger. The
54 trigger will be associated with the specified table, view, or foreign table
55 and will execute the specified
56 function <replaceable class="parameter">function_name</replaceable> when
57 certain operations are performed on that table.
58 </para>
59
60 <para>
61 To replace the current definition of an existing trigger, use
62 <command>CREATE OR REPLACE TRIGGER</command>, specifying the existing
63 trigger's name and parent table. All other properties are replaced.
64 </para>
65
66 <para>
67 The trigger can be specified to fire before the
68 operation is attempted on a row (before constraints are checked and
69 the <command>INSERT</command>, <command>UPDATE</command>, or
70 <command>DELETE</command> is attempted); or after the operation has
71 completed (after constraints are checked and the
72 <command>INSERT</command>, <command>UPDATE</command>, or
73 <command>DELETE</command> has completed); or instead of the operation
74 (in the case of inserts, updates or deletes on a view).
75 If the trigger fires before or instead of the event, the trigger can skip
76 the operation for the current row, or change the row being inserted (for
77 <command>INSERT</command> and <command>UPDATE</command> operations
78 only). If the trigger fires after the event, all changes, including
79 the effects of other triggers, are <quote>visible</quote>
80 to the trigger.
81 </para>
82
83 <para>
84 A trigger that is marked <literal>FOR EACH ROW</literal> is called
85 once for every row that the operation modifies. For example, a
86 <command>DELETE</command> that affects 10 rows will cause any
87 <literal>ON DELETE</literal> triggers on the target relation to be
88 called 10 separate times, once for each deleted row. In contrast, a
89 trigger that is marked <literal>FOR EACH STATEMENT</literal> only
90 executes once for any given operation, regardless of how many rows
91 it modifies (in particular, an operation that modifies zero rows
92 will still result in the execution of any applicable <literal>FOR
93 EACH STATEMENT</literal> triggers).
94 </para>
95
96 <para>
97 Triggers that are specified to fire <literal>INSTEAD OF</literal> the trigger
98 event must be marked <literal>FOR EACH ROW</literal>, and can only be defined
99 on views. <literal>BEFORE</literal> and <literal>AFTER</literal> triggers on a view
100 must be marked as <literal>FOR EACH STATEMENT</literal>.
101 </para>
102
103 <para>
104 In addition, triggers may be defined to fire for
105 <command>TRUNCATE</command>, though only
106 <literal>FOR EACH STATEMENT</literal>.
107 </para>
108
109 <para>
110 The following table summarizes which types of triggers may be used on
111 tables, views, and foreign tables:
112 </para>
113
114 <informaltable id="supported-trigger-types">
115 <tgroup cols="4">
116 <thead>
117 <row>
118 <entry>When</entry>
119 <entry>Event</entry>
120 <entry>Row-level</entry>
121 <entry>Statement-level</entry>
122 </row>
123 </thead>
124 <tbody>
125 <row>
126 <entry align="center" morerows="1"><literal>BEFORE</literal></entry>
127 <entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry>
128 <entry align="center">Tables and foreign tables</entry>
129 <entry align="center">Tables, views, and foreign tables</entry>
130 </row>
131 <row>
132 <entry align="center"><command>TRUNCATE</command></entry>
133 <entry align="center">&mdash;</entry>
134 <entry align="center">Tables and foreign tables</entry>
135 </row>
136 <row>
137 <entry align="center" morerows="1"><literal>AFTER</literal></entry>
138 <entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry>
139 <entry align="center">Tables and foreign tables</entry>
140 <entry align="center">Tables, views, and foreign tables</entry>
141 </row>
142 <row>
143 <entry align="center"><command>TRUNCATE</command></entry>
144 <entry align="center">&mdash;</entry>
145 <entry align="center">Tables and foreign tables</entry>
146 </row>
147 <row>
148 <entry align="center" morerows="1"><literal>INSTEAD OF</literal></entry>
149 <entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry>
150 <entry align="center">Views</entry>
151 <entry align="center">&mdash;</entry>
152 </row>
153 <row>
154 <entry align="center"><command>TRUNCATE</command></entry>
155 <entry align="center">&mdash;</entry>
156 <entry align="center">&mdash;</entry>
157 </row>
158 </tbody>
159 </tgroup>
160 </informaltable>
161
162 <para>
163 Also, a trigger definition can specify a Boolean <literal>WHEN</literal>
164 condition, which will be tested to see whether the trigger should
165 be fired. In row-level triggers the <literal>WHEN</literal> condition can
166 examine the old and/or new values of columns of the row. Statement-level
167 triggers can also have <literal>WHEN</literal> conditions, although the feature
168 is not so useful for them since the condition cannot refer to any values
169 in the table.
170 </para>
171
172 <para>
173 If multiple triggers of the same kind are defined for the same event,
174 they will be fired in alphabetical order by name.
175 </para>
176
177 <para>
178 When the <literal>CONSTRAINT</literal> option is specified, this command creates a
179 <firstterm>constraint trigger</firstterm>.<indexterm><primary>trigger</primary>
180 <secondary>constraint trigger</secondary></indexterm>
181 This is the same as a regular trigger
182 except that the timing of the trigger firing can be adjusted using
183 <link linkend="sql-set-constraints"><command>SET CONSTRAINTS</command></link>.
184 Constraint triggers must be <literal>AFTER ROW</literal> triggers on plain
185 tables (not foreign tables). They
186 can be fired either at the end of the statement causing the triggering
187 event, or at the end of the containing transaction; in the latter case they
188 are said to be <firstterm>deferred</firstterm>. A pending deferred-trigger firing
189 can also be forced to happen immediately by using <command>SET
190 CONSTRAINTS</command>. Constraint triggers are expected to raise an exception
191 when the constraints they implement are violated.
192 </para>
193
194 <para>
195 The <literal>REFERENCING</literal> option enables collection
196 of <firstterm>transition relations</firstterm>, which are row sets that include all
197 of the rows inserted, deleted, or modified by the current SQL statement.
198 This feature lets the trigger see a global view of what the statement did,
199 not just one row at a time. This option is only allowed for
200 an <literal>AFTER</literal> trigger on a plain table (not a foreign table).
201 The trigger should not be a constraint trigger. Also, if the trigger is
202 an <literal>UPDATE</literal> trigger, it must not specify
203 a <replaceable class="parameter">column_name</replaceable> list when using
204 this option.
205 <literal>OLD TABLE</literal> may only be specified once, and only for a trigger
206 that can fire on <literal>UPDATE</literal> or <literal>DELETE</literal>; it creates a
207 transition relation containing the <firstterm>before-images</firstterm> of all rows
208 updated or deleted by the statement.
209 Similarly, <literal>NEW TABLE</literal> may only be specified once, and only for
210 a trigger that can fire on <literal>UPDATE</literal> or <literal>INSERT</literal>;
211 it creates a transition relation containing the <firstterm>after-images</firstterm>
212 of all rows updated or inserted by the statement.
213 </para>
214
215 <para>
216 <command>SELECT</command> does not modify any rows so you cannot
217 create <command>SELECT</command> triggers. Rules and views may provide
218 workable solutions to problems that seem to need <command>SELECT</command>
219 triggers.
220 </para>
221
222 <para>
223 Refer to <xref linkend="triggers"/> for more information about triggers.
224 </para>
225 </refsect1>
226
227 <refsect1>
228 <title>Parameters</title>
229
230 <variablelist>
231 <varlistentry>
232 <term><replaceable class="parameter">name</replaceable></term>
233 <listitem>
234 <para>
235 The name to give the new trigger. This must be distinct from
236 the name of any other trigger for the same table.
237 The name cannot be schema-qualified &mdash; the trigger inherits the
238 schema of its table. For a constraint trigger, this is also the name to
239 use when modifying the trigger's behavior using
240 <command>SET CONSTRAINTS</command>.
241 </para>
242 </listitem>
243 </varlistentry>
244
245 <varlistentry>
246 <term><literal>BEFORE</literal></term>
247 <term><literal>AFTER</literal></term>
248 <term><literal>INSTEAD OF</literal></term>
249 <listitem>
250 <para>
251 Determines whether the function is called before, after, or instead of
252 the event. A constraint trigger can only be specified as
253 <literal>AFTER</literal>.
254 </para>
255 </listitem>
256 </varlistentry>
257
258 <varlistentry>
259 <term><replaceable class="parameter">event</replaceable></term>
260 <listitem>
261 <para>
262 One of <literal>INSERT</literal>, <literal>UPDATE</literal>,
263 <literal>DELETE</literal>, or <literal>TRUNCATE</literal>;
264 this specifies the event that will fire the trigger. Multiple
265 events can be specified using <literal>OR</literal>, except when
266 transition relations are requested.
267 </para>
268
269 <para>
270 For <literal>UPDATE</literal> events, it is possible to
271 specify a list of columns using this syntax:
272 <synopsis>
273 UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</replaceable> ... ]
274 </synopsis>
275 The trigger will only fire if at least one of the listed columns
276 is mentioned as a target of the <command>UPDATE</command> command
277 or if one of the listed columns is a generated column that depends on a
278 column that is the target of the <command>UPDATE</command>.
279 </para>
280
281 <para>
282 <literal>INSTEAD OF UPDATE</literal> events do not allow a list of columns.
283 A column list cannot be specified when requesting transition relations,
284 either.
285 </para>
286 </listitem>
287 </varlistentry>
288
289 <varlistentry>
290 <term><replaceable class="parameter">table_name</replaceable></term>
291 <listitem>
292 <para>
293 The name (optionally schema-qualified) of the table, view, or foreign
294 table the trigger is for.
295 </para>
296 </listitem>
297 </varlistentry>
298
299 <varlistentry>
300 <term><replaceable class="parameter">referenced_table_name</replaceable></term>
301 <listitem>
302 <para>
303 The (possibly schema-qualified) name of another table referenced by the
304 constraint. This option is used for foreign-key constraints and is not
305 recommended for general use. This can only be specified for
306 constraint triggers.
307 </para>
308 </listitem>
309 </varlistentry>
310
311 <varlistentry>
312 <term><literal>DEFERRABLE</literal></term>
313 <term><literal>NOT DEFERRABLE</literal></term>
314 <term><literal>INITIALLY IMMEDIATE</literal></term>
315 <term><literal>INITIALLY DEFERRED</literal></term>
316 <listitem>
317 <para>
318 The default timing of the trigger.
319 See the <xref linkend="sql-createtable"/> documentation for details of
320 these constraint options. This can only be specified for constraint
321 triggers.
322 </para>
323 </listitem>
324 </varlistentry>
325
326 <varlistentry>
327 <term><literal>REFERENCING</literal></term>
328 <listitem>
329 <para>
330 This keyword immediately precedes the declaration of one or two
331 relation names that provide access to the transition relations of the
332 triggering statement.
333 </para>
334 </listitem>
335 </varlistentry>
336
337 <varlistentry>
338 <term><literal>OLD TABLE</literal></term>
339 <term><literal>NEW TABLE</literal></term>
340 <listitem>
341 <para>
342 This clause indicates whether the following relation name is for the
343 before-image transition relation or the after-image transition
344 relation.
345 </para>
346 </listitem>
347 </varlistentry>
348
349 <varlistentry>
350 <term><replaceable class="parameter">transition_relation_name</replaceable></term>
351 <listitem>
352 <para>
353 The (unqualified) name to be used within the trigger for this
354 transition relation.
355 </para>
356 </listitem>
357 </varlistentry>
358
359 <varlistentry>
360 <term><literal>FOR EACH ROW</literal></term>
361 <term><literal>FOR EACH STATEMENT</literal></term>
362
363 <listitem>
364 <para>
365 This specifies whether the trigger function should be fired
366 once for every row affected by the trigger event, or just once
367 per SQL statement. If neither is specified, <literal>FOR EACH
368 STATEMENT</literal> is the default. Constraint triggers can only
369 be specified <literal>FOR EACH ROW</literal>.
370 </para>
371 </listitem>
372 </varlistentry>
373
374 <varlistentry>
375 <term><replaceable class="parameter">condition</replaceable></term>
376 <listitem>
377 <para>
378 A Boolean expression that determines whether the trigger function
379 will actually be executed. If <literal>WHEN</literal> is specified, the
380 function will only be called if the <replaceable
381 class="parameter">condition</replaceable> returns <literal>true</literal>.
382 In <literal>FOR EACH ROW</literal> triggers, the <literal>WHEN</literal>
383 condition can refer to columns of the old and/or new row values
384 by writing <literal>OLD.<replaceable
385 class="parameter">column_name</replaceable></literal> or
386 <literal>NEW.<replaceable
387 class="parameter">column_name</replaceable></literal> respectively.
388 Of course, <literal>INSERT</literal> triggers cannot refer to <literal>OLD</literal>
389 and <literal>DELETE</literal> triggers cannot refer to <literal>NEW</literal>.
390 </para>
391
392 <para><literal>INSTEAD OF</literal> triggers do not support <literal>WHEN</literal>
393 conditions.
394 </para>
395
396 <para>
397 Currently, <literal>WHEN</literal> expressions cannot contain
398 subqueries.
399 </para>
400
401 <para>
402 Note that for constraint triggers, evaluation of the <literal>WHEN</literal>
403 condition is not deferred, but occurs immediately after the row update
404 operation is performed. If the condition does not evaluate to true then
405 the trigger is not queued for deferred execution.
406 </para>
407 </listitem>
408 </varlistentry>
409
410 <varlistentry>
411 <term><replaceable class="parameter">function_name</replaceable></term>
412 <listitem>
413 <para>
414 A user-supplied function that is declared as taking no arguments
415 and returning type <literal>trigger</literal>, which is executed when
416 the trigger fires.
417 </para>
418
419 <para>
420 In the syntax of <literal>CREATE TRIGGER</literal>, the keywords
421 <literal>FUNCTION</literal> and <literal>PROCEDURE</literal> are
422 equivalent, but the referenced function must in any case be a function,
423 not a procedure. The use of the keyword <literal>PROCEDURE</literal>
424 here is historical and deprecated.
425 </para>
426 </listitem>
427 </varlistentry>
428
429 <varlistentry>
430 <term><replaceable class="parameter">arguments</replaceable></term>
431 <listitem>
432 <para>
433 An optional comma-separated list of arguments to be provided to
434 the function when the trigger is executed. The arguments are
435 literal string constants. Simple names and numeric constants
436 can be written here, too, but they will all be converted to
437 strings. Please check the description of the implementation
438 language of the trigger function to find out how these arguments
439 can be accessed within the function; it might be different from
440 normal function arguments.
441 </para>
442 </listitem>
443 </varlistentry>
444 </variablelist>
445 </refsect1>
446
447 <refsect1 id="sql-createtrigger-notes">
448 <title>Notes</title>
449
450 <para>
451 To create or replace a trigger on a table, the user must have the
452 <literal>TRIGGER</literal> privilege on the table. The user must
453 also have <literal>EXECUTE</literal> privilege on the trigger function.
454 </para>
455
456 <para>
457 Use <link linkend="sql-droptrigger"><command>DROP TRIGGER</command></link> to remove a trigger.
458 </para>
459
460 <para>
461 Creating a row-level trigger on a partitioned table will cause an
462 identical <quote>clone</quote> trigger to be created on each of its
463 existing partitions; and any partitions created or attached later will have
464 an identical trigger, too. If there is a conflictingly-named trigger on a
465 child partition already, an error occurs unless <command>CREATE OR REPLACE
466 TRIGGER</command> is used, in which case that trigger is replaced with a
467 clone trigger. When a partition is detached from its parent, its clone
468 triggers are removed.
469 </para>
470
471 <para>
472 A column-specific trigger (one defined using the <literal>UPDATE OF
473 <replaceable>column_name</replaceable></literal> syntax) will fire when any
474 of its columns are listed as targets in the <command>UPDATE</command>
475 command's <literal>SET</literal> list. It is possible for a column's value
476 to change even when the trigger is not fired, because changes made to the
477 row's contents by <literal>BEFORE UPDATE</literal> triggers are not considered.
478 Conversely, a command such as <literal>UPDATE ... SET x = x ...</literal>
479 will fire a trigger on column <literal>x</literal>, even though the column's
480 value did not change.
481 </para>
482
483 <para>
484 In a <literal>BEFORE</literal> trigger, the <literal>WHEN</literal> condition is
485 evaluated just before the function is or would be executed, so using
486 <literal>WHEN</literal> is not materially different from testing the same
487 condition at the beginning of the trigger function. Note in particular
488 that the <literal>NEW</literal> row seen by the condition is the current value,
489 as possibly modified by earlier triggers. Also, a <literal>BEFORE</literal>
490 trigger's <literal>WHEN</literal> condition is not allowed to examine the
491 system columns of the <literal>NEW</literal> row (such as <literal>ctid</literal>),
492 because those won't have been set yet.
493 </para>
494
495 <para>
496 In an <literal>AFTER</literal> trigger, the <literal>WHEN</literal> condition is
497 evaluated just after the row update occurs, and it determines whether an
498 event is queued to fire the trigger at the end of statement. So when an
499 <literal>AFTER</literal> trigger's <literal>WHEN</literal> condition does not return
500 true, it is not necessary to queue an event nor to re-fetch the row at end
501 of statement. This can result in significant speedups in statements that
502 modify many rows, if the trigger only needs to be fired for a few of the
503 rows.
504 </para>
505
506 <para>
507 In some cases it is possible for a single SQL command to fire more than
508 one kind of trigger. For instance an <command>INSERT</command> with
509 an <literal>ON CONFLICT DO UPDATE</literal> clause may cause both insert and
510 update operations, so it will fire both kinds of triggers as needed.
511 The transition relations supplied to triggers are
512 specific to their event type; thus an <command>INSERT</command> trigger
513 will see only the inserted rows, while an <command>UPDATE</command>
514 trigger will see only the updated rows.
515 </para>
516
517 <para>
518 Row updates or deletions caused by foreign-key enforcement actions, such
519 as <literal>ON UPDATE CASCADE</literal> or <literal>ON DELETE SET NULL</literal>, are
520 treated as part of the SQL command that caused them (note that such
521 actions are never deferred). Relevant triggers on the affected table will
522 be fired, so that this provides another way in which an SQL command might
523 fire triggers not directly matching its type. In simple cases, triggers
524 that request transition relations will see all changes caused in their
525 table by a single original SQL command as a single transition relation.
526 However, there are cases in which the presence of an <literal>AFTER ROW</literal>
527 trigger that requests transition relations will cause the foreign-key
528 enforcement actions triggered by a single SQL command to be split into
529 multiple steps, each with its own transition relation(s). In such cases,
530 any statement-level triggers that are present will be fired once per
531 creation of a transition relation set, ensuring that the triggers see
532 each affected row in a transition relation once and only once.
533 </para>
534
535 <para>
536 Statement-level triggers on a view are fired only if the action on the
537 view is handled by a row-level <literal>INSTEAD OF</literal> trigger.
538 If the action is handled by an <literal>INSTEAD</literal> rule, then
539 whatever statements are emitted by the rule are executed in place of the
540 original statement naming the view, so that the triggers that will be
541 fired are those on tables named in the replacement statements.
542 Similarly, if the view is automatically updatable, then the action is
543 handled by automatically rewriting the statement into an action on the
544 view's base table, so that the base table's statement-level triggers are
545 the ones that are fired.
546 </para>
547
548 <para>
549 Modifying a partitioned table or a table with inheritance children fires
550 statement-level triggers attached to the explicitly named table, but not
551 statement-level triggers for its partitions or child tables. In contrast,
552 row-level triggers are fired on the rows in affected partitions or
553 child tables, even if they are not explicitly named in the query.
554 If a statement-level trigger has been defined with transition relations
555 named by a <literal>REFERENCING</literal> clause, then before and after
556 images of rows are visible from all affected partitions or child tables.
557 In the case of inheritance children, the row images include only columns
558 that are present in the table that the trigger is attached to.
559 </para>
560
561 <para>
562 Currently, row-level triggers with transition relations cannot be defined
563 on partitions or inheritance child tables. Also, triggers on partitioned
564 tables may not be <literal>INSTEAD OF</literal>.
565 </para>
566
567 <para>
568 Currently, the <literal>OR REPLACE</literal> option is not supported for
569 constraint triggers.
570 </para>
571
572 <para>
573 Replacing an existing trigger within a transaction that has already
574 performed updating actions on the trigger's table is not recommended.
575 Trigger firing decisions, or portions of firing decisions, that have
576 already been made will not be reconsidered, so the effects could be
577 surprising.
578 </para>
579
580 <para>
581 There are a few built-in trigger functions that can be used to
582 solve common problems without having to write your own trigger code;
583 see <xref linkend="functions-trigger"/>.
584 </para>
585 </refsect1>
586
587 <refsect1 id="sql-createtrigger-examples">
588 <title>Examples</title>
589
590 <para>
591 Execute the function <function>check_account_update</function> whenever
592 a row of the table <literal>accounts</literal> is about to be updated:
593
594 <programlisting>
595 CREATE TRIGGER check_update
596 BEFORE UPDATE ON accounts
597 FOR EACH ROW
598 EXECUTE FUNCTION check_account_update();
599 </programlisting>
600
601 Modify that trigger definition to only execute the function if
602 column <literal>balance</literal> is specified as a target in
603 the <command>UPDATE</command> command:
604
605 <programlisting>
606 CREATE OR REPLACE TRIGGER check_update
607 BEFORE UPDATE OF balance ON accounts
608 FOR EACH ROW
609 EXECUTE FUNCTION check_account_update();
610 </programlisting>
611
612 This form only executes the function if column <literal>balance</literal>
613 has in fact changed value:
614
615 <programlisting>
616 CREATE TRIGGER check_update
617 BEFORE UPDATE ON accounts
618 FOR EACH ROW
619 WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
620 EXECUTE FUNCTION check_account_update();
621 </programlisting>
622
623 Call a function to log updates of <literal>accounts</literal>, but only if
624 something changed:
625
626 <programlisting>
627 CREATE TRIGGER log_update
628 AFTER UPDATE ON accounts
629 FOR EACH ROW
630 WHEN (OLD.* IS DISTINCT FROM NEW.*)
631 EXECUTE FUNCTION log_account_update();
632 </programlisting>
633
634 Execute the function <function>view_insert_row</function> for each row to insert
635 rows into the tables underlying a view:
636
637 <programlisting>
638 CREATE TRIGGER view_insert
639 INSTEAD OF INSERT ON my_view
640 FOR EACH ROW
641 EXECUTE FUNCTION view_insert_row();
642 </programlisting>
643
644 Execute the function <function>check_transfer_balances_to_zero</function> for each
645 statement to confirm that the <literal>transfer</literal> rows offset to a net of
646 zero:
647
648 <programlisting>
649 CREATE TRIGGER transfer_insert
650 AFTER INSERT ON transfer
651 REFERENCING NEW TABLE AS inserted
652 FOR EACH STATEMENT
653 EXECUTE FUNCTION check_transfer_balances_to_zero();
654 </programlisting>
655
656 Execute the function <function>check_matching_pairs</function> for each row to
657 confirm that changes are made to matching pairs at the same time (by the
658 same statement):
659
660 <programlisting>
661 CREATE TRIGGER paired_items_update
662 AFTER UPDATE ON paired_items
663 REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
664 FOR EACH ROW
665 EXECUTE FUNCTION check_matching_pairs();
666 </programlisting>
667 </para>
668
669 <para>
670 <xref linkend="trigger-example"/> contains a complete example of a trigger
671 function written in C.
672 </para>
673 </refsect1>
674
675 <refsect1 id="sql-createtrigger-compatibility">
676 <title>Compatibility</title>
677
678 <!--
679 It's not clear whether SQL/MED contemplates triggers on foreign tables.
680 Its <drop basic column definition> General Rules do mention the possibility
681 of a reference from a trigger column list. On the other hand, nothing
682 overrides the fact that CREATE TRIGGER only targets base tables. For now,
683 do not document the compatibility status of triggers on foreign tables.
684 -->
685
686 <para>
687 The <command>CREATE TRIGGER</command> statement in
688 <productname>PostgreSQL</productname> implements a subset of the
689 <acronym>SQL</acronym> standard. The following functionalities are currently
690 missing:
691
692 <itemizedlist>
693 <listitem>
694 <para>
695 While transition table names for <literal>AFTER</literal> triggers are
696 specified using the <literal>REFERENCING</literal> clause in the standard way,
697 the row variables used in <literal>FOR EACH ROW</literal> triggers may not be
698 specified in a <literal>REFERENCING</literal> clause. They are available in a
699 manner that is dependent on the language in which the trigger function
700 is written, but is fixed for any one language. Some languages
701 effectively behave as though there is a <literal>REFERENCING</literal> clause
702 containing <literal>OLD ROW AS OLD NEW ROW AS NEW</literal>.
703 </para>
704 </listitem>
705
706 <listitem>
707 <para>
708 The standard allows transition tables to be used with
709 column-specific <literal>UPDATE</literal> triggers, but then the set of rows
710 that should be visible in the transition tables depends on the
711 trigger's column list. This is not currently implemented by
712 <productname>PostgreSQL</productname>.
713 </para>
714 </listitem>
715
716 <listitem>
717 <para>
718 <productname>PostgreSQL</productname> only allows the execution
719 of a user-defined function for the triggered action. The standard
720 allows the execution of a number of other SQL commands, such as
721 <command>CREATE TABLE</command>, as the triggered action. This
722 limitation is not hard to work around by creating a user-defined
723 function that executes the desired commands.
724 </para>
725 </listitem>
726
727 </itemizedlist>
728 </para>
729
730 <para>
731 SQL specifies that multiple triggers should be fired in
732 time-of-creation order. <productname>PostgreSQL</productname> uses
733 name order, which was judged to be more convenient.
734 </para>
735
736 <para>
737 SQL specifies that <literal>BEFORE DELETE</literal> triggers on cascaded
738 deletes fire <emphasis>after</emphasis> the cascaded <literal>DELETE</literal> completes.
739 The <productname>PostgreSQL</productname> behavior is for <literal>BEFORE
740 DELETE</literal> to always fire before the delete action, even a cascading
741 one. This is considered more consistent. There is also nonstandard
742 behavior if <literal>BEFORE</literal> triggers modify rows or prevent
743 updates during an update that is caused by a referential action. This can
744 lead to constraint violations or stored data that does not honor the
745 referential constraint.
746 </para>
747
748 <para>
749 The ability to specify multiple actions for a single trigger using
750 <literal>OR</literal> is a <productname>PostgreSQL</productname> extension of
751 the SQL standard.
752 </para>
753
754 <para>
755 The ability to fire triggers for <command>TRUNCATE</command> is a
756 <productname>PostgreSQL</productname> extension of the SQL standard, as is the
757 ability to define statement-level triggers on views.
758 </para>
759
760 <para>
761 <command>CREATE CONSTRAINT TRIGGER</command> is a
762 <productname>PostgreSQL</productname> extension of the <acronym>SQL</acronym>
763 standard.
764 So is the <literal>OR REPLACE</literal> option.
765 </para>
766
767 </refsect1>
768
769 <refsect1>
770 <title>See Also</title>
771
772 <simplelist type="inline">
773 <member><xref linkend="sql-altertrigger"/></member>
774 <member><xref linkend="sql-droptrigger"/></member>
775 <member><xref linkend="sql-createfunction"/></member>
776 <member><xref linkend="sql-set-constraints"/></member>
777 </simplelist>
778 </refsect1>
779 </refentry>