Informix Guide to SQL: Tutorial Creating and Using Triggers

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, it triggers the associated SQL statements, which are stored in the database. Figure 11-1 illustrates the relationship of the precipitating action, or trigger event, to the triggered action.

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

To create a trigger, use DB-Access, the Relational Object Manager, or one of the SQL APIs. This section describes the CREATE TRIGGER statement as you enter it with the interactive Query-language option in DB-Access or the Relational Object Manager. In an SQL API, you precede the statement with the symbol or keywords that identify it as an embedded statement.

The trigger name identifies the trigger. It follows the words CREATE TRIGGER in the statement. It can be up to 18 characters in length, 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

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, SELECT, DELETE, or UPDATE statement. When you define an UPDATE or SELECT 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 or SELECT 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 or SELECT 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

The triggered actions are the SQL statements that are performed when the trigger event occurs. The triggered actions can consist of INSERT, DELETE, UPDATE, and EXECUTE PROCEDURE 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.

To define a triggered action, specify when it occurs and then provide 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

To define a complete CREATE TRIGGER statement, combine the trigger-name clause, the trigger-event clause, and the triggered-action clause. 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.