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 OPAQUE. Note that the function must be declared with
no arguments even if it expects to receive arguments specified in
CREATE TRIGGER --- trigger arguments are
TG_ARGV, as described
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.
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in ROW 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 is 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 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 23-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 OPAQUE 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();
Be careful with BEFORE DELETE triggers!
A BEFORE DELETE trigger receives a null NEW.
If you just automatically RETURN NEW from the trigger, you'll essentially tell the trigger manager that it shouldn't execute the delete.
Thus, if you return NEW from a BEFORE DELETE trigger, you will prevent all deletes from a table. (An interesting trick if it's what you intend...)