While it is generally a good practice to keep related code together and avoid hidden actions as part of the main application code workflows, there are 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 modified. That is, 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, which is similar to how constraints make it impossible to insert invalid data.
The method used to add automated function calls to a table modifying event is called a trigger. Triggers are especially useful for the cases where there are multiple different client applications—possibly from different sources and using different programming styles—accessing the same data using multiple different functions or simple SQL.
From a standard's perspective, triggers are standardized in SQL3 (SQL1999). Triggers are generally defined using an event-condition-action (ECA) model. Events occur in the database, which trigger the invocation of a certain function if the conditions are satisfied. All data actions performed by the trigger execute within the same transaction in which the trigger is executing. Triggers cannot contain transaction control statements such as COMMIT and ROLLBACK. Triggers can be statement level or row level (more on this later in the chapter).
In PostgreSQL, a trigger is defined in two steps:
- Define a trigger function using
CREATE FUNCTION. - Bind this trigger function to a table using
CREATE TRIGGER.
In this chapter, we will cover the following topics:
- Creating a trigger and a trigger function
- Taking a look at some of the use cases of triggers, such as an audit trigger, and disallowing certain operations using triggers
- Discussing conditional triggers and triggers on specific field changes
- Describing the list of variables passed on to a trigger in brief, which can be used in the trigger function for conditional lookups
The trigger function's 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 in which the trigger is in before and after triggering the event. Additionally, there are several other local variables that start 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.
The simplified syntax to create a user-defined TRIGGER statement is given as follows:
CREATE TRIGGER name
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE function_name ( arguments )In the preceding code, the event is either INSERT, UPDATE, DELETE, or TRUNCATE. There are a few more options which we will come back to in a later section.
The arguments variable seemingly passed to the trigger function in the trigger definition are not used as arguments when calling the trigger. Instead, they are available to the trigger function as a text array (text[]) in the TG_ARGV variable (the length of this array is in TG_NARGS). Let's slowly start investigating how triggers and trigger functions work.
Starting from PostgreSQL 9.3, there is support for DDL triggers. We will learn more about DDL triggers, also called event triggers, in the next chapter.
First, we will use a simple trigger example and move on to more complex examples step by step.