CREATE TRIGGER statement

Use the CREATE TRIGGER statement to define a trigger on a table. You can also use CREATE TRIGGER to define an INSTEAD OF trigger on a view.

This is an extension to the ANSI/ISO standard for SQL.

Syntax

(1)
Notes:
Element Description Restrictions Syntax
trigger Name that you declare here for a new trigger Must be unique among the names of triggers in the current database Identifier

Usage

A trigger is a database object that, unless disabled, automatically executes a specified set of SQL statements, called the trigger action, when a specified trigger event occurs.

The trigger event that initiates the trigger action can be an INSERT, DELETE, UPDATE, or a SELECT statement. The MERGE statement can also be the triggering event for an UPDATE, DELETE, or INSERT trigger. The event definition must specify the table or view on which the trigger is defined. (SELECT or UPDATE events for triggers on tables can also specify one or more columns.)

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if a trigger of the specified name is already defined on a table or view in the current database.

You can use the CREATE TRIGGER statement in two distinct ways:
  • You can define a trigger on a table in the current database.
  • You can also define an INSTEAD OF trigger on a view in the current database.
Any SQL statement that is an instance of the trigger event is called a triggering statement. When the event occurs, triggers defined on tables and triggers defined on views differ in whether the triggering statement is executed:
  • For tables, the trigger event and the trigger action both execute.
  • For views, only the trigger action executes, instead of the event.
That is, if you define an INSTEAD OF trigger on an updatable view, the database server does not perform the DML operation that corresponds to the trigger event, but instead performs the specified trigger action on the view or on its base table.
Important: In some cases, however, after the CREATE TRIGGER statement defines queries on a table or on a column as triggering events for enabled Select triggers, the database server does not execute the trigger action when the query executes. For a list of contexts where this is the expected behavior, see Circumstances When a Select Trigger Is Not Activated. For a similar list of contexts where the trigger actions of enabled Insert, Delete, or Update triggers on views are not executed, see Restrictions on INSTEAD OF Triggers on Views.

The CREATE TRIGGER statement can support the integrity of data in the database by defining rules by which specified DML operations (the triggering events) cause the database server to take specified actions. The following sections describe the syntax elements.

Clause Page Effect
Defining a Trigger Event and Actions Defining a Trigger Event and Action Associates triggered actions with an event
Trigger Modes Trigger Modes Enables or disables the trigger
Insert Events and Delete Events INSERT Events and DELETE Events Defines Insert events and Delete events
Update Events UPDATE Event Defines Update events
Select Events SELECT Event Defines Select events
Action Clause Action Clause Defines triggered actions
REFERENCING Clause for Delete REFERENCING Clause for Delete Declares qualifier for deleted values
REFERENCING Clause for Insert REFERENCING Clause for Insert triggers Declares qualifier for inserted values
REFERENCING Clause for Update REFERENCING Clause for Update Declares qualifiers for old and new values
REFERENCING Clause for Select REFERENCING Clause for Select Declares qualifier for result set values
Correlated Table Action Correlated Table Action Defines triggered actions
Triggered Action Triggered Action on a Table Defines triggered actions
INSTEAD OF Trigger on Views INSTEAD OF Triggers on Views Defines a trigger on views
Action Clause of INSTEAD OF Triggers The Action Clause of INSTEAD OF Triggers Triggered actions on views