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.

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();