SQL in a Nutshell, 2nd Edition

Name

CREATE/ALTER TRIGGER Statement

Synopsis

A trigger is a special kind of stored procedure that fires automatically (hence the term “trigger”) when a specific data-modification statement is executed against a table. The trigger is directly associated with the table and is considered a dependent object. For example, you might want all of the part_numbers in the sales table to be updated when a part_number is changed in the products table, thus ensuring that part numbers are always in sync.

Tip

ALTER TRIGGER is not an ANSI-supported statement.

Platform	Command
DB2	Supported, with variations
MySQL	Not supported
Oracle	Supported, with variations
PostgreSQL	Supported, with variations
SQL Server	Supported, with variations

SQL2003 Syntax

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {DELETE | INSERT | UPDATE [OF column [,...]}
ON table_name
[REFERENCING {OLD {[ROW] | TABLE} [AS] old_name | NEW 
{ROW | TABLE} [AS] new_name}] [FOR EACH { ROW | STATEMENT }]
[WHEN (conditions)]
[BEGIN ATOMIC]
code_block
[END]

Keywords

CREATE TRIGGER trigger_name

Creates a trigger of trigger_name and associates it with a specific table.

BEFORE | AFTER

Declares that the trigger logic is fired either BEFORE or AFTER the data manipulation operation that invoked the trigger. BEFORE triggers perform their operations before the INSERT, UPDATE, or DELETE operation occurs, allowing you to do dramatic things like circumvent the data manipulation operation altogether. AFTER triggers fire after the data manipulation ...