Name
CREATE TRIGGER
-- Creates a new trigger.
Synopsis
CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
ON table FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE func ( arguments )
Parameters
- name
This parameter takes the name of the new trigger.
- table
Use this parameter to specify the name of a table this trigger will be associated with.
- event
This parameter holds the event you wish to associate the trigger with (i.e., when
the trigger will fire). This can be either INSERT, DELETE, or UPDATE.
- func
This parameter takes the name of the function you wish to link to the new trigger.
This function must return a variable of type opaque; the opaque
type is used only by internal functions and cannot be defined by SQL.
Results
- CREATE
This message will be displayed if the trigger is successfully
created.
Description
Use the CREATE TRIGGER command to add triggers
into a database. Once you issue the command, the trigger will be added (assuming
you completed th command correctly). It will then be associated with the
table you specified during its creation and, when triggered, will
execute the function you specified.
 | Ownership |
|---|
| | The relation owner is the only user who may create a trigger on the relation.
|
When you create a trigger, you can specify if it is to be executed before or after
the operation is attempted (during the constraint check) or completed (on an
INSERT, UPDATE,
or DELETE on a tuple. If the trigger is set to
fire before one of those events, it may skip the operation
for the current tuple or change the tuple being inserted. If you have set the
trigger to fire after the event, it will be aware of all changes that were made
during the event (including the last insertion, update, or deletion).
Examples
This example defines a trigger that is invoked when a new author row is added or updated.
CREATE TRIGGER if_author_added
AFTER INSERT OR UPDATE ON author
FOR EACH ROW
EXECUTE PROCEDURE check_a_id();
The check_a_id() function is a PL/pgSQL function that was defined
to check the author identification number. If the identification number
is less than 100 it will display a message and abort the transaction. If
the identification number is left blank it will display a different
message and abort the transaction.