SQL in a Nutshell

Name

CREATE TRIGGER

Synopsis

A trigger is a special kind of stored procedure that fires automatically (hence, the term trigger) when a data-modification statement is executed. Triggers are associated with a specific data-modification statement (INSERT , UPDATE , or DELETE ) on a specific table.

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

SQL99 Syntax and Description

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

Triggers, by default, fire once at the statement level. That is, a single INSERT statement might insert 500 rows into a table, but an insert trigger on that table fires only one time. Some vendors allow a trigger to fire for each row of the data-modification operation. So, a statement that inserts 500 rows into a table that has a row-level insert trigger fires 500 times, once for each inserted row.

In addition to being associated with a specific data-modification statement (INSERT, UPDATE, or DELETE ) on a given table, triggers are associated with a specific time of firing. In general, triggers can fire BEFORE the data-modification statement is processed, AFTER it is processed, or (when supported by the vendor) INSTEAD OF processing ...