Chapter 23

Triggers

IN THIS CHAPTER

Bullet Creating triggers

Bullet Considerations in firing a trigger

Bullet Executing a trigger

Bullet Firing multiple triggers

In the course of executing a database application, occasions may arise where if some specific action occurs, you want that action to cause another action, or perhaps a succession of actions, to occur. In a sense, that first action triggers the execution of the following actions. SQL provides the TRIGGER mechanism to provide this capability.

Triggers, of course, are best known as those parts of a firearm that cause it to fire. More generally, a trigger is an action or event that causes another event to occur. In SQL, the word trigger is used in this more general sense. A triggering SQL statement causes another SQL statement (the triggered statement) to be executed.

Examining Some Applications of Triggers

The firing of a trigger is useful in a number of situations. One example is to perform a logging function. Certain actions that are critical to the integrity of a database — such as inserting, editing, or deleting a table row — could trigger the making of an entry in a log that documents that action. Log entries can record not only what action was taken, but also when it was taken and by whom.

Triggers can also be used to keep a database consistent. In an order entry application, an order for a specific product can trigger a statement that changes the status of that product in the inventory table from available to reserved. Similarly, the deletion of a row in the orders table can trigger a statement that changes the status of the subject product from reserved to available.

Triggers offer even greater flexibility than is illustrated in the preceding examples. The triggered item doesn't have to be an SQL statement. It can be a host language procedure that performs some operation in the outside world, such as shutting down a production line or causing a robot to fetch a cold beer from the fridge.

Creating a Trigger

You create a trigger, logically enough, with a CREATE TRIGGER statement. After the trigger is created, it lies in wait — waiting for the triggering event to occur. When the triggering event occurs, bang! The trigger fires.

The syntax for the CREATE TRIGGER statement is fairly involved, but you can break it down into understandable pieces. First take a look at the overall picture:

CREATE TRIGGER trigger_name

trigger_action_time trigger_event

ON table_name

[REFERENCING old_or_new_value_alias_list]

triggered_action

The trigger name is the unique identifier for this trigger. The trigger action time is the time you want the triggered action to occur: either BEFORE or AFTER the triggering event. The fact that a triggered action can occur before the event that is supposedly causing it to happen may seem a little bizarre, but in some cases, this ability can be very useful (and can be accomplished without invoking time travel). Because the database engine knows that it is about to execute a triggering event before it actually executes it, it has the ability to sandwich in the triggered event ahead of the execution of the triggering event, if a trigger action time of BEFORE has been specified.

Three possible trigger events can cause a trigger to fire: the execution of an INSERT statement, a DELETE statement, or an UPDATE statement. These three statements have the power to change the contents of a database table. Thus, any insertion of one or more rows into the subject table, any deletion of one or more rows from the subject table, or any update of one or more columns in one or more rows in the subject table can cause a trigger to fire. ON table_name, of course, refers to the table for which an INSERT, DELETE, or UPDATE has been specified.

Statement and row triggers

The triggered_action in the preceding example has the following syntax:

[ FOR EACH { ROW | STATEMENT }]

WHEN <left paren><search condition><right paren>

<triggered SQL statement>

You can specify how the trigger will act:

  • Row trigger: The trigger will fire once upon encountering the INSERT, DELETE, or UPDATE statement that constitutes the triggering event.
  • Statement trigger: The trigger will fire multiple times, once for every row in the subject table that is affected by the triggering event.

As indicated by the square brackets, the FOR EACH clause is optional. Despite this, the trigger must act one way or the other. If no FOR EACH clause is specified, the default behavior is FOR EACH STATEMENT.

When a trigger fires

The search condition in the WHEN clause enables you to specify the circumstances under which a trigger will fire. Specify a predicate, and if the predicate is true, the trigger will fire; if it's false, it won’t. This capability greatly increases the usefulness of triggers. You can specify that a trigger fires only after a certain threshold value has been exceeded, or when any other condition can be determined to be either True or False.

The triggered SQL statement

The triggered SQL statement can be a single SQL statement or a sequence of SQL statements executed one after another. In the case of a single SQL statement, the triggered SQL statement is merely an ordinary SQL statement. For a sequence of SQL statements, however, you must guarantee atomicity to ensure that the operation is not aborted midstream, leaving the database in an unwanted state. You can do this with a BEGIN-END block that includes the ATOMIC keyword:

