Practical PostgreSQL
Prev		Next

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

Prev	Home	Next
CREATE TABLE AS	Up	CREATE TYPE