Chapter 20.
Triggers

PostgreSQL has various
server-side function interfaces. Server-side functions can be
written in SQL, PLPGSQL, TCL, or C. Trigger functions can be
written in any of these languages except 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.

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 is
created as a function taking no arguments and returning opaque.
If the function is written in C, it must use the "version 1" function manager interface.

The name of the trigger is used if you ever have to
delete the trigger. It is used as an argument to the
DROP TRIGGER command.

BEFORE
AFTER

Determines whether the function is called before or
after the event.

INSERT
DELETE
UPDATE

The next element of the command determines on what
event(s) will trigger the function. Multiple events can
be specified separated by OR.

relation

The relation name determines which table the event
applies to.

ROW
STATEMENT

The FOR EACH clause determines whether the trigger is
fired for each affected row or before (or after) the
entire statement has completed.

procedure

The procedure name is the function called.

args

The arguments passed to the function in the
TriggerData structure. The purpose of passing arguments
to the function is to allow different triggers with
similar requirements to call the same function.

Also, procedure may be
used for triggering different relations (these functions
are named as general trigger
functions).

As example of using both features above, there could
be a general function that takes as its arguments two
field names and puts the current user in one and the
current timestamp in the other. This allows triggers to
be written on INSERT events to automatically track
creation of records in a transaction table for example.
It could also be used as a "last
updated" function if used in an UPDATE event.

Trigger functions return HeapTuple to the calling Executor.
This is ignored for triggers fired after an INSERT, DELETE or
UPDATE operation but it allows BEFORE triggers to:

Return NULL to skip the operation for the current tuple
(and so the tuple will not be
inserted/updated/deleted).

Return a pointer to another tuple (INSERT and UPDATE
only) which will be inserted (as the new version of the
updated tuple if UPDATE) instead of original tuple.

Note that there is no initialization performed by the CREATE
TRIGGER handler. This will be changed in the future. Also, if
more than one trigger is defined for the same event on the same
relation, the order of trigger firing is unpredictable. This
may be changed in the future.

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 explicit limitation on the
number of cascade levels.

If a trigger is fired by INSERT and inserts a new tuple in
the same relation then this trigger will be fired again.
Currently, there is nothing provided for synchronization (etc)
of these cases but this may change. At the moment, there is
function funny_dup17() in the regress tests which uses some
techniques to stop recursion (cascading) on itself...