1 <Chapter Id="triggers">
2 <Title>Triggers</Title>
5 While the current version of <ProductName>Postgres</ProductName> has various client interfaces
6 such as Perl, Tcl, Python and C, it lacks an actual <FirstTerm>Procedural Language</FirstTerm>
7 (PL). We hope to have a proper PL one day. In the meantime it is possible
8 to call C functions as trigger actions. Note that STATEMENT-level trigger
9 events are not supported in the current version. You can currently specify
10 BEFORE or AFTER on INSERT, DELETE or UPDATE of a tuple as a trigger event.
14 <Title>Trigger Creation</Title>
17 If a trigger event occurs, the trigger manager (called by the Executor)
18 initializes the global structure TriggerData *CurrentTriggerData (described
19 below) and calls the trigger function to handle the event.
23 The trigger function must be created before the trigger is created as a
24 function taking no arguments and returns opaque.
28 The syntax for creating triggers is as follows:
31 CREATE TRIGGER <trigger name> <BEFORE|AFTER> <INSERT|DELETE|UPDATE>
32 ON <relation name> FOR EACH <ROW|STATEMENT>
33 EXECUTE PROCEDURE <procedure name> (<function args>);
38 The name of the trigger is used if you ever have to delete the trigger.
39 It is used as an argument to the DROP TRIGGER command.
43 The next word determines whether the function is called before or after
48 The next element of the command determines on what event(s) will trigger
49 the function. Multiple events can be specified separated by OR.
53 The relation name determines which table the event applies to.
57 The FOR EACH statement determines whether the trigger is fired for each
58 affected row or before (or after) the entire statement has completed.
62 The procedure name is the C function called.
66 The args are passed to the function in the CurrentTriggerData structure.
67 The purpose of passing arguments to the function is to allow different
68 triggers with similar requirements to call the same function.
72 Also, function may be used for triggering different relations (these
73 functions are named as "general trigger functions").
77 As example of using both features above, there could be a general
78 function that takes as its arguments two field names and puts the current
79 user in one and the current timestamp in the other. This allows triggers to
80 be written on INSERT events to automatically track creation of records in a
81 transaction table for example. It could also be used as a "last updated"
82 function if used in an UPDATE event.
86 Trigger functions return HeapTuple to the calling Executor. This
87 is ignored for triggers fired after an INSERT, DELETE or UPDATE operation
88 but it allows BEFORE triggers to:
90 - return NULL to skip the operation for the current tuple (and so the
91 tuple will not be inserted/updated/deleted);
92 - return a pointer to another tuple (INSERT and UPDATE only) which will
93 be inserted (as the new version of the updated tuple if UPDATE) instead
98 Note, that there is no initialization performed by the CREATE TRIGGER
99 handler. This will be changed in the future. Also, if more than one trigger
100 is defined for the same event on the same relation, the order of trigger
101 firing is unpredictable. This may be changed in the future.
105 If a trigger function executes SQL-queries (using SPI) then these queries
106 may fire triggers again. This is known as cascading triggers. There is no
107 explicit limitation on the number of cascade levels.
111 If a trigger is fired by INSERT and inserts a new tuple in the same
112 relation then this trigger will be fired again. Currently, there is nothing
113 provided for synchronization (etc) of these cases but this may change. At
114 the moment, there is function funny_dup17() in the regress tests which uses
115 some techniques to stop recursion (cascading) on itself...
121 <Title>Interaction with the Trigger Manager</Title>
124 As mentioned above, when function is called by the trigger manager,
125 structure TriggerData *CurrentTriggerData is NOT NULL and initialized. So
126 it is better to check CurrentTriggerData against being NULL at the start
127 and set it to NULL just after fetching the information to prevent calls to
128 a trigger function not from the trigger manager.
132 struct TriggerData is defined in src/include/commands/trigger.h:
135 typedef struct TriggerData
137 TriggerEvent tg_event;
138 Relation tg_relation;
139 HeapTuple tg_trigtuple;
140 HeapTuple tg_newtuple;
147 describes event for which the function is called. You may use the
148 following macros to examine tg_event:
150 TRIGGER_FIRED_BEFORE(event) returns TRUE if trigger fired BEFORE;
151 TRIGGER_FIRED_AFTER(event) returns TRUE if trigger fired AFTER;
152 TRIGGER_FIRED_FOR_ROW(event) returns TRUE if trigger fired for
154 TRIGGER_FIRED_FOR_STATEMENT(event) returns TRUE if trigger fired for
155 STATEMENT-level event;
156 TRIGGER_FIRED_BY_INSERT(event) returns TRUE if trigger fired by INSERT;
157 TRIGGER_FIRED_BY_DELETE(event) returns TRUE if trigger fired by DELETE;
158 TRIGGER_FIRED_BY_UPDATE(event) returns TRUE if trigger fired by UPDATE.
161 is pointer to structure describing the triggered relation. Look at
162 src/include/utils/rel.h for details about this structure. The most
163 interest things are tg_relation->rd_att (descriptor of the relation
164 tuples) and tg_relation->rd_rel->relname (relation's name. This is not
165 char*, but NameData. Use SPI_getrelname(tg_relation) to get char* if
166 you need a copy of name).
169 is a pointer to the tuple for which the trigger is fired. This is the tuple
170 being inserted (if INSERT), deleted (if DELETE) or updated (if UPDATE).
171 If INSERT/DELETE then this is what you are to return to Executor if
172 you don't want to replace tuple with another one (INSERT) or skip the
176 is a pointer to the new version of tuple if UPDATE and NULL if this is
177 for an INSERT or a DELETE. This is what you are to return to Executor if
178 UPDATE and you don't want to replace this tuple with another one or skip
182 is pointer to structure Trigger defined in src/include/utils/rel.h:
184 typedef struct Trigger
195 tgname is the trigger's name, tgnargs is number of arguments in tgargs,
196 tgargs is an array of pointers to the arguments specified in the CREATE
197 TRIGGER statement. Other members are for internal use only.
203 <Title>Visibility of Data Changes</Title>
206 <ProductName>Postgres</ProductName> data changes visibility rule: during a query execution, data
207 changes made by the query itself (via SQL-function, SPI-function, triggers)
208 are invisible to the query scan. For example, in query
211 INSERT INTO a SELECT * FROM a
214 tuples inserted are invisible for SELECT' scan. In effect, this
215 duplicates the database table within itself (subject to unique index
216 rules, of course) without recursing.
220 But keep in mind this notice about visibility in the SPI documentation:
223 Changes made by query Q are visible by queries which are started after
224 query Q, no matter whether they are started inside Q (during the
225 execution of Q) or after Q is done.
230 This is true for triggers as well so, though a tuple being inserted
231 (tg_trigtuple) is not visible to queries in a BEFORE trigger, this tuple
232 (just inserted) is visible to queries in an AFTER trigger, and to queries
233 in BEFORE/AFTER triggers fired after this!
238 <Title>Examples</Title>
241 There are more complex examples in in src/test/regress/regress.c and
246 Here is a very simple example of trigger usage. Function trigf reports
247 the number of tuples in the triggered relation ttest and skips the
248 operation if the query attempts to insert NULL into x (i.e - it acts as a
249 NOT NULL constraint but doesn't abort the transaction).
252 #include "executor/spi.h" /* this is what you need to work with SPI */
253 #include "commands/trigger.h" /* -"- and triggers */
255 HeapTuple trigf(void);
263 bool checknull = false;
267 if (!CurrentTriggerData)
268 elog(WARN, "trigf: triggers are not initialized");
270 /* tuple to return to Executor */
271 if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event))
272 rettuple = CurrentTriggerData->tg_newtuple;
274 rettuple = CurrentTriggerData->tg_trigtuple;
276 /* check for NULLs ? */
277 if (!TRIGGER_FIRED_BY_DELETE(CurrentTriggerData->tg_event) &&
278 TRIGGER_FIRED_BEFORE(CurrentTriggerData->tg_event))
281 if (TRIGGER_FIRED_BEFORE(CurrentTriggerData->tg_event))
286 tupdesc = CurrentTriggerData->tg_relation->rd_att;
287 CurrentTriggerData = NULL;
289 /* Connect to SPI manager */
290 if ((ret = SPI_connect()) < 0)
291 elog(WARN, "trigf (fired %s): SPI_connect returned %d", when, ret);
293 /* Get number of tuples in relation */
294 ret = SPI_exec("select count(*) from ttest", 0);
297 elog(WARN, "trigf (fired %s): SPI_exec returned %d", when, ret);
299 i = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull);
301 elog (NOTICE, "trigf (fired %s): there are %d tuples in ttest", when, i);
307 i = SPI_getbinval(rettuple, tupdesc, 1, &isnull);
319 create table ttest (x int4);
320 create function trigf () returns opaque as
321 '...path_to_so' language 'c';
324 vac=> create trigger tbefore before insert or update or delete on ttest
325 for each row execute procedure trigf();
327 vac=> create trigger tafter after insert or update or delete on ttest
328 for each row execute procedure trigf();
330 vac=> insert into ttest values (null);
331 NOTICE:trigf (fired before): there are 0 tuples in ttest
334 -- Insertion skipped and AFTER trigger is not fired
336 vac=> select * from ttest;
341 vac=> insert into ttest values (1);
342 NOTICE:trigf (fired before): there are 0 tuples in ttest
343 NOTICE:trigf (fired after ): there are 1 tuples in ttest
345 remember what we said about visibility.
347 vac=> select * from ttest;
353 vac=> insert into ttest select x * 2 from ttest;
354 NOTICE:trigf (fired before): there are 1 tuples in ttest
355 NOTICE:trigf (fired after ): there are 2 tuples in ttest
357 remember what we said about visibility.
359 vac=> select * from ttest;
366 vac=> update ttest set x = null where x = 2;
367 NOTICE:trigf (fired before): there are 2 tuples in ttest
369 vac=> update ttest set x = 4 where x = 2;
370 NOTICE:trigf (fired before): there are 2 tuples in ttest
371 NOTICE:trigf (fired after ): there are 2 tuples in ttest
373 vac=> select * from ttest;
380 vac=> delete from ttest;
381 NOTICE:trigf (fired before): there are 2 tuples in ttest
382 NOTICE:trigf (fired after ): there are 1 tuples in ttest
383 NOTICE:trigf (fired before): there are 1 tuples in ttest
384 NOTICE:trigf (fired after ): there are 0 tuples in ttest
386 remember what we said about visibility.
388 vac=> select * from ttest;