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

Get SQL in a Nutshell, 3rd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.