create_trigger(5)
NAME
CREATE TRIGGER - define a new trigger
SYNOPSIS
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )
DESCRIPTION
CREATE TRIGGER creates a new trigger. The trigger will be
associated with the specified table and will execute the
specified function funcname when certain events occur.
The trigger can be specified to fire either before the
operation is attempted on a row (before constraints are
checked and the INSERT, UPDATE, or DELETE is attempted) or
after the operation has completed (after constraints are
checked and the INSERT, UPDATE, or DELETE has completed).
If the trigger fires before the event, the trigger may
skip the operation for the current row, or change the row
being inserted (for INSERT and UPDATE operations only). If
the trigger fires after the event, all changes, including
the last insertion, update, or deletion, are ``visible''
to the trigger.
A trigger that is marked FOR EACH ROW is called once for
every row that the operation modifies. For example, a
DELETE that affects 10 rows will cause any ON DELETE trig-
gers on the target relation to be called 10 separate
times, once for each deleted row. In contrast, a trigger
that is marked FOR EACH STATEMENT only executes once for
any given operation, regardless of how many rows it modi-
fies (in particular, an operation that modifies zero rows
will still result in the execution of any applicable FOR
EACH STATEMENT triggers).
If multiple triggers of the same kind are defined for the
same event, they will be fired in alphabetical order by
name.
SELECT does not modify any rows so you can not create
SELECT triggers. Rules and views are more appropriate in
such cases.
Refer to in the documentation for more information about
triggers.
PARAMETERS
name The name to give the new trigger. This must be dis-
tinct from the name of any other trigger for the
same table.
BEFORE
AFTER Determines whether the function is called before or
after the event.
event One of INSERT, UPDATE, or DELETE; this specifies
the event that will fire the trigger. Multiple
events can be specified using OR.
table The name (optionally schema-qualified) of the table
the trigger is for.
FOR EACH ROW
FOR EACH STATEMENT
This specifies whether the trigger procedure should
be fired once for every row affected by the trigger
event, or just once per SQL statement. If neither
is specified, FOR EACH STATEMENT is the default.
funcname
A user-supplied function that is declared as taking
no arguments and returning type trigger, which is
executed when the trigger fires.
arguments
An optional comma-separated list of arguments to be
provided to the function when the trigger is exe-
cuted. The arguments are literal string constants.
Simple names and numeric constants may be written
here, too, but they will all be converted to
strings. Please check the description of the imple-
mentation language of the trigger function about
how the trigger arguments are accessible within the
function; it may be different from normal function
arguments.
NOTES
To create a trigger on a table, the user must have the
TRIGGER privilege on the table.
In PostgreSQL versions before 7.3, it was necessary to
declare trigger functions as returning the placeholder
type opaque, rather than trigger. To support loading of
old dump files, CREATE TRIGGER will accept a function
declared as returning opaque, but it will issue a notice
and change the function's declared return type to trigger.
Use DROP TRIGGER [drop_trigger(5)] to remove a trigger.
EXAMPLES
in the documentation contains a complete example.
COMPATIBILITY
The CREATE TRIGGER statement in PostgreSQL implements a
subset of the SQL standard. The following functionality is
currently missing:
o SQL allows triggers to fire on updates to specific
columns (e.g., AFTER UPDATE OF col1, col2).
o SQL allows you to define aliases for the ``old'' and
``new'' rows or tables for use in the definition of the
triggered action (e.g., CREATE TRIGGER ... ON tablename
REFERENCING OLD ROW AS somename NEW ROW AS othername
...). Since PostgreSQL allows trigger procedures to be
written in any number of user-defined languages, access
to the data is handled in a language-specific way.
o PostgreSQL only allows the execution of a user-defined
function for the triggered action. The standard allows
the execution of a number of other SQL commands, such as
CREATE TABLE as the triggered action. This limitation is
not hard to work around by creating a user-defined
function that executes the desired commands.
SQL specifies that multiple triggers should be fired in
time-of-creation order. PostgreSQL uses name order, which
was judged to be more convenient.
SQL specifies that BEFORE DELETE triggers on cascaded
deletes fire after the cascaded DELETE completes. The
PostgreSQL behavior is for BEFORE DELETE to always fire
before the delete action, even a cascading one. This is
considered more consistent. There is also unpredictable
behavior when BEFORE triggers modify rows that are later
to be modified by referential actions. This can lead to
constraint violations or stored data that does not honor
the referential constraint.
The ability to specify multiple actions for a single trig-
ger using OR is a PostgreSQL extension of the SQL stan-
dard.
SEE ALSO
CREATE FUNCTION [create_function(5)], ALTER TRIGGER
[alter_trigger(l)], DROP TRIGGER [drop_trigger(l)]
SQL - Language Statements 2008-01-03 CREATE TRIGGER()
Man(1) output converted with
man2html