| PostgreSQL 7.4.8 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 37. PL/pgSQL - SQL Procedural Language | Fast Forward | Next |
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:
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.
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.
Data type name; variable that contains the name of the trigger actually fired.
Data type text; a string of either BEFORE or AFTER depending on the trigger's definition.
Data type text; a string of either ROW or STATEMENT depending on the trigger's definition.
Data type text; a string of INSERT, UPDATE, or DELETE telling for which operation the trigger was fired.
Data type oid; the object ID of the table that caused the trigger invocation.
Data type name; the name of the table that caused the trigger invocation.
Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement.
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 37-1 shows an example of a trigger procedure in PL/pgSQL.
Example 37-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 '
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;
' LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();