Debian Codegraph @

doc/src/sgml/trigger.sgml

raw
1 <!-- doc/src/sgml/trigger.sgml -->
2
3 <chapter id="triggers">
4 <title>Triggers</title>
5
6 <indexterm zone="triggers">
7 <primary>trigger</primary>
8 </indexterm>
9
10 <para>
11 This chapter provides general information about writing trigger functions.
12 Trigger functions can be written in most of the available procedural
13 languages, including
14 <application>PL/pgSQL</application> (<xref linkend="plpgsql"/>),
15 <application>PL/Tcl</application> (<xref linkend="pltcl"/>),
16 <application>PL/Perl</application> (<xref linkend="plperl"/>), and
17 <application>PL/Python</application> (<xref linkend="plpython"/>).
18 After reading this chapter, you should consult the chapter for
19 your favorite procedural language to find out the language-specific
20 details of writing a trigger in it.
21 </para>
22
23 <para>
24 It is also possible to write a trigger function in C, although
25 most people find it easier to use one of the procedural languages.
26 It is not currently possible to write a trigger function in the
27 plain SQL function language.
28 </para>
29
30 <sect1 id="trigger-definition">
31 <title>Overview of Trigger Behavior</title>
32
33 <para>
34 A trigger is a specification that the database should automatically
35 execute a particular function whenever a certain type of operation is
36 performed. Triggers can be attached to tables (partitioned or not),
37 views, and foreign tables.
38 </para>
39
40 <para>
41 On tables and foreign tables, triggers can be defined to execute either
42 before or after any <command>INSERT</command>, <command>UPDATE</command>,
43 or <command>DELETE</command> operation, either once per modified row,
44 or once per <acronym>SQL</acronym> statement.
45 <command>UPDATE</command> triggers can moreover be set to fire only if
46 certain columns are mentioned in the <literal>SET</literal> clause of
47 the <command>UPDATE</command> statement. Triggers can also fire
48 for <command>TRUNCATE</command> statements. If a trigger event occurs,
49 the trigger's function is called at the appropriate time to handle the
50 event.
51 </para>
52
53 <para>
54 On views, triggers can be defined to execute instead of
55 <command>INSERT</command>, <command>UPDATE</command>, or
56 <command>DELETE</command> operations.
57 Such <literal>INSTEAD OF</literal> triggers
58 are fired once for each row that needs to be modified in the view.
59 It is the responsibility of the
60 trigger's function to perform the necessary modifications to the view's
61 underlying base table(s) and, where appropriate, return the modified
62 row as it will appear in the view. Triggers on views can also be defined
63 to execute once per <acronym>SQL</acronym> statement, before or after
64 <command>INSERT</command>, <command>UPDATE</command>, or
65 <command>DELETE</command> operations.
66 However, such triggers are fired only if there is also
67 an <literal>INSTEAD OF</literal> trigger on the view. Otherwise,
68 any statement targeting the view must be rewritten into a statement
69 affecting its underlying base table(s), and then the triggers
70 that will be fired are the ones attached to the base table(s).
71 </para>
72
73 <para>
74 The trigger function must be defined before the trigger itself can be
75 created. The trigger function must be declared as a
76 function taking no arguments and returning type <literal>trigger</literal>.
77 (The trigger function receives its input through a specially-passed
78 <structname>TriggerData</structname> structure, not in the form of ordinary function
79 arguments.)
80 </para>
81
82 <para>
83 Once a suitable trigger function has been created, the trigger is
84 established with
85 <xref linkend="sql-createtrigger"/>.
86 The same trigger function can be used for multiple triggers.
87 </para>
88
89 <para>
90 <productname>PostgreSQL</productname> offers both <firstterm>per-row</firstterm>
91 triggers and <firstterm>per-statement</firstterm> triggers. With a per-row
92 trigger, the trigger function
93 is invoked once for each row that is affected by the statement
94 that fired the trigger. In contrast, a per-statement trigger is
95 invoked only once when an appropriate statement is executed,
96 regardless of the number of rows affected by that statement. In
97 particular, a statement that affects zero rows will still result
98 in the execution of any applicable per-statement triggers. These
99 two types of triggers are sometimes called <firstterm>row-level</firstterm>
100 triggers and <firstterm>statement-level</firstterm> triggers,
101 respectively. Triggers on <command>TRUNCATE</command> may only be
102 defined at statement level, not per-row.
103 </para>
104
105 <para>
106 Triggers are also classified according to whether they fire
107 <firstterm>before</firstterm>, <firstterm>after</firstterm>, or
108 <firstterm>instead of</firstterm> the operation. These are referred to
109 as <literal>BEFORE</literal> triggers, <literal>AFTER</literal> triggers, and
110 <literal>INSTEAD OF</literal> triggers respectively.
111 Statement-level <literal>BEFORE</literal> triggers naturally fire before the
112 statement starts to do anything, while statement-level <literal>AFTER</literal>
113 triggers fire at the very end of the statement. These types of
114 triggers may be defined on tables, views, or foreign tables. Row-level
115 <literal>BEFORE</literal> triggers fire immediately before a particular row is
116 operated on, while row-level <literal>AFTER</literal> triggers fire at the end of
117 the statement (but before any statement-level <literal>AFTER</literal> triggers).
118 These types of triggers may only be defined on tables and
119 foreign tables, not views.
120 <literal>INSTEAD OF</literal> triggers may only be
121 defined on views, and only at row level; they fire immediately as each
122 row in the view is identified as needing to be operated on.
123 </para>
124
125 <para>
126 The execution of an <literal>AFTER</literal> trigger can be deferred
127 to the end of the transaction, rather than the end of the statement,
128 if it was defined as a <firstterm>constraint trigger</firstterm>.
129 In all cases, a trigger is executed as part of the same transaction as
130 the statement that triggered it, so if either the statement or the
131 trigger causes an error, the effects of both will be rolled back.
132 Also, the trigger will always run as the role that queued the trigger
133 event, unless the trigger function is marked as <literal>SECURITY
134 DEFINER</literal>, in which case it will run as the function owner.
135 </para>
136
137 <para>
138 If an <command>INSERT</command> contains an <literal>ON CONFLICT
139 DO UPDATE</literal> clause, it is possible for row-level
140 <literal>BEFORE</literal> <command>INSERT</command> and then
141 <literal>BEFORE</literal> <command>UPDATE</command> triggers
142 to be executed on triggered rows. Such interactions can be
143 complex if the triggers are not idempotent because change made by
144 <literal>BEFORE</literal> <command>INSERT</command> triggers will be
145 seen by <literal>BEFORE</literal> <command>UPDATE</command> triggers,
146 including changes to <varname>EXCLUDED</varname> columns.
147 </para>
148
149 <para>
150 Note that statement-level
151 <command>UPDATE</command> triggers are executed when <literal>ON
152 CONFLICT DO UPDATE</literal> is specified, regardless of whether or not
153 any rows were affected by the <command>UPDATE</command> (and
154 regardless of whether the alternative <command>UPDATE</command>
155 path was ever taken). An <command>INSERT</command> with an
156 <literal>ON CONFLICT DO UPDATE</literal> clause will execute
157 statement-level <literal>BEFORE</literal> <command>INSERT</command>
158 triggers first, then statement-level <literal>BEFORE</literal>
159 <command>UPDATE</command> triggers, followed by statement-level
160 <literal>AFTER</literal> <command>UPDATE</command> triggers and finally
161 statement-level <literal>AFTER</literal> <command>INSERT</command>
162 triggers.
163 </para>
164
165 <para>
166 A statement that targets a parent table in an inheritance or partitioning
167 hierarchy does not cause the statement-level triggers of affected child
168 tables to be fired; only the parent table's statement-level triggers are
169 fired. However, row-level triggers of any affected child tables will be
170 fired.
171 </para>
172
173 <para>
174 If an <command>UPDATE</command> on a partitioned table causes a row to move
175 to another partition, it will be performed as a <command>DELETE</command>
176 from the original partition followed by an <command>INSERT</command> into
177 the new partition. In this case, all row-level <literal>BEFORE</literal>
178 <command>UPDATE</command> triggers and all row-level
179 <literal>BEFORE</literal> <command>DELETE</command> triggers are fired on
180 the original partition. Then all row-level <literal>BEFORE</literal>
181 <command>INSERT</command> triggers are fired on the destination partition.
182 The possibility of surprising outcomes should be considered when all these
183 triggers affect the row being moved. As far as <literal>AFTER ROW</literal>
184 triggers are concerned, <literal>AFTER</literal> <command>DELETE</command>
185 and <literal>AFTER</literal> <command>INSERT</command> triggers are
186 applied; but <literal>AFTER</literal> <command>UPDATE</command> triggers
187 are not applied because the <command>UPDATE</command> has been converted to
188 a <command>DELETE</command> and an <command>INSERT</command>. As far as
189 statement-level triggers are concerned, none of the
190 <command>DELETE</command> or <command>INSERT</command> triggers are fired,
191 even if row movement occurs; only the <command>UPDATE</command> triggers
192 defined on the target table used in the <command>UPDATE</command> statement
193 will be fired.
194 </para>
195
196 <para>
197 No separate triggers are defined for <command>MERGE</command>. Instead,
198 statement-level or row-level <command>UPDATE</command>,
199 <command>DELETE</command>, and <command>INSERT</command> triggers are fired
200 depending on (for statement-level triggers) what actions are specified in
201 the <command>MERGE</command> query and (for row-level triggers) what
202 actions are performed.
203 </para>
204
205 <para>
206 While running a <command>MERGE</command> command, statement-level
207 <literal>BEFORE</literal> and <literal>AFTER</literal> triggers are
208 fired for events specified in the actions of the <command>MERGE</command>
209 command, irrespective of whether or not the action is ultimately performed.
210 This is the same as an <command>UPDATE</command> statement that updates
211 no rows, yet statement-level triggers are fired.
212 The row-level triggers are fired only when a row is actually updated,
213 inserted or deleted. So it's perfectly legal that while statement-level
214 triggers are fired for certain types of action, no row-level triggers
215 are fired for the same kind of action.
216 </para>
217
218 <para>
219 Trigger functions invoked by per-statement triggers should always
220 return <symbol>NULL</symbol>. Trigger functions invoked by per-row
221 triggers can return a table row (a value of
222 type <structname>HeapTuple</structname>) to the calling executor,
223 if they choose. A row-level trigger fired before an operation has
224 the following choices:
225
226 <itemizedlist>
227 <listitem>
228 <para>
229 It can return <symbol>NULL</symbol> to skip the operation for the
230 current row. This instructs the executor to not perform the
231 row-level operation that invoked the trigger (the insertion,
232 modification, or deletion of a particular table row).
233 </para>
234 </listitem>
235
236 <listitem>
237 <para>
238 For row-level <command>INSERT</command>
239 and <command>UPDATE</command> triggers only, the returned row
240 becomes the row that will be inserted or will replace the row
241 being updated. This allows the trigger function to modify the
242 row being inserted or updated.
243 </para>
244 </listitem>
245 </itemizedlist>
246
247 A row-level <literal>BEFORE</literal> trigger that does not intend to cause
248 either of these behaviors must be careful to return as its result the same
249 row that was passed in (that is, the <varname>NEW</varname> row
250 for <command>INSERT</command> and <command>UPDATE</command>
251 triggers, the <varname>OLD</varname> row for
252 <command>DELETE</command> triggers).
253 </para>
254
255 <para>
256 A row-level <literal>INSTEAD OF</literal> trigger should either return
257 <symbol>NULL</symbol> to indicate that it did not modify any data from
258 the view's underlying base tables, or it should return the view
259 row that was passed in (the <varname>NEW</varname> row
260 for <command>INSERT</command> and <command>UPDATE</command>
261 operations, or the <varname>OLD</varname> row for
262 <command>DELETE</command> operations). A nonnull return value is
263 used to signal that the trigger performed the necessary data
264 modifications in the view. This will cause the count of the number
265 of rows affected by the command to be incremented. For
266 <command>INSERT</command> and <command>UPDATE</command> operations only, the trigger
267 may modify the <varname>NEW</varname> row before returning it. This will
268 change the data returned by
269 <command>INSERT RETURNING</command> or <command>UPDATE RETURNING</command>,
270 and is useful when the view will not show exactly the same data
271 that was provided.
272 </para>
273
274 <para>
275 The return value is ignored for row-level triggers fired after an
276 operation, and so they can return <symbol>NULL</symbol>.
277 </para>
278
279 <para>
280 Some considerations apply for generated
281 columns.<indexterm><primary>generated column</primary><secondary>in
282 triggers</secondary></indexterm> Stored generated columns are computed after
283 <literal>BEFORE</literal> triggers and before <literal>AFTER</literal>
284 triggers. Therefore, the generated value can be inspected in
285 <literal>AFTER</literal> triggers. In <literal>BEFORE</literal> triggers,
286 the <literal>OLD</literal> row contains the old generated value, as one
287 would expect, but the <literal>NEW</literal> row does not yet contain the
288 new generated value and should not be accessed. In the C language
289 interface, the content of the column is undefined at this point; a
290 higher-level programming language should prevent access to a stored
291 generated column in the <literal>NEW</literal> row in a
292 <literal>BEFORE</literal> trigger. Changes to the value of a generated
293 column in a <literal>BEFORE</literal> trigger are ignored and will be
294 overwritten.
295 Virtual generated columns are never computed when triggers fire. In the C
296 language interface, their content is undefined in a trigger function.
297 Higher-level programming languages should prevent access to virtual
298 generated columns in triggers.
299 </para>
300
301 <para>
302 If more than one trigger is defined for the same event on the same
303 relation, the triggers will be fired in alphabetical order by
304 trigger name. In the case of <literal>BEFORE</literal> and
305 <literal>INSTEAD OF</literal> triggers, the possibly-modified row returned by
306 each trigger becomes the input to the next trigger. If any
307 <literal>BEFORE</literal> or <literal>INSTEAD OF</literal> trigger returns
308 <symbol>NULL</symbol>, the operation is abandoned for that row and subsequent
309 triggers are not fired (for that row).
310 </para>
311
312 <para>
313 A trigger definition can also specify a Boolean <literal>WHEN</literal>
314 condition, which will be tested to see whether the trigger should
315 be fired. In row-level triggers the <literal>WHEN</literal> condition can
316 examine the old and/or new values of columns of the row. (Statement-level
317 triggers can also have <literal>WHEN</literal> conditions, although the feature
318 is not so useful for them.) In a <literal>BEFORE</literal> trigger, the
319 <literal>WHEN</literal>
320 condition is evaluated just before the function is or would be executed,
321 so using <literal>WHEN</literal> is not materially different from testing the
322 same condition at the beginning of the trigger function. However, in
323 an <literal>AFTER</literal> trigger, the <literal>WHEN</literal> condition is evaluated
324 just after the row update occurs, and it determines whether an event is
325 queued to fire the trigger at the end of statement. So when an
326 <literal>AFTER</literal> trigger's
327 <literal>WHEN</literal> condition does not return true, it is not necessary
328 to queue an event nor to re-fetch the row at end of statement. This
329 can result in significant speedups in statements that modify many
330 rows, if the trigger only needs to be fired for a few of the rows.
331 <literal>INSTEAD OF</literal> triggers do not support
332 <literal>WHEN</literal> conditions.
333 </para>
334
335 <para>
336 Typically, row-level <literal>BEFORE</literal> triggers are used for checking or
337 modifying the data that will be inserted or updated. For example,
338 a <literal>BEFORE</literal> trigger might be used to insert the current time into a
339 <type>timestamp</type> column, or to check that two elements of the row are
340 consistent. Row-level <literal>AFTER</literal> triggers are most sensibly
341 used to propagate the updates to other tables, or make consistency
342 checks against other tables. The reason for this division of labor is
343 that an <literal>AFTER</literal> trigger can be certain it is seeing the final
344 value of the row, while a <literal>BEFORE</literal> trigger cannot; there might
345 be other <literal>BEFORE</literal> triggers firing after it. If you have no
346 specific reason to make a trigger <literal>BEFORE</literal> or
347 <literal>AFTER</literal>, the <literal>BEFORE</literal> case is more efficient, since
348 the information about
349 the operation doesn't have to be saved until end of statement.
350 </para>
351
352 <para>
353 If a trigger function executes SQL commands then these
354 commands might fire triggers again. This is known as cascading
355 triggers. There is no direct limitation on the number of cascade
356 levels. It is possible for cascades to cause a recursive invocation
357 of the same trigger; for example, an <command>INSERT</command>
358 trigger might execute a command that inserts an additional row
359 into the same table, causing the <command>INSERT</command> trigger
360 to be fired again. It is the trigger programmer's responsibility
361 to avoid infinite recursion in such scenarios.
362 </para>
363
364 <para>
365 If a foreign key constraint specifies referential actions (that
366 is, cascading updates or deletes), those actions are performed via
367 ordinary SQL <command>UPDATE</command> or <command>DELETE</command>
368 commands on the referencing table.
369 In particular, any triggers that exist on the referencing table
370 will be fired for those changes. If such a trigger modifies or
371 blocks the effect of one of these commands, the end result could
372 be to break referential integrity. It is the trigger programmer's
373 responsibility to avoid that.
374 </para>
375
376 <para>
377 <indexterm>
378 <primary>trigger</primary>
379 <secondary>arguments for trigger functions</secondary>
380 </indexterm>
381 When a trigger is being defined, arguments can be specified for
382 it. The purpose of including arguments in the
383 trigger definition is to allow different triggers with similar
384 requirements to call the same function. As an example, there
385 could be a generalized trigger function that takes as its
386 arguments two column names and puts the current user in one and
387 the current time stamp in the other. Properly written, this
388 trigger function would be independent of the specific table it is
389 triggering on. So the same function could be used for
390 <command>INSERT</command> events on any table with suitable
391 columns, to automatically track creation of records in a
392 transaction table for example. It could also be used to track
393 last-update events if defined as an <command>UPDATE</command>
394 trigger.
395 </para>
396
397 <para>
398 Each programming language that supports triggers has its own method
399 for making the trigger input data available to the trigger function.
400 This input data includes the type of trigger event (e.g.,
401 <command>INSERT</command> or <command>UPDATE</command>) as well as any
402 arguments that were listed in <command>CREATE TRIGGER</command>.
403 For a row-level trigger, the input data also includes the
404 <varname>NEW</varname> row for <command>INSERT</command> and
405 <command>UPDATE</command> triggers, and/or the <varname>OLD</varname> row
406 for <command>UPDATE</command> and <command>DELETE</command> triggers.
407 </para>
408
409 <para>
410 By default, statement-level triggers do not have any way to examine the
411 individual row(s) modified by the statement. But an <literal>AFTER
412 STATEMENT</literal> trigger can request that <firstterm>transition tables</firstterm>
413 be created to make the sets of affected rows available to the trigger.
414 <literal>AFTER ROW</literal> triggers can also request transition tables, so
415 that they can see the total changes in the table as well as the change in
416 the individual row they are currently being fired for. The method for
417 examining the transition tables again depends on the programming language
418 that is being used, but the typical approach is to make the transition
419 tables act like read-only temporary tables that can be accessed by SQL
420 commands issued within the trigger function.
421 </para>
422
423 </sect1>
424
425 <sect1 id="trigger-datachanges">
426 <title>Visibility of Data Changes</title>
427
428 <para>
429 If you execute SQL commands in your trigger function, and these
430 commands access the table that the trigger is for, then
431 you need to be aware of the data visibility rules, because they determine
432 whether these SQL commands will see the data change that the trigger
433 is fired for. Briefly:
434
435 <itemizedlist>
436
437 <listitem>
438 <para>
439 Statement-level triggers follow simple visibility rules: none of
440 the changes made by a statement are visible to statement-level
441 <literal>BEFORE</literal> triggers, whereas all
442 modifications are visible to statement-level <literal>AFTER</literal>
443 triggers.
444 </para>
445 </listitem>
446
447 <listitem>
448 <para>
449 The data change (insertion, update, or deletion) causing the
450 trigger to fire is naturally <emphasis>not</emphasis> visible
451 to SQL commands executed in a row-level <literal>BEFORE</literal> trigger,
452 because it hasn't happened yet.
453 </para>
454 </listitem>
455
456 <listitem>
457 <para>
458 However, SQL commands executed in a row-level <literal>BEFORE</literal>
459 trigger <emphasis>will</emphasis> see the effects of data
460 changes for rows previously processed in the same outer
461 command. This requires caution, since the ordering of these
462 change events is not in general predictable; an SQL command that
463 affects multiple rows can visit the rows in any order.
464 </para>
465 </listitem>
466
467 <listitem>
468 <para>
469 Similarly, a row-level <literal>INSTEAD OF</literal> trigger will see the
470 effects of data changes made by previous firings of <literal>INSTEAD
471 OF</literal> triggers in the same outer command.
472 </para>
473 </listitem>
474
475 <listitem>
476 <para>
477 When a row-level <literal>AFTER</literal> trigger is fired, all data
478 changes made
479 by the outer command are already complete, and are visible to
480 the invoked trigger function.
481 </para>
482 </listitem>
483 </itemizedlist>
484 </para>
485
486 <para>
487 If your trigger function is written in any of the standard procedural
488 languages, then the above statements apply only if the function is
489 declared <literal>VOLATILE</literal>. Functions that are declared
490 <literal>STABLE</literal> or <literal>IMMUTABLE</literal> will not see changes made by
491 the calling command in any case.
492 </para>
493
494 <para>
495 Further information about data visibility rules can be found in
496 <xref linkend="spi-visibility"/>. The example in <xref
497 linkend="trigger-example"/> contains a demonstration of these rules.
498 </para>
499 </sect1>
500
501 <sect1 id="trigger-interface">
502 <title>Writing Trigger Functions in C</title>
503
504 <indexterm zone="trigger-interface">
505 <primary>trigger</primary>
506 <secondary>in C</secondary>
507 </indexterm>
508
509 <indexterm>
510 <primary>transition tables</primary>
511 <secondary>referencing from C trigger</secondary>
512 </indexterm>
513
514 <para>
515 This section describes the low-level details of the interface to a
516 trigger function. This information is only needed when writing
517 trigger functions in C. If you are using a higher-level language then
518 these details are handled for you. In most cases you should consider
519 using a procedural language before writing your triggers in C. The
520 documentation of each procedural language explains how to write a
521 trigger in that language.
522 </para>
523
524 <para>
525 Trigger functions must use the <quote>version 1</quote> function manager
526 interface.
527 </para>
528
529 <para>
530 When a function is called by the trigger manager, it is not passed
531 any normal arguments, but it is passed a <quote>context</quote>
532 pointer pointing to a <structname>TriggerData</structname> structure. C
533 functions can check whether they were called from the trigger
534 manager or not by executing the macro:
535 <programlisting>
536 CALLED_AS_TRIGGER(fcinfo)
537 </programlisting>
538 which expands to:
539 <programlisting>
540 ((fcinfo)-&gt;context != NULL &amp;&amp; IsA((fcinfo)-&gt;context, TriggerData))
541 </programlisting>
542 If this returns true, then it is safe to cast
543 <literal>fcinfo-&gt;context</literal> to type <literal>TriggerData
544 *</literal> and make use of the pointed-to
545 <structname>TriggerData</structname> structure. The function must
546 <emphasis>not</emphasis> alter the <structname>TriggerData</structname>
547 structure or any of the data it points to.
548 </para>
549
550 <para>
551 <structname>struct TriggerData</structname> is defined in
552 <filename>commands/trigger.h</filename>:
553
554 <programlisting>
555 typedef struct TriggerData
556 {
557 NodeTag type;
558 TriggerEvent tg_event;
559 Relation tg_relation;
560 HeapTuple tg_trigtuple;
561 HeapTuple tg_newtuple;
562 Trigger *tg_trigger;
563 TupleTableSlot *tg_trigslot;
564 TupleTableSlot *tg_newslot;
565 Tuplestorestate *tg_oldtable;
566 Tuplestorestate *tg_newtable;
567 const Bitmapset *tg_updatedcols;
568 } TriggerData;
569 </programlisting>
570
571 where the members are defined as follows:
572
573 <variablelist>
574 <varlistentry>
575 <term><structfield>type</structfield></term>
576 <listitem>
577 <para>
578 Always <literal>T_TriggerData</literal>.
579 </para>
580 </listitem>
581 </varlistentry>
582
583 <varlistentry>
584 <term><structfield>tg_event</structfield></term>
585 <listitem>
586 <para>
587 Describes the event for which the function is called. You can use the
588 following macros to examine <literal>tg_event</literal>:
589
590 <variablelist>
591 <varlistentry>
592 <term><literal>TRIGGER_FIRED_BEFORE(tg_event)</literal></term>
593 <listitem>
594 <para>
595 Returns true if the trigger fired before the operation.
596 </para>
597 </listitem>
598 </varlistentry>
599
600 <varlistentry>
601 <term><literal>TRIGGER_FIRED_AFTER(tg_event)</literal></term>
602 <listitem>
603 <para>
604 Returns true if the trigger fired after the operation.
605 </para>
606 </listitem>
607 </varlistentry>
608
609 <varlistentry>
610 <term><literal>TRIGGER_FIRED_INSTEAD(tg_event)</literal></term>
611 <listitem>
612 <para>
613 Returns true if the trigger fired instead of the operation.
614 </para>
615 </listitem>
616 </varlistentry>
617
618 <varlistentry>
619 <term><literal>TRIGGER_FIRED_FOR_ROW(tg_event)</literal></term>
620 <listitem>
621 <para>
622 Returns true if the trigger fired for a row-level event.
623 </para>
624 </listitem>
625 </varlistentry>
626
627 <varlistentry>
628 <term><literal>TRIGGER_FIRED_FOR_STATEMENT(tg_event)</literal></term>
629 <listitem>
630 <para>
631 Returns true if the trigger fired for a statement-level event.
632 </para>
633 </listitem>
634 </varlistentry>
635
636 <varlistentry>
637 <term><literal>TRIGGER_FIRED_BY_INSERT(tg_event)</literal></term>
638 <listitem>
639 <para>
640 Returns true if the trigger was fired by an <command>INSERT</command> command.
641 </para>
642 </listitem>
643 </varlistentry>
644
645 <varlistentry>
646 <term><literal>TRIGGER_FIRED_BY_UPDATE(tg_event)</literal></term>
647 <listitem>
648 <para>
649 Returns true if the trigger was fired by an <command>UPDATE</command> command.
650 </para>
651 </listitem>
652 </varlistentry>
653
654 <varlistentry>
655 <term><literal>TRIGGER_FIRED_BY_DELETE(tg_event)</literal></term>
656 <listitem>
657 <para>
658 Returns true if the trigger was fired by a <command>DELETE</command> command.
659 </para>
660 </listitem>
661 </varlistentry>
662
663 <varlistentry>
664 <term><literal>TRIGGER_FIRED_BY_TRUNCATE(tg_event)</literal></term>
665 <listitem>
666 <para>
667 Returns true if the trigger was fired by a <command>TRUNCATE</command> command.
668 </para>
669 </listitem>
670 </varlistentry>
671 </variablelist>
672 </para>
673 </listitem>
674 </varlistentry>
675
676 <varlistentry>
677 <term><structfield>tg_relation</structfield></term>
678 <listitem>
679 <para>
680 A pointer to a structure describing the relation that the trigger fired for.
681 Look at <filename>utils/rel.h</filename> for details about
682 this structure. The most interesting things are
683 <literal>tg_relation-&gt;rd_att</literal> (descriptor of the relation
684 tuples) and <literal>tg_relation-&gt;rd_rel-&gt;relname</literal>
685 (relation name; the type is not <type>char*</type> but
686 <type>NameData</type>; use
687 <literal>SPI_getrelname(tg_relation)</literal> to get a <type>char*</type> if you
688 need a copy of the name).
689 </para>
690 </listitem>
691 </varlistentry>
692
693 <varlistentry>
694 <term><structfield>tg_trigtuple</structfield></term>
695 <listitem>
696 <para>
697 A pointer to the row for which the trigger was fired. This is
698 the row being inserted, updated, or deleted. If this trigger
699 was fired for an <command>INSERT</command> or
700 <command>DELETE</command> then this is what you should return
701 from the function if you don't want to replace the row with
702 a different one (in the case of <command>INSERT</command>) or
703 skip the operation. For triggers on foreign tables, values of system
704 columns herein are unspecified.
705 </para>
706 </listitem>
707 </varlistentry>
708
709 <varlistentry>
710 <term><structfield>tg_newtuple</structfield></term>
711 <listitem>
712 <para>
713 A pointer to the new version of the row, if the trigger was
714 fired for an <command>UPDATE</command>, and <symbol>NULL</symbol> if
715 it is for an <command>INSERT</command> or a
716 <command>DELETE</command>. This is what you have to return
717 from the function if the event is an <command>UPDATE</command>
718 and you don't want to replace this row by a different one or
719 skip the operation. For triggers on foreign tables, values of system
720 columns herein are unspecified.
721 </para>
722 </listitem>
723 </varlistentry>
724
725 <varlistentry>
726 <term><structfield>tg_trigger</structfield></term>
727 <listitem>
728 <para>
729 A pointer to a structure of type <structname>Trigger</structname>,
730 defined in <filename>utils/reltrigger.h</filename>:
731
732 <programlisting>
733 typedef struct Trigger
734 {
735 Oid tgoid;
736 char *tgname;
737 Oid tgfoid;
738 int16 tgtype;
739 char tgenabled;
740 bool tgisinternal;
741 bool tgisclone;
742 Oid tgconstrrelid;
743 Oid tgconstrindid;
744 Oid tgconstraint;
745 bool tgdeferrable;
746 bool tginitdeferred;
747 int16 tgnargs;
748 int16 tgnattr;
749 int16 *tgattr;
750 char **tgargs;
751 char *tgqual;
752 char *tgoldtable;
753 char *tgnewtable;
754 } Trigger;
755 </programlisting>
756
757 where <structfield>tgname</structfield> is the trigger's name,
758 <structfield>tgnargs</structfield> is the number of arguments in
759 <structfield>tgargs</structfield>, and <structfield>tgargs</structfield> is an array of
760 pointers to the arguments specified in the <command>CREATE
761 TRIGGER</command> statement. The other members are for internal use
762 only.
763 </para>
764 </listitem>
765 </varlistentry>
766
767 <varlistentry>
768 <term><structfield>tg_trigslot</structfield></term>
769 <listitem>
770 <para>
771 The slot containing <structfield>tg_trigtuple</structfield>,
772 or a <symbol>NULL</symbol> pointer if there is no such tuple.
773 </para>
774 </listitem>
775 </varlistentry>
776
777 <varlistentry>
778 <term><structfield>tg_newslot</structfield></term>
779 <listitem>
780 <para>
781 The slot containing <structfield>tg_newtuple</structfield>,
782 or a <symbol>NULL</symbol> pointer if there is no such tuple.
783 </para>
784 </listitem>
785 </varlistentry>
786
787 <varlistentry>
788 <term><structfield>tg_oldtable</structfield></term>
789 <listitem>
790 <para>
791 A pointer to a structure of type <structname>Tuplestorestate</structname>
792 containing zero or more rows in the format specified by
793 <structfield>tg_relation</structfield>, or a <symbol>NULL</symbol> pointer
794 if there is no <literal>OLD TABLE</literal> transition relation.
795 </para>
796 </listitem>
797 </varlistentry>
798
799 <varlistentry>
800 <term><structfield>tg_newtable</structfield></term>
801 <listitem>
802 <para>
803 A pointer to a structure of type <structname>Tuplestorestate</structname>
804 containing zero or more rows in the format specified by
805 <structfield>tg_relation</structfield>, or a <symbol>NULL</symbol> pointer
806 if there is no <literal>NEW TABLE</literal> transition relation.
807 </para>
808 </listitem>
809 </varlistentry>
810
811 <varlistentry>
812 <term><structfield>tg_updatedcols</structfield></term>
813 <listitem>
814 <para>
815 For <literal>UPDATE</literal> triggers, a bitmap set indicating the
816 columns that were updated by the triggering command. Generic trigger
817 functions can use this to optimize actions by not having to deal with
818 columns that were not changed.
819 </para>
820
821 <para>
822 As an example, to determine whether a column with attribute number
823 <varname>attnum</varname> (1-based) is a member of this bitmap set,
824 call <literal>bms_is_member(attnum -
825 FirstLowInvalidHeapAttributeNumber,
826 trigdata->tg_updatedcols))</literal>.
827 </para>
828
829 <para>
830 For triggers other than <literal>UPDATE</literal> triggers, this will
831 be <symbol>NULL</symbol>.
832 </para>
833 </listitem>
834 </varlistentry>
835 </variablelist>
836 </para>
837
838 <para>
839 To allow queries issued through SPI to reference transition tables, see
840 <xref linkend="spi-spi-register-trigger-data"/>.
841 </para>
842
843 <para>
844 A trigger function must return either a
845 <structname>HeapTuple</structname> pointer or a <symbol>NULL</symbol> pointer
846 (<emphasis>not</emphasis> an SQL null value, that is, do not set <parameter>isNull</parameter> true).
847 Be careful to return either
848 <structfield>tg_trigtuple</structfield> or <structfield>tg_newtuple</structfield>,
849 as appropriate, if you don't want to modify the row being operated on.
850 </para>
851 </sect1>
852
853 <sect1 id="trigger-example">
854 <title>A Complete Trigger Example</title>
855
856 <para>
857 Here is a very simple example of a trigger function written in C.
858 (Examples of triggers written in procedural languages can be found
859 in the documentation of the procedural languages.)
860 </para>
861
862 <para>
863 The function <function>trigf</function> reports the number of rows in the
864 table <structname>ttest</structname> and skips the actual operation if the
865 command attempts to insert a null value into the column
866 <structfield>x</structfield>. (So the trigger acts as a not-null constraint but
867 doesn't abort the transaction.)
868 </para>
869
870 <para>
871 First, the table definition:
872 <programlisting>
873 CREATE TABLE ttest (
874 x integer
875 );
876 </programlisting>
877 </para>
878
879 <para>
880 This is the source code of the trigger function:
881 <programlisting><![CDATA[
882 #include "postgres.h"
883 #include "fmgr.h"
884 #include "executor/spi.h" /* this is what you need to work with SPI */
885 #include "commands/trigger.h" /* ... triggers ... */
886 #include "utils/rel.h" /* ... and relations */
887
888 PG_MODULE_MAGIC;
889
890 PG_FUNCTION_INFO_V1(trigf);
891
892 Datum
893 trigf(PG_FUNCTION_ARGS)
894 {
895 TriggerData *trigdata = (TriggerData *) fcinfo->context;
896 TupleDesc tupdesc;
897 HeapTuple rettuple;
898 char *when;
899 bool checknull = false;
900 bool isnull;
901 int ret, i;
902
903 /* make sure it's called as a trigger at all */
904 if (!CALLED_AS_TRIGGER(fcinfo))
905 elog(ERROR, "trigf: not called by trigger manager");
906
907 /* tuple to return to executor */
908 if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
909 rettuple = trigdata->tg_newtuple;
910 else
911 rettuple = trigdata->tg_trigtuple;
912
913 /* check for null values */
914 if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
915 && TRIGGER_FIRED_BEFORE(trigdata->tg_event))
916 checknull = true;
917
918 if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
919 when = "before";
920 else
921 when = "after ";
922
923 tupdesc = trigdata->tg_relation->rd_att;
924
925 /* connect to SPI manager */
926 SPI_connect();
927
928 /* get number of rows in table */
929 ret = SPI_exec("SELECT count(*) FROM ttest", 0);
930
931 if (ret < 0)
932 elog(ERROR, "trigf (fired %s): SPI_exec returned %d", when, ret);
933
934 /* count(*) returns int8, so be careful to convert */
935 i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
936 SPI_tuptable->tupdesc,
937 1,
938 &isnull));
939
940 elog (INFO, "trigf (fired %s): there are %d rows in ttest", when, i);
941
942 SPI_finish();
943
944 if (checknull)
945 {
946 SPI_getbinval(rettuple, tupdesc, 1, &isnull);
947 if (isnull)
948 rettuple = NULL;
949 }
950
951 return PointerGetDatum(rettuple);
952 }
953 ]]>
954 </programlisting>
955 </para>
956
957 <para>
958 After you have compiled the source code (see <xref
959 linkend="dfunc"/>), declare the function and the triggers:
960 <programlisting>
961 CREATE FUNCTION trigf() RETURNS trigger
962 AS '<replaceable>filename</replaceable>'
963 LANGUAGE C;
964
965 CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest
966 FOR EACH ROW EXECUTE FUNCTION trigf();
967
968 CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest
969 FOR EACH ROW EXECUTE FUNCTION trigf();
970 </programlisting>
971 </para>
972
973 <para>
974 Now you can test the operation of the trigger:
975 <screen>
976 =&gt; INSERT INTO ttest VALUES (NULL);
977 INFO: trigf (fired before): there are 0 rows in ttest
978 INSERT 0 0
979
980 -- Insertion skipped and AFTER trigger is not fired
981
982 =&gt; SELECT * FROM ttest;
983 x
984 ---
985 (0 rows)
986
987 =&gt; INSERT INTO ttest VALUES (1);
988 INFO: trigf (fired before): there are 0 rows in ttest
989 INFO: trigf (fired after ): there are 1 rows in ttest
990 ^^^^^^^^
991 remember what we said about visibility.
992 INSERT 167793 1
993 vac=&gt; SELECT * FROM ttest;
994 x
995 ---
996 1
997 (1 row)
998
999 =&gt; INSERT INTO ttest SELECT x * 2 FROM ttest;
1000 INFO: trigf (fired before): there are 1 rows in ttest
1001 INFO: trigf (fired after ): there are 2 rows in ttest
1002 ^^^^^^
1003 remember what we said about visibility.
1004 INSERT 167794 1
1005 =&gt; SELECT * FROM ttest;
1006 x
1007 ---
1008 1
1009 2
1010 (2 rows)
1011
1012 =&gt; UPDATE ttest SET x = NULL WHERE x = 2;
1013 INFO: trigf (fired before): there are 2 rows in ttest
1014 UPDATE 0
1015 =&gt; UPDATE ttest SET x = 4 WHERE x = 2;
1016 INFO: trigf (fired before): there are 2 rows in ttest
1017 INFO: trigf (fired after ): there are 2 rows in ttest
1018 UPDATE 1
1019 vac=&gt; SELECT * FROM ttest;
1020 x
1021 ---
1022 1
1023 4
1024 (2 rows)
1025
1026 =&gt; DELETE FROM ttest;
1027 INFO: trigf (fired before): there are 2 rows in ttest
1028 INFO: trigf (fired before): there are 1 rows in ttest
1029 INFO: trigf (fired after ): there are 0 rows in ttest
1030 INFO: trigf (fired after ): there are 0 rows in ttest
1031 ^^^^^^
1032 remember what we said about visibility.
1033 DELETE 2
1034 =&gt; SELECT * FROM ttest;
1035 x
1036 ---
1037 (0 rows)
1038 </screen>
1039
1040 </para>
1041
1042 <para>
1043 There are more complex examples in
1044 <filename>src/test/regress/regress.c</filename> and
1045 in <xref linkend="contrib-spi"/>.
1046 </para>
1047 </sect1>
1048 </chapter>