1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
|
<!--
$Header: /cvsroot/pgsql/doc/src/sgml/trigger.sgml,v 1.25 2002/09/21 18:32:54 petere Exp $
-->
<chapter id="triggers">
<title>Triggers</title>
<para>
<productname>PostgreSQL</productname> has various server-side
function interfaces. Server-side functions can be written in SQL,
C, or any defined procedural language. Trigger functions can be
written in C and most procedural languages, but not in SQL. Note that
statement-level trigger events are not supported in the current
version. You can currently specify BEFORE or AFTER on INSERT,
DELETE or UPDATE of a tuple as a trigger event.
</para>
<sect1 id="trigger-definition">
<title>Trigger Definition</title>
<para>
If a trigger event occurs, the trigger manager (called by the Executor)
sets up a <structname>TriggerData</> information structure (described below) and calls
the trigger function to handle the event.
</para>
<para>
The trigger function must be defined before the trigger itself can be
created. The trigger function must be declared as a
function taking no arguments and returning type <literal>trigger</>.
(The trigger function receives its input through a <structname>TriggerData</>
structure, not in the form of ordinary function arguments.)
If the function is written in C, it must use the <quote>version 1</>
function manager interface.
</para>
<para>
The syntax for creating triggers is:
<programlisting>
CREATE TRIGGER <replaceable>trigger</replaceable> [ BEFORE | AFTER ] [ INSERT | DELETE | UPDATE [ OR ... ] ]
ON <replaceable>relation</replaceable> FOR EACH [ ROW | STATEMENT ]
EXECUTE PROCEDURE <replaceable>procedure</replaceable>
(<replaceable>args</replaceable>);
</programlisting>
where the arguments are:
<variablelist>
<varlistentry>
<term>
<replaceable>trigger</replaceable>
</term>
<listitem>
<para>
The trigger must have a name distinct from all other triggers on
the same table. The name is needed
if you ever have to delete the trigger.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>BEFORE</term>
<term>AFTER</term>
<listitem>
<para>
Determines whether the function is called before or after
the event.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>INSERT</term>
<term>DELETE</term>
<term>UPDATE</term>
<listitem>
<para>
The next element of the command determines what event(s) will trigger
the function. Multiple events can be specified separated by OR.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable>relation</replaceable></term>
<listitem>
<para>
The relation name indicates which table the event applies to.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>ROW</term>
<term>STATEMENT</term>
<listitem>
<para>
The FOR EACH clause determines whether the trigger is fired for each
affected row or before (or after) the entire statement has completed.
Currently only the ROW case is supported.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable>procedure</replaceable></term>
<listitem>
<para>
The procedure name is the function to be called.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable>args</replaceable></term>
<listitem>
<para>
The arguments passed to the function in the <structname>TriggerData</> structure.
This is either empty or a list of one or more simple literal
constants (which will be passed to the function as strings).
</para>
<para>
The purpose of including arguments in the trigger definition
is to allow different
triggers with similar requirements to call the same function.
As an example, there could be a generalized trigger
function that takes as its arguments two field names and puts the
current user in one and the current time stamp in the other.
Properly written, this trigger function would be independent of
the specific table it is triggering on. So the same function
could be used for INSERT events on any table with suitable fields,
to automatically track creation of records in a transaction table for
example. It could also be used to track last-update events if
defined as an UPDATE trigger.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
<para>
Trigger functions return a <structname>HeapTuple</> to the calling executor. The return
value is ignored for triggers fired AFTER an operation,
but it allows BEFORE triggers to:
<itemizedlist>
<listitem>
<para>
Return a <symbol>NULL</> pointer to skip the operation for the
current tuple (and so the tuple will not be
inserted/updated/deleted).
</para>
</listitem>
<listitem>
<para>
For INSERT and UPDATE triggers only, the returned tuple becomes the
tuple which will be inserted or will replace the tuple being updated.
This allows the trigger function to modify the row being inserted or
updated.
</para>
</listitem>
</itemizedlist>
A BEFORE trigger that does not intend to cause either of these behaviors
must be careful to return the same NEW tuple it is passed.
</para>
<para>
Note that there is no initialization performed by the CREATE TRIGGER
handler. This may be changed in the future.
</para>
<para>
If more than one trigger is defined for the same event on the same
relation, the triggers will be fired in alphabetical order by
name. In the case of BEFORE triggers, the possibly-modified tuple
returned by each trigger becomes the input to the next trigger.
If any BEFORE trigger returns <symbol>NULL</>, the operation is
abandoned and subsequent triggers are not fired.
</para>
<para>
If a trigger function executes SQL-queries (using SPI) then these queries
may fire triggers again. This is known as cascading triggers. There is no
direct limitation on the number of cascade levels. It is possible for
cascades to cause recursive invocation of the same trigger --- for
example, an INSERT trigger might execute a query that inserts an
additional tuple into the same table, causing the INSERT trigger to be
fired again. It is the trigger programmer's
responsibility to avoid infinite recursion in such scenarios.
</para>
</sect1>
<sect1 id="trigger-manager">
<title>Interaction with the Trigger Manager</title>
<para>
This section describes the low-level details of the interface to a
trigger function. This information is only needed when writing a
trigger function in C. If you are using a higher-level function
language then these details are handled for you.
</para>
<note>
<para>
The interface described here applies for
<productname>PostgreSQL</productname> 7.1 and later.
Earlier versions passed the <structname>TriggerData</> pointer in a global
variable <varname>CurrentTriggerData</>.
</para>
</note>
<para>
When a function is called by the trigger manager, it is not passed any
normal parameters, but it is passed a <quote>context</> pointer pointing to a
<structname>TriggerData</> structure. C functions can check whether they were called
from the trigger manager or not by executing the macro
<literal>CALLED_AS_TRIGGER(fcinfo)</literal>, which expands to
<programlisting>
((fcinfo)->context != NULL && IsA((fcinfo)->context, TriggerData))
</programlisting>
If this returns true, then it is safe to cast <literal>fcinfo->context</> to type
<literal>TriggerData *</literal> and make use of the pointed-to
<structname>TriggerData</> structure.
The function must <emphasis>not</emphasis> alter the <structname>TriggerData</>
structure or any of the data it points to.
</para>
<para>
<structname>struct TriggerData</structname> is defined in
<filename>commands/trigger.h</filename>:
<programlisting>
typedef struct TriggerData
{
NodeTag type;
TriggerEvent tg_event;
Relation tg_relation;
HeapTuple tg_trigtuple;
HeapTuple tg_newtuple;
Trigger *tg_trigger;
} TriggerData;
</programlisting>
where the members are defined as follows:
<variablelist>
<varlistentry>
<term><structfield>type</></term>
<listitem>
<para>
Always <literal>T_TriggerData</literal> if this is a trigger event.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><structfield>tg_event</></term>
<listitem>
<para>
describes the event for which the function is called. You may use the
following macros to examine <literal>tg_event</literal>:
<variablelist>
<varlistentry>
<term>TRIGGER_FIRED_BEFORE(tg_event)</term>
<listitem>
<para>
returns TRUE if trigger fired BEFORE.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>TRIGGER_FIRED_AFTER(tg_event)</term>
<listitem>
<para>
Returns TRUE if trigger fired AFTER.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>TRIGGER_FIRED_FOR_ROW(event)</term>
<listitem>
<para>
Returns TRUE if trigger fired for
a ROW-level event.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>TRIGGER_FIRED_FOR_STATEMENT(event)</term>
<listitem>
<para>
Returns TRUE if trigger fired for
STATEMENT-level event.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>TRIGGER_FIRED_BY_INSERT(event)</term>
<listitem>
<para>
Returns TRUE if trigger fired by INSERT.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>TRIGGER_FIRED_BY_DELETE(event)</term>
<listitem>
<para>
Returns TRUE if trigger fired by DELETE.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>TRIGGER_FIRED_BY_UPDATE(event)</term>
<listitem>
<para>
Returns TRUE if trigger fired by UPDATE.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><structfield>tg_relation</></term>
<listitem>
<para>
is a pointer to structure describing the triggered
relation. Look at <filename>utils/rel.h</> for details about
this structure. The most interesting things are
<literal>tg_relation->rd_att</> (descriptor of the relation
tuples) and <literal>tg_relation->rd_rel->relname</>
(relation's name. This is not <type>char*</>, but
<type>NameData</>. Use
<literal>SPI_getrelname(tg_relation)</> to get <type>char*</> if you
need a copy of the name).
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><structfield>tg_trigtuple</></term>
<listitem>
<para>
is a pointer to the tuple for which the trigger is fired. This is the tuple
being inserted (if INSERT), deleted (if DELETE) or updated (if UPDATE).
If INSERT/DELETE then this is what you are to return to Executor if
you don't want to replace tuple with another one (INSERT) or skip the
operation.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><structfield>tg_newtuple</></term>
<listitem>
<para>
is a pointer to the new version of tuple if UPDATE and <symbol>NULL</> if this is
for an INSERT or a DELETE. This is what you are to return to Executor if
UPDATE and you don't want to replace this tuple with another one or skip
the operation.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><structfield>tg_trigger</></term>
<listitem>
<para>
is pointer to structure <structname>Trigger</> defined in <filename>utils/rel.h</>:
<programlisting>
typedef struct Trigger
{
Oid tgoid;
char *tgname;
Oid tgfoid;
int16 tgtype;
bool tgenabled;
bool tgisconstraint;
Oid tgconstrrelid;
bool tgdeferrable;
bool tginitdeferred;
int16 tgnargs;
int16 tgattr[FUNC_MAX_ARGS];
char **tgargs;
} Trigger;
</programlisting>
where <structfield>tgname</> is the trigger's name,
<structfield>tgnargs</> is number of arguments in
<structfield>tgargs</>, <structfield>tgargs</> is an array of
pointers to the arguments specified in the CREATE TRIGGER
statement. Other members are for internal use only.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</sect1>
<sect1 id="trigger-datachanges">
<title>Visibility of Data Changes</title>
<para>
<productname>PostgreSQL</productname> data changes visibility rule: during a query execution, data
changes made by the query itself (via SQL-function, SPI-function, triggers)
are invisible to the query scan. For example, in query
<programlisting>
INSERT INTO a SELECT * FROM a;
</programlisting>
tuples inserted are invisible for SELECT scan. In effect, this
duplicates the database table within itself (subject to unique index
rules, of course) without recursing.
</para>
<para>
But keep in mind this notice about visibility in the SPI documentation:
<blockquote>
<para>
Changes made by query Q are visible by queries that are started after
query Q, no matter whether they are started inside Q (during the
execution of Q) or after Q is done.
</para>
</blockquote>
</para>
<para>
This is true for triggers as well so, though a tuple being inserted
(<structfield>tg_trigtuple</>) is not visible to queries in a BEFORE trigger, this tuple
(just inserted) is visible to queries in an AFTER trigger, and to queries
in BEFORE/AFTER triggers fired after this!
</para>
</sect1>
<sect1 id="trigger-examples">
<title>Examples</title>
<para>
There are more complex examples in
<filename>src/test/regress/regress.c</filename> and
in <filename>contrib/spi</filename>.
</para>
<para>
Here is a very simple example of trigger usage. Function <function>trigf</> reports
the number of tuples in the triggered relation <literal>ttest</> and skips the
operation if the query attempts to insert a null value into x (i.e - it acts as a
not-null constraint but doesn't abort the transaction).
<programlisting>
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* -"- and triggers */
extern Datum trigf(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(trigf);
Datum
trigf(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
TupleDesc tupdesc;
HeapTuple rettuple;
char *when;
bool checknull = false;
bool isnull;
int ret, i;
/* Make sure trigdata is pointing at what I expect */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "trigf: not fired by trigger manager");
/* tuple to return to Executor */
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
rettuple = trigdata->tg_newtuple;
else
rettuple = trigdata->tg_trigtuple;
/* check for null values */
if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
&& TRIGGER_FIRED_BEFORE(trigdata->tg_event))
checknull = true;
if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
when = "before";
else
when = "after ";
tupdesc = trigdata->tg_relation->rd_att;
/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
elog(INFO, "trigf (fired %s): SPI_connect returned %d", when, ret);
/* Get number of tuples in relation */
ret = SPI_exec("SELECT count(*) FROM ttest", 0);
if (ret < 0)
elog(NOTICE, "trigf (fired %s): SPI_exec returned %d", when, ret);
/* count(*) returns int8 as of PG 7.2, so be careful to convert */
i = (int) DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
SPI_tuptable->tupdesc,
1,
&isnull));
elog (NOTICE, "trigf (fired %s): there are %d tuples in ttest", when, i);
SPI_finish();
if (checknull)
{
(void) SPI_getbinval(rettuple, tupdesc, 1, &isnull);
if (isnull)
rettuple = NULL;
}
return PointerGetDatum(rettuple);
}
</programlisting>
</para>
<para>
Now, compile and create the trigger function:
<programlisting>
CREATE FUNCTION trigf () RETURNS TRIGGER AS
'...path_to_so' LANGUAGE C;
CREATE TABLE ttest (x int4);
</programlisting>
<programlisting>
vac=> CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest
FOR EACH ROW EXECUTE PROCEDURE trigf();
CREATE
vac=> CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest
FOR EACH ROW EXECUTE PROCEDURE trigf();
CREATE
vac=> INSERT INTO ttest VALUES (NULL);
WARNING: trigf (fired before): there are 0 tuples in ttest
INSERT 0 0
-- Insertion skipped and AFTER trigger is not fired
vac=> SELECT * FROM ttest;
x
---
(0 rows)
vac=> INSERT INTO ttest VALUES (1);
INFO: trigf (fired before): there are 0 tuples in ttest
INFO: trigf (fired after ): there are 1 tuples in ttest
^^^^^^^^
remember what we said about visibility.
INSERT 167793 1
vac=> SELECT * FROM ttest;
x
---
1
(1 row)
vac=> INSERT INTO ttest SELECT x * 2 FROM ttest;
INFO: trigf (fired before): there are 1 tuples in ttest
INFO: trigf (fired after ): there are 2 tuples in ttest
^^^^^^^^
remember what we said about visibility.
INSERT 167794 1
vac=> SELECT * FROM ttest;
x
---
1
2
(2 rows)
vac=> UPDATE ttest SET x = NULL WHERE x = 2;
INFO: trigf (fired before): there are 2 tuples in ttest
UPDATE 0
vac=> UPDATE ttest SET x = 4 WHERE x = 2;
INFO: trigf (fired before): there are 2 tuples in ttest
INFO: trigf (fired after ): there are 2 tuples in ttest
UPDATE 1
vac=> SELECT * FROM ttest;
x
---
1
4
(2 rows)
vac=> DELETE FROM ttest;
INFO: trigf (fired before): there are 2 tuples in ttest
INFO: trigf (fired after ): there are 1 tuples in ttest
INFO: trigf (fired before): there are 1 tuples in ttest
INFO: trigf (fired after ): there are 0 tuples in ttest
^^^^^^^^
remember what we said about visibility.
DELETE 2
vac=> SELECT * FROM ttest;
x
---
(0 rows)
</programlisting>
</para>
</sect1>
</chapter>
<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->
|