Creates a new trigger.
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR event ... ] } ON table FOR EACH { ROW | STATEMENT } EXECUTE PROCEDURE function ( arguments )
name
The name of the new trigger.
table
The name of the table with which the trigger will be associated.
event
The event with which you wish to associate the trigger, that causes the trigger to fire. Valid events are: INSERT, DELETE, and UPDATE. A trigger may be associated with more than one event.
function
The name of the function you wish to link to the new trigger. When the trigger fires,
the function is invoked. The function must return a variable of type opaque
; the opaque
type is
used only by internal functions and such functions cannot be invoked directly from
SQL.
arguments
The arguments to pass to the function
when
the trigger is called.
Use the CREATE TRIGGER
command to add a trigger to a database. When a
trigger is added to the database, it is associated with the table
specified in the ON
clause. When the specified
event
“fires” the trigger, the function you
specified will be executed.
When you create a trigger, you must specify whether it is to be fired before or after the
event
is attempted (or completed). 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).
The following example defines a trigger that is invoked when an existing row in the
authors
table is updated:
booktown=# CREATE TRIGGER sync_authors_books booktown-# BEFORE UPDATE booktown-# ON authors booktown-# FOR EACH ROW booktown-# EXECUTE PROCEDURE sync_authors_and_books(); CREATE
The sync_authors_and_books( )
function is a PL/pgSQL function defined
to update the value of the author_id
column in the books
table if the id
value in the authors
table is updated.
It therefore keeps the books
table in sync with the authors
table. A similar effect could be achieved with a FOREIGN
KEY
constraint (see Chapter 7).
18.226.172.200