Database Reference
In-Depth Information
Summary
A trigger is a binding of a set of actions to certain operations performed on a table or
view. This set of actions is defined in a special trigger function distinguished by spe-
cifying the type of returned value to be of special pseudotype trigger. So each time an
operation (
INSERT
,
UPDATE
,
DELETE
, or
TRUNCATE
) is performed on the table, this
trigger function is called by the system.
It can be executed either
FOR EACH ROW
or
FOR EACH STATEMENT
. If executed
for each row (row level trigger), the function is passed special variables
OLD
and
NEW
.
This will contain the row content, as it is currently in the database (
OLD
), and as it is at
the moment the trigger function is called (
NEW
). Where the
OLD
or
NEW
value is miss-
ing, it is passed as
undefined
. If executed once per statement (the statement-level
trigger), both
OLD
and
NEW
are unassigned for all operations.
The trigger function for row-level triggers on
INSERT
,
UPDATE
, and
DELETE
can be
set to execute either
BEFORE
or
AFTER
the operation on a table and the
INSTEAD OF
operation on view.
The trigger function for statement level triggers on
INSERT
,
UPDATE
, and
DELETE
can
be set to execute either
BEFORE
or
AFTER
the operation on both tables and views.
While
TRUNCATE
is logically a special form of "delete all" statement, no
ON DELETE
triggers will fire in case of
TRUNCATE
. Instead, you can use a special
ON TRUNCATE
trigger on the same table. Only statement-level on truncate triggers are possible.
While you can't skip statement triggers by returning a
NULL
, you can
RAISE
EXCEPTION
and abort the transaction.
It is also not possible to define any
ON TRUNCATE
triggers on views.