BEGIN ATOMIC

{ SQL statement 1 }

{ SQL statement 2 }

…

{ SQL statement n }

END

An example trigger definition

Suppose the corporate human resources manager wants to be informed whenever one of the regional managers hires a new employee. The following trigger can handle this situation nicely:

CREATE TRIGGER newhire

BEFORE INSERT ON employee

FOR EACH STATEMENT

BEGIN ATOMIC

CALL sendmail ('HRDirector')

INSERT INTO logtable

VALUES ('NEWHIRE', CURRENT_USER, CURRENT_TIMESTAMP);

END;

Whenever a new row is inserted into the NEWHIRE table, an email is fired off to the HR manager with the details, and the logon name of the person making the insertion and the time of the insertion are recorded in a log table, providing an audit trail.

Firing a Succession of Triggers

You can probably see a complication in the way triggers operate. Suppose you create a trigger that causes an SQL statement to be executed on a table upon the execution of some preceding SQL statement. What if that triggered statement itself causes a second trigger to fire? That second trigger causes a third SQL statement to be executed on a second table, which may itself cause yet another trigger to fire, affecting yet another table. How is it possible to keep everything straight? SQL handles this machine-gun-style trigger firing with something called trigger execution contexts.

A succession of INSERT, DELETE, and UPDATE operations can be performed by nesting the contexts in which they occur. When a trigger fires, an execution context is created. Only one execution context can be active at a time. Within that context, an SQL statement may be executed that fires a second trigger. At that point, the existing execution context is suspended in an operation analogous to pushing a value onto a stack. A new execution context, corresponding to the second trigger, is created, and its operation is performed. There is no arbitrary limit to the depth of nesting possible. When an operation is complete, its execution context is destroyed, and the next higher execution context is “popped off the stack” and reactivated. This process continues until all actions are complete and all execution contexts have been destroyed.

Referencing Old Values and New Values

The one part of the CREATE TRIGGER syntax that I have not talked about yet is the optional REFERENCING old_or_new_value_alias_list phrase. It enables you to create an alias or correlation name that references values in the trigger's subject table. After you create a correlation name for new values or an alias for new table contents, you can then reference the values that will exist after an INSERT or UPDATE operation. In a similar way, after you create a correlation name for old values or an alias for old table contents, you can then reference the values that existed in the subject table before an UPDATE or DELETE operation.

The old_or_new_values_alias_list in the CREATE TRIGGER syntax can be one or more of the following phrases:

OLD [ ROW ] [ AS ] <old values correlation name>

or

NEW [ ROW ] [ AS ] <new values correlation name>

or

OLD TABLE [ AS ] <old values table alias>

or

NEW TABLE [ AS ] <new values table alias>

The table aliases are identifiers for transition tables, which are not persistent, but which exist only to facilitate the referencing operation. As you would expect, NEW ROW and NEW TABLE cannot be specified for a DELETE trigger, and OLD ROW as well as OLD TABLE cannot be specified for an INSERT trigger. After you delete a row or table, there is no new value. Similarly, OLD ROW and OLD TABLE cannot be specified for an INSERT trigger. There are no old values to reference.

In a row-level trigger, you can use an old value correlation name to reference the values in the row being modified or deleted by the triggering SQL statement as that row existed before the statement modified or deleted it. Similarly, an old value table alias is what you use to access the values in the entire table as they existed before the triggering SQL statement's action took effect.

You may not specify either OLD TABLE or NEW TABLE with a BEFORE trigger. The transition tables created by the OLD TABLE or NEW TABLE keyword are too likely to be affected by the actions caused by the triggered SQL statement. To eliminate this potential problem, using OLD TABLE and NEW TABLE with a BEFORE trigger is prohibited.

Firing Multiple Triggers on a Single Table

One final topic that I want to cover in this chapter is the case in which multiple triggers are created, all causing an SQL statement to be executed that operates on the same table. All those triggers are primed and ready to fire. When the triggering event occurs, which one goes first? This conundrum is solved by an executive decision. Whichever trigger was created first is the first to fire. The trigger created second fires next, and so on down the line. Thus the potential ambiguity is avoided, and execution proceeds in an orderly fashion.