This chapter provides general information about writing trigger functions. Trigger functions can be written in most of the available procedural languages, including PL/pgSQL (Chapter 40, PL/pgSQL - SQL Procedural Language), PL/Tcl (Chapter 41, PL/Tcl - Tcl Procedural Language), PL/Perl (Chapter 42, PL/Perl - Perl Procedural Language), and PL/Python (Chapter 43, PL/Python - Python Procedural Language). After reading this chapter, you should consult the chapter for your favorite procedural language to find out the language-specific details of writing a trigger in it.
It is also possible to write a trigger function in C, although most people find it easier to use one of the procedural languages. It is not currently possible to write a trigger function in the plain SQL function language.
A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. Triggers can be attached to tables, views, and foreign tables.
On tables and foreign tables, triggers can be defined to execute either
before or after any INSERT, UPDATE,
or DELETE operation, either once per modified row,
or once per SQL statement.
UPDATE triggers can moreover be set to fire only if
certain columns are mentioned in the SET
clause of the
UPDATE statement.
Triggers can also fire for TRUNCATE statements.
If a trigger event occurs, the trigger's function is called at the
appropriate time to handle the event. Foreign tables do not support the
TRUNCATE statement at all.
On views, triggers can be defined to execute instead of
INSERT, UPDATE, or
DELETE operations. INSTEAD OF
triggers
are fired once for each row that needs to be modified in the view.
It is the responsibility of the
trigger's function to perform the necessary modifications to the
underlying base tables and, where appropriate, return the modified
row as it will appear in the view. Triggers on views can also be defined
to execute once per SQL statement, before or after
INSERT, UPDATE, or
DELETE operations.
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 specially-passed
TriggerData structure, not in the form of ordinary function
arguments.)
Once a suitable trigger function has been created, the trigger is established with CREATE TRIGGER(7). The same trigger function can be used for multiple triggers.
PostgreSQL™ offers both per-row triggers and per-statement triggers. With a per-row trigger, the trigger function is invoked once for each row that is affected by the statement that fired the trigger. In contrast, a per-statement trigger is invoked only once when an appropriate statement is executed, regardless of the number of rows affected by that statement. In particular, a statement that affects zero rows will still result in the execution of any applicable per-statement triggers. These two types of triggers are sometimes called row-level triggers and statement-level triggers, respectively. Triggers on TRUNCATE may only be defined at statement level. On views, triggers that fire before or after may only be defined at statement level, while triggers that fire instead of an INSERT, UPDATE, or DELETE may only be defined at row level.
Triggers are also classified according to whether they fire
before, after, or
instead of the operation. These are referred to
as BEFORE
triggers, AFTER
triggers, and
INSTEAD OF
triggers respectively.
Statement-level BEFORE
triggers naturally fire before the
statement starts to do anything, while statement-level AFTER
triggers fire at the very end of the statement. These types of
triggers may be defined on tables or views. Row-level BEFORE
triggers fire immediately before a particular row is operated on,
while row-level AFTER
triggers fire at the end of the
statement (but before any statement-level AFTER
triggers).
These types of triggers may only be defined on tables and foreign tables.
Row-level INSTEAD OF
triggers may only be defined on views,
and fire immediately as each row in the view is identified as needing to
be operated on.
Trigger functions invoked by per-statement triggers should always return NULL. Trigger functions invoked by per-row triggers can return a table row (a value of type HeapTuple) to the calling executor, if they choose. A row-level trigger fired before an operation has the following choices:
It can return NULL to skip the operation for the current row. This instructs the executor to not perform the row-level operation that invoked the trigger (the insertion, modification, or deletion of a particular table row).
For row-level INSERT and UPDATE triggers only, the returned row becomes the row that will be inserted or will replace the row being updated. This allows the trigger function to modify the row being inserted or updated.
A row-level BEFORE
trigger that does not intend to cause
either of these behaviors must be careful to return as its result the same
row that was passed in (that is, the NEW
row
for INSERT and UPDATE
triggers, the OLD
row for
DELETE triggers).
A row-level INSTEAD OF
trigger should either return
NULL to indicate that it did not modify any data from
the view's underlying base tables, or it should return the view
row that was passed in (the NEW
row
for INSERT and UPDATE
operations, or the OLD
row for
DELETE operations). A nonnull return value is
used to signal that the trigger performed the necessary data
modifications in the view. This will cause the count of the number
of rows affected by the command to be incremented. For
INSERT and UPDATE operations, the trigger
may modify the NEW
row before returning it. This will
change the data returned by
INSERT RETURNING or UPDATE RETURNING,
and is useful when the view will not show exactly the same data
that was provided.
The return value is ignored for row-level triggers fired after an operation, and so they can return NULL.
If more than one trigger is defined for the same event on the same
relation, the triggers will be fired in alphabetical order by
trigger name. In the case of BEFORE
and
INSTEAD OF
triggers, the possibly-modified row returned by
each trigger becomes the input to the next trigger. If any
BEFORE
or INSTEAD OF
trigger returns
NULL, the operation is abandoned for that row and subsequent
triggers are not fired (for that row).
A trigger definition can also specify a Boolean WHEN
condition, which will be tested to see whether the trigger should
be fired. In row-level triggers the WHEN
condition can
examine the old and/or new values of columns of the row. (Statement-level
triggers can also have WHEN
conditions, although the feature
is not so useful for them.) In a BEFORE
trigger, the
WHEN
condition is evaluated just before the function is or would be executed,
so using WHEN
is not materially different from testing the
same condition at the beginning of the trigger function. However, in
an AFTER
trigger, the WHEN
condition is evaluated
just after the row update occurs, and it determines whether an event is
queued to fire the trigger at the end of statement. So when an
AFTER
trigger's
WHEN
condition does not return true, it is not necessary
to queue an event nor to re-fetch the row at end of statement. This
can result in significant speedups in statements that modify many
rows, if the trigger only needs to be fired for a few of the rows.
INSTEAD OF
triggers do not support
WHEN
conditions.
Typically, row-level BEFORE
triggers are used for checking or
modifying the data that will be inserted or updated. For example,
a BEFORE
trigger might be used to insert the current time into a
timestamp column, or to check that two elements of the row are
consistent. Row-level AFTER
triggers are most sensibly
used to propagate the updates to other tables, or make consistency
checks against other tables. The reason for this division of labor is
that an AFTER
trigger can be certain it is seeing the final
value of the row, while a BEFORE
trigger cannot; there might
be other BEFORE
triggers firing after it. If you have no
specific reason to make a trigger BEFORE
or
AFTER
, the BEFORE
case is more efficient, since
the information about
the operation doesn't have to be saved until end of statement.
If a trigger function executes SQL commands then these commands might 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 a recursive invocation of the same trigger; for example, an INSERT trigger might execute a command that inserts an additional row 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 being defined, arguments can be specified for it. 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 column 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 columns, 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.
Each programming language that supports triggers has its own method
for making the trigger input data available to the trigger function.
This input data includes the type of trigger event (e.g.,
INSERT or UPDATE) as well as any
arguments that were listed in CREATE TRIGGER.
For a row-level trigger, the input data also includes the
NEW
row for INSERT and
UPDATE triggers, and/or the OLD
row
for UPDATE and DELETE triggers.
Statement-level triggers do not currently have any way to examine the
individual row(s) modified by the statement.