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

Notes:

¹ See Owner name
² See Defining a Trigger Event and Action
³ See INSTEAD OF Triggers on Views

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