O'Reilly logo

SQL in a Nutshell, 3rd Edition by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required