Chapter 16.
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. 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
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 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.

BEFORE
AFTER

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

INSERT
DELETE
UPDATE

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

relation

The relation name indicates 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. Currently only the ROW
case is supported.

procedure

The procedure name is the function to be called.

args

The arguments passed to the function in the 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).

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.

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.