PostgreSQL Triggers

References

• PostgreSQL Triggers
• PostgreSQL Trigger Functions

Notes

Overview

Trigger functions can be written in most of the available procedural languages, including PL/pgSQL, ..., and PL/Python.

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 defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. If a trigger event occurs, the trigger's function is called at the appropriate time 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.

Once a suitable trigger function has been created, the trigger is established with CREATE TRIGGER. The same trigger 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 per 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 and statement-level triggers, respectively.

Triggers are also classified as before triggers and after triggers. 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. 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.

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.
• For a row-level INSERT and DELETE 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

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 triggers, the possibly-modified row returned by each trigger becomes the input to the next trigger. If any before trigger returns NULL, the operation is abandoned for that row and subsequent triggers are not fired.

Typically, row before triggers are used for checking or modifying the data that will be inserted or updated. Row 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, whole a before trigger cannot; there might be other triggers firing after it.

If a trigger function executes SQL commands than these commands may fire triggers again. This is known as cascading triggers. There is no direct limitation on the number of cascade levels. 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.

The input data for the trigger function includes the type of trigger event 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 for for the UPDATE and DELETE triggers.

Visibility of Data Changes

If you execute SQL commands in your trigger function, and these commands access the table the trigger is for, then you need to be aware of the data visibility rules, because they determined whether these SQL commands will see the data change that the trigger is fired for:

• Statement-level triggers follow simple visibility rules: none of the changes made by a statement are visible to statement-level triggers that are invoked before the statement, whereas all modifications are visible to statement-level after triggers
• The data change (insertion, update, or deletion) causing the trigger to fire is naturally not visible to SQL commands executed in a row-level before trigger, because it hasn't happened yet.
• SQL commands executed in a row-level before trigger will see the effects of data changes for rows previously processed in the same outer command.
• When a row-level after trigger is fired, all data changes made by the outer command are already complete, and are visible to the invoked trigger function.

Trigger Functions

PL/pgSQL can be used to define trigger functions on data changes or database events. A trigger function is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger (for data change triggers) or event_trigger (for database event triggers). Special local variables named TG_something are automatically defined to describe the condition that triggered the call.

Triggers on Data Changes

A data change trigger is declared as a function with no arguments and a return type of trigger. Note that the function must be declared with no arguments even if it expected to receive some arguments specified in CREATE TRIGGER - such arguments are passed via TG_ARGV, as described below.

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:

• NEW record
• • New database row for INSERT/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.
• OLD record
• • Old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.
• TG_NAME name
• • name of the trigger which fired
• TG_WHEN text
• • BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition
• TG_LEVEL text
• • ROW or STATEMENT, depending on the trigger's definition
• TL_OP text
• • operation for which the trigger was fired: INSERT, UPDATE, DELETE, or TRUNCATE
• TL_RELID oid
• • Object ID of the table that caused the trigger invocation
• TL_RELNAME name
• • Table that caused the trigger invocation. This is now deprecated, and could disappear in a future release.
• TG_TABLE_NAME name
• • Table that caused the trigger invocation.
• TG_TABLE_SCHEMA name
• • Schema of the table that caused the trigger invocation/
• TG_NARGS integer
• • Number of arguments given to the trigger function in the CREATE TRIGGER statement.
• TG_ARGV text[]
• • Arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes result in a null value.

A trigger function must return either NULL or a record/row value having the structure of the table the trigger was fired for. Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row. If a nonnull value is returned then the operation proceeds with that row value. Returning a row value different from the original value of NEW alters the row that will be inserted or updated. Thus, if the trigger functions want the triggering action to success normally without altering the row value, NEW (or value equal thereto) has to be returned. To alter the row to be stored, it is possible to replace single values directly in NEW and return the modified NEW, or to build a complete new record/row to return.

INSTEAD OF triggers can return null to signal that they did not perform any updates, and that the rest of the operation for this row should be skipped (i.e., subsequent triggers are not fired, and the row is not counted in the rows-affected status for the surrounding INSERT/UPDATE/DELETE. Otherwise, a nonnull value should be returned, to signal that the trigger performed the requested operation. For INSERT and UPDATE operations, the return value should be NEW which the trigger function may modify to support INSERT RETURNING and UPDATE RETURNING. For DELETE operations, the return value should be OLD.

The return value of a row-level trigger fired AFTER or a statement level trigger fired BEFORE or AFTER is always ignored; it might as well be null.

Example

CREATE TABLE emp (
    empname           text,
    salary            integer,
    last_date         timestamp,
    last_user         text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when they must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE FUNCTION emp_stamp();

Triggers on Events

PL/pgSQL can be used to define event triggers. PostgreSQL requires that function that is to be called as an event trigger must be declared as a function with no arguments and a return type of event_trigger. When a PL/pgSQL function is called as an event trigger, several variables are created automatically in the top-level block. They are:

• TG_EVENT text
• • event the trigger is fired for
• TG_TAG text
• • command tag for which the trigger is fired

CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
    RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();