Database Reference
In-Depth Information
Chapter 5. PL/pgSQL Trigger Functions
While it is generally a good practice to keep related code together and avoid "hidden"
actions as part of main application code flows, there are also valid cases where it is
a good practice to add some kind of general or cross-application functionality to the
database using automated actions which happen each and every time a table is mod-
ified. That is, the actions are part of your data model and not your application code
and you want to be sure that it is not possible to forget or bypass them in a similar
way that constraints make it impossible to insert invalid data.
The tool for adding automated function calls to a table modifying event is called a
trigger. Triggers are especially useful for cases where there are multiple different cli-
ent applications—possibly from different sources and using different programming
styles—accessing the same data using multiple different functions or straight SQL.
In PostgreSQL a trigger is defined in two steps:
1. Define a trigger function using
CREATE FUNCTION
2. Bind this trigger function to a table using
CREATE TRIGGER
Creating the trigger function
The trigger function definition looks mostly like an ordinary function definition, except
that it has a return value type
trigger
, and it does not take any arguments:
CREATE FUNCTION mytriggerfunc() RETURNS trigger
AS $$ …
Trigger functions are passed information about their calling environment through a
special
TriggerData
structure, which in the case of PL/pgSQL is accessible through
a set of local variables. The local variables,
OLD
and
NEW
, represent the row the
trigger is in the before and after states of the triggering event. Additionally, there
are several other local variables starting with the prefix
TG_
such as
TG_WHEN
or
TG_TABLE_NAME
for general context. Once your trigger function is defined, you can
bind it to a specific set of actions on a table.
