Triggers

PostgreSQL has various server-side function interfaces. Server-side functions can be written in SQL, PL/pgSQL, 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.

The trigger function must be defined before the trigger is created as a function taking no arguments and returning opaque. Please refer to CREATE TRIGGER in the reference section of this guide for the syntax for registering a trigger with PostgreSQL.

If a trigger event occurs, the Trigger Manager calls the trigger function to handle the event.

Triggers with similar requirements can call the same function by passing different values for its arguments.

Also, the same function may be used for triggers on 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.

Note

If more than one trigger is defined for the same event on the same relation, the order of trigger firing is unpredictable.

Triggers fired before an INSERT, DELETE, or UPDATE operation can:

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 of these cases.

Refer to the language documentation in the Red Hat Database Programmer's Guide for details on how to write triggers using that language.