Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

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

 
 
  Published under the terms of the Open Publication License Design by Interspire