Triggers
PostgreSQL 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. Both per-row and
per-statement triggers are supported. A trigger procedure can
execute BEFORE or AFTER a INSERT,
DELETE or UPDATE, either once
per modified row, or once per SQL statement.
Trigger Definition
If a trigger event occurs, the trigger manager (called by the
Executor) sets up a TriggerData information
structure (described below) and calls the trigger function to
handle the event.
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 trigger.
(The trigger function receives its input through a TriggerData
structure, not in the form of ordinary function arguments.)
If the function is written in C, it must use the version 1
function manager interface.
The syntax for creating triggers is described in .
Trigger functions return a HeapTuple to the calling
executor. The return value is ignored for triggers fired AFTER an
operation, but it allows BEFORE triggers to:
Return a NULL pointer to skip the operation for the
current tuple (and so the tuple will not be
inserted/updated/deleted).
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.
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.
Note that there is no initialization performed by the
CREATE TRIGGER handler. This may be changed in
the future.
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 NULL, the operation is
abandoned and subsequent triggers are not fired.
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.
When a trigger is defined, a number of arguments can be
specified. 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.
Interaction with the Trigger Manager
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.
The interface described here applies for
PostgreSQL 7.1 and later.
Earlier versions passed the TriggerData pointer in a global
variable CurrentTriggerData.
When a function is called by the trigger manager, it is not passed
any normal parameters, but it is passed a context
pointer pointing to a TriggerData structure. C
functions can check whether they were called from the trigger
manager or not by executing the macro
CALLED_AS_TRIGGER(fcinfo), which expands to
((fcinfo)->context != NULL && IsA((fcinfo)->context, TriggerData))
If this returns true, then it is safe to cast
fcinfo->context to type TriggerData
* and make use of the pointed-to
TriggerData structure. The function must
not alter the TriggerData
structure or any of the data it points to.
struct TriggerData is defined in
commands/trigger.h:
typedef struct TriggerData
{
NodeTag type;
TriggerEvent tg_event;
Relation tg_relation;
HeapTuple tg_trigtuple;
HeapTuple tg_newtuple;
Trigger *tg_trigger;
} TriggerData;
where the members are defined as follows:
type
Always T_TriggerData if this is a trigger event.
tg_event
describes the event for which the function is called. You may use the
following macros to examine tg_event:
TRIGGER_FIRED_BEFORE(tg_event)
returns TRUE if trigger fired BEFORE.
TRIGGER_FIRED_AFTER(tg_event)
Returns TRUE if trigger fired AFTER.
TRIGGER_FIRED_FOR_ROW(event)
Returns TRUE if trigger fired for a ROW-level event.
TRIGGER_FIRED_FOR_STATEMENT(event)
Returns TRUE if trigger fired for STATEMENT-level event.
TRIGGER_FIRED_BY_INSERT(event)
Returns TRUE if trigger fired by INSERT.
TRIGGER_FIRED_BY_DELETE(event)
Returns TRUE if trigger fired by DELETE.
TRIGGER_FIRED_BY_UPDATE(event)
Returns TRUE if trigger fired by UPDATE.
tg_relation
is a pointer to structure describing the triggered
relation. Look at utils/rel.h for details about
this structure. The most interesting things are
tg_relation->rd_att (descriptor of the relation
tuples) and tg_relation->rd_rel->relname
(relation's name. This is not char*, but
NameData. Use
SPI_getrelname(tg_relation) to get char* if you
need a copy of the name).
tg_trigtuple
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 this trigger was fired for an
INSERT or DELETE then this
is what you should return to the Executor if you don't want to
replace the tuple with a different one (in the case of
INSERT) or skip the operation (in the case of
DELETE).
tg_newtuple
is a pointer to the new version of tuple if
UPDATE and 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.
tg_trigger
is pointer to structure Trigger defined in utils/rel.h:
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;
where tgname is the trigger's name,
tgnargs is number of arguments in
tgargs, tgargs is an array of
pointers to the arguments specified in the CREATE
TRIGGER statement. Other members are for internal use
only.
Visibility of Data Changes
PostgreSQL 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
INSERT INTO a SELECT * FROM a;
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.
But keep in mind this notice about visibility in the SPI documentation:
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.
This is true for triggers as well so, though a tuple being inserted
(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!
Examples
There are more complex examples in
src/test/regress/regress.c and
in contrib/spi.
Here is a very simple example of trigger usage. Function
trigf reports the number of tuples in the triggered
relation 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).
#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);
}
Now, compile and create the trigger function:
CREATE FUNCTION trigf () RETURNS TRIGGER AS
'...path_to_so' LANGUAGE C;
CREATE TABLE ttest (x int4);
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)