Database Reference
In-Depth Information
Controlling when a trigger is called
While it is relatively easy to perform trigger actions conditionally inside the PL/pgSQL
trigger function, it is often more efficient to skip invoking the trigger altogether. The
performance effects of firing a trigger is not generally noticed when only a few events
are fired. However, if you are bulk loading data or updating large portions of your
table, the cumulative effects can certainly be felt. To avoid the overhead, its best to
only call the trigger function when it is actually needed.
There are two ways to narrow down when a trigger is called in the
CREATE TRIGGER
command itself.
So once more use the same syntax, but this time with all options:
CREATE TRIGGER name
{ BEFORE | AFTER | INSTEAD OF } { event [ OR
event ... ] }
[ OF column_name [ OR column_name ... ] ]
ON table_name
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )
Conditional trigger
A flexible way of controlling triggers is a generic
WHEN
clause that is similar to
WHERE
in SQL queries. With a
WHEN
clause, you can write any expression, except a sub-
query, that is tested before the trigger function is called. The expression must result in
a Boolean value, and if the value is
FALSE
(or
NULL
which is automatically converted
to
FALSE
), the trigger function is not called.
For example, you could use this to enforce a "No updates on Friday afternoon" policy.
CREATE OR REPLACE FUNCTION cancel_with_message()
RETURNS TRIGGER AS $$
BEGIN
