PL/pgSQL can be used to define trigger
procedures. A trigger procedure is created with the
CREATE FUNCTION command 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.
- OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE
operations in ROW 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 is 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 and can be given as an expression. Invalid
indices (< 0 or >= 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.
Triggers fired BEFORE may return NULL to signal the trigger manager
to skip the rest of the operation for this row (ie, subsequent triggers
are not fired, and the INSERT/UPDATE/DELETE does not occur for this
row). If a non-NULL value is returned then the operation proceeds with
that row value. Note that returning a row value different from the
original value of NEW alters the row that will be inserted or updated.
It is possible to replace single values directly
in NEW and return that, or to build a complete new record/row to
return.
The return value of a trigger fired AFTER is ignored; it may as well
always return a NULL value. But an AFTER trigger can still abort the
operation by raising an error.
Example 19-1. A PL/pgSQL Trigger Procedure Example
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 ensures 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 ISNULL THEN
RAISE EXCEPTION ''empname cannot be NULL value'';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
END IF;
-- Who works for us when she must pay for?
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();
