Databases - Practical PostgreSQL
Previous Page	Home	Next Page

CREATE TRIGGER

Name

CREATE TRIGGER -- Creates a new trigger.

Synopsis

CREATE TRIGGER 
name
 { BEFORE | AFTER } { 
event
 [ OR 
event
 ... ] }
       ON 
table
 FOR EACH { ROW | STATEMENT }
       EXECUTE PROCEDURE 
function
 ( 
arguments
 )

Parameters

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.

Results

CREATE: The message returned when a trigger is successfully created.

Description

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.

Note: A trigger may only be created on a table by the table's owner, or by a superuser.

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).

Examples

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).

Databases - Practical PostgreSQL
Previous Page	Home	Next Page