PostgreSQL 8.0.0rc4 Documentation
Prev | Fast Backward | Chapter 35. PL/pgSQL - SQL Procedural Language | Fast Forward | Next

# 35.10. Trigger Procedures

PL/pgSQL can be used to define trigger procedures. A trigger procedure is created with the `CREATE FUNCTION` command, declaring it 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 expects to receive arguments specified in `CREATE TRIGGER` — trigger 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`

  Data type `RECORD`; variable holding the new database row for `INSERT`/`UPDATE` operations in row-level triggers. This variable is `NULL` in statement-level triggers.

`OLD`

  Data type `RECORD`; variable holding the old database row for `UPDATE`/`DELETE` operations in row-level triggers. This variable is `NULL` in statement-level triggers.

`TG_NAME`

  Data type `name`; variable that contains the name of the trigger actually fired.

`TG_WHEN`

  Data type `text`; a string of either `BEFORE` or `AFTER` depending on the trigger's definition.

`TG_LEVEL`

  Data type `text`; a string of either `ROW` or `STATEMENT` depending on the trigger's definition.

`TG_OP`

  Data type `text`; a string of `INSERT`, `UPDATE`, or `DELETE` telling for which operation the trigger was fired.

`TG_RELID`

  Data type `oid`; the object ID of the table that caused the trigger invocation.

`TG_RELNAME`

  Data type `name`; the name of the table that caused the trigger invocation.

`TG_NARGS`

  Data type `integer`; the number of arguments given to the trigger procedure in the `CREATE TRIGGER` statement.

`TG_ARGV[]`

  Data type array of `text`; the arguments from the `CREATE TRIGGER` statement. The index counts from 0. Invalid indices (less than 0 or greater than or equal to `tg_nargs`) result in a null value.

A trigger function must return either `NULL` or a record/row value having exactly the structure of the table the trigger was fired for.

Row-level triggers fired `BEFORE` may return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the `INSERT`/`UPDATE`/`DELETE` does not occur 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 (but has no direct effect in the `DELETE` case). 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.

The return value of a `BEFORE` or `AFTER` statement-level trigger or an `AFTER` row-level trigger is always ignored; it may as well be null. However, any of these types of triggers can still abort the entire operation by raising an error.

Example 35-1 shows an example of a trigger procedure in PL/pgSQL.

Example 35-1. A PL/pgSQL Trigger Procedure

This example trigger ensures that any time a row is inserted or updated in the table, the current user name and time are stamped into the row. And it checks that an employee's name is given and that the salary is a positive value.

    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 she 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 := 'now';
            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 PROCEDURE emp_stamp();

Another way to log changes to a table involves creating a new table that holds a row for each insert, update, or delete that occurs. This approach can be thought of as auditing changes to a table. Example 35-2 shows an example of an audit trigger procedure in PL/pgSQL.

Example 35-2. A PL/pgSQL Trigger Procedure For Auditing

This example trigger ensures that any insert, update or delete of a row in the `emp` table is recorded (i.e., audited) in the `emp_audit` table. The current time and user name are stamped into the row, together with the type of operation performed on it.

    CREATE TABLE emp (
        empname           text NOT NULL,
        salary            integer
    );

    CREATE TABLE emp_audit(
        operation         char(1)   NOT NULL,
        stamp             timestamp NOT NULL,
        userid            text      NOT NULL,
        empname           text      NOT NULL,
        salary integer
    );

    CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
        BEGIN
            --
            -- Create a row in emp_audit to reflect the operation performed on emp,
            -- make use of the special variable TG_OP to work out the operation.
            --
            IF (TG_OP = 'DELETE') THEN
                INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
                RETURN OLD;
            ELSIF (TG_OP = 'UPDATE') THEN
                INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
                RETURN NEW;
            ELSIF (TG_OP = 'INSERT') THEN
                INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
                RETURN NEW;
            END IF;
            RETURN NULL; -- result is ignored since this is an AFTER trigger
        END;
    $emp_audit$ language plpgsql;

    CREATE TRIGGER emp_audit
    AFTER INSERT OR UPDATE OR DELETE ON emp
        FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

Prev | Home | Next
Errors and Messages | Up | Porting from Oracle PL/SQL