O'Reilly logo

SQL in a Nutshell 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 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 ...

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