SQL in a Nutshell, 3rd Edition

Name

CREATE/ALTER TRIGGER Statement

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. You can accomplish this with a trigger.

Tip

ALTER TRIGGER is not an ANSI-supported statement.

Platform	Command
MySQL	Supported, with variations
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 named 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 operation ...