Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 15: Creating and Using Triggers

Home Contents Index Master Index New Book

How to Create a Trigger

You use the CREATE TRIGGER statement to create a trigger. The CREATE TRIGGER statement is a data definition statement that associates SQL statements with a precipitating action on a table. When the precipitating action occurs, the associated SQL statements, which are stored in the database, are triggered. Figure 15-1 illustrates the relationship of the precipitating action, or trigger event, to the triggered action.


	Figure 15-1 Trigger Event and Triggered Action

The CREATE TRIGGER statement consists of clauses that perform the following actions:

Assign a trigger name.
Specify the trigger event, that is, the table and the type of action that initiate the trigger.
Define the SQL actions that are triggered.

An optional clause, called the REFERENCING clause, is discussed in "Using FOR EACH ROW Triggered Actions".

You can create a trigger using DB-Access, the SQL Editor, or one of the SQL APIs. This section describes the CREATE TRIGGER statement as you would enter it using the interactive Query-language option in DB-Access. In an SQL API, you simply precede the statement with the symbol or keywords that identify it as an embedded statement.

Assigning a Trigger Name

The trigger name identifies the trigger. It follows the words CREATE TRIGGER in the statement. It can be up to 18 characters long, beginning with a letter and consisting of letters, the digits 0 to 9, and the underscore. In the following example, the portion of the CREATE TRIGGER statement that is shown assigns the name upqty to the trigger:

CREATE TRIGGER upqty								-- assign trigger name

Specifying the Trigger Event

The trigger event is the type of statement that activates the trigger. When a statement of this type is performed on the table, the database server executes the SQL statements that make up the triggered action. The trigger event can be an INSERT, DELETE, or UPDATE statement. When you define an UPDATE trigger event, you can name one or more columns in the table to activate the trigger. If you do not name any columns, then an update of any column in the table activates the trigger. You can create only one INSERT and one DELETE trigger per table, but you can create multiple UPDATE triggers as long as the triggering columns are mutually exclusive.

In the following excerpt of a CREATE TRIGGER statement, the trigger event is defined as an update of the quantity column in the items table:

CREATE TRIGGER upqty						

UPDATE OF quantity ON items								-- an UPDATE trigger event

This portion of the statement identifies the table on which you create the trigger. If the trigger event is an insert or delete, only the type of statement and the table name are required, as the following example shows:

CREATE TRIGGER ins_qty

INSERT ON items								-- an INSERT trigger event

Defining the Triggered Actions

The triggered actions are the SQL statements that are performed when the trigger event occurs. The triggered actions can consist of INSERT, DELETE, UPDATE, EXECUTE PROCEDURE, or EXECUTE FUNCTION statements. In addition to specifying what actions are to be performed, however, you must also specify when they are to be performed in relation to the triggering statement. You have the following choices:

Before the triggering statement executes
After the triggering statement executes
For each row that is affected by the triggering statement

A single trigger can define actions for each of these times.

You define a triggered action by specifying when it occurs and then providing the SQL statement or statements to execute. You specify when the action is to occur with the keywords BEFORE, AFTER, or FOR EACH ROW. The triggered actions follow, enclosed in parentheses. The following triggered action definition specifies that the SPL routine upd_items_p1 is to be executed before the triggering statement:

BEFORE(EXECUTE PROCEDURE upd_items_p1)										-- a BEFORE action

A Complete CREATE TRIGGER Statement

If you combine the trigger-name clause, the trigger-event clause, and the triggered-action clause, you have a complete CREATE TRIGGER statement. The following CREATE TRIGGER statement is the result of combining the components of the statement from the preceding examples. This trigger executes the SPL routine upd_items_p1 whenever the quantity column of the items table is updated.

CREATE TRIGGER upqty

UPDATE OF quantity ON items

BEFORE(EXECUTE PROCEDURE upd_items_p1)

If a database object in the trigger definition, such as the SPL routine upd_items_p1 in this example, does not exist when the database server processes the CREATE TRIGGER statement, it returns an error.

Informix Guide to SQL: Tutorial, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.