Triggers
Chapter 16. Triggers
- Table of Contents
- 16.1. Trigger Definition
- 16.2. Interaction with the Trigger Manager
- 16.3. Visibility of Data Changes
- 16.4. Examples
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.
16.1. 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:
CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | DELETE | UPDATE [ OR ... ] ]
ON relation FOR EACH [ ROW | STATEMENT ]
EXECUTE PROCEDURE procedure
(args);where the arguments are:
- trigger
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.