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 can 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 triggers 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 modifies (in particular, an
operation that modifies zero rows will still result in the execution of
any applicable FOR EACH STATEMENT triggers).
In addition, triggers may be defined to fire for a TRUNCATE, though
only FOR EACH STATEMENT.
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 cannot 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 distinct 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, DELETE, or TRUNCATE; 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 executed. The arguments are
literal string constants. Simple names and numeric constants can
be written here, too, but they will all be converted to strings.
Please check the description of the implementation language of
the trigger function about how the trigger arguments are
accessible within the function; it might be different from
normal function arguments.
NOTES
To create a trigger on a table, the user must have the TRIGGER
privilege on the table.
Use DROP TRIGGER [drop_trigger(7)] to remove a trigger.
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.
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:
· SQL allows triggers to fire on updates to specific columns (e.g.,
AFTER UPDATE OF col1, col2).
· 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.
· 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 trigger using OR
is a PostgreSQL extension of the SQL standard.
The ability to fire triggers for TRUNCATE is a PostgreSQL extension of
the SQL standard.
SEE ALSO
CREATE FUNCTION [create_function(7)], ALTER TRIGGER [alter_trigger(7)],
DROP TRIGGER [drop_trigger(7)]