Informix Guide to SQL: Tutorial Creating and Using Triggers

To use triggers effectively, you need to understand the relationship between the triggering statement and the resulting triggered actions. You define this relationship when you specify the time that the triggered action occurs; that is, BEFORE, AFTER, or FOR EACH ROW.

Triggered actions that occur before or after the trigger event execute only once. A BEFORE triggered action executes before the triggering statement, that is, before the occurrence of the trigger event. An AFTER triggered action executes after the action of the triggering statement is complete. BEFORE and AFTER triggered actions execute even if the triggering statement does not process any rows.

Among other uses, you can use BEFORE and AFTER triggered actions to determine the effect of the triggering statement. For example, before you update the quantity column in the items table, you could call the SPL routine upd_items_p1 to calculate the total quantity on order for all items in the table, as the following example shows. The procedure stores the total in a global variable called old_qty.

CREATE PROCEDURE upd_items_p1()

	DEFINE GLOBAL old_qty INT DEFAULT 0;

	LET old_qty = (SELECT SUM(quantity) FROM items);

END PROCEDURE;

After the triggering update completes, you can calculate the total again to see how much it has changed. The following SPL routine, upd_items_p2, calculates the total of quantity again and stores the result in the local variable new_qty. Then it compares new_qty to the global variable old_qty to see if the total quantity for all orders has increased by more than 50 percent. If so, the procedure uses the RAISE EXCEPTION statement to simulate an SQL error.

CREATE PROCEDURE upd_items_p2()
DEFINE GLOBAL old_qty INT DEFAULT 0;
DEFINE new_qty INT;
LET new_qty = (SELECT SUM(quantity) FROM items);
IF new_qty > old_qty * 1.50 THEN
RAISE EXCEPTION -746, 0, 'Not allowed - rule violation';
END IF
END PROCEDURE;

The following trigger calls upd_items_p1 and upd_items_p2 to prevent an extraordinary update on the quantity column of the items table:

CREATE TRIGGER up_items

UPDATE OF quantity ON items

BEFORE(EXECUTE PROCEDURE upd_items_p1())

AFTER(EXECUTE PROCEDURE upd_items_p2());

If an update raises the total quantity on order for all items by more than 50 percent, the RAISE EXCEPTION statement in upd_items_p2 terminates the trigger with an error. When a trigger fails in the database server and the database has logging, the database server rolls back the changes that both the triggering statement and the triggered actions make. For more information on what happens when a trigger fails, see the CREATE TRIGGER statement in the Informix Guide to SQL: Syntax.

A FOR EACH ROW triggered action executes once for each row that the triggering statement affects. For example, if the triggering statement has the following syntax, a FOR EACH ROW triggered action executes once for each row in the items table in which the manu_code column has a value of `KAR':

UPDATE items SET quantity = quantity * 2 WHERE manu_code = 'KAR'

If the triggering statement does not process any rows, a FOR EACH ROW triggered action does not execute.

If the triggering statement is a SELECT statement (the trigger is a select trigger), the triggered actions execute after all processing on the retrieved row is complete. However, the triggered actions might not execute immediately; a FOR EACH ROW action executes for every instance of a row that is returned to the user. For example, in a SELECT statement with an ORDER BY clause, all rows must be qualified against the WHERE clause before they are sorted and returned.

When you create a FOR EACH ROW triggered action, you must usually indicate in the triggered action statements whether you are referring to the value of a column before or after the effect of the triggering statement. For example, imagine that you want to track updates to the quantity column of the items table. To do this, create the following table to record the activity:

CREATE TABLE log_record

	(item_num   	 		SMALLINT,

	ord_num      			INTEGER,

	username     			CHARACTER(8),

	update_time			 DATETIME YEAR TO MINUTE,

	old_qty      			SMALLINT,

	new_qty      			SMALLINT);

To supply values for the old_qty and new_qty columns in this table, you must be able to refer to the old and new values of quantity in the items table; that is, the values before and after the effect of the triggering statement. The REFERENCING clause enables you to do this.

The REFERENCING clause lets you create two prefixes that you can combine with a column name, one to reference the old value of the column and one to reference its new value. These prefixes are called correlation names. You can create one or both correlation names, depending on your requirements. You indicate which one you are creating with the keywords OLD and NEW. The following REFERENCING clause creates the correlation names pre_upd and post_upd to refer to the old and new values in a row:

REFERENCING OLD AS pre_upd NEW AS post_upd

The following triggered action creates a row in log_record when quantity is updated in a row of the items table. The INSERT statement refers to the old values of the item_num and order_num columns and to both the old and new values of the quantity column.

FOR EACH ROW(INSERT INTO log_record
VALUES (pre_upd.item_num, pre_upd.order_num, USER, CURRENT,
pre_upd.quantity, post_upd.quantity));

The correlation names defined in the REFERENCING clause apply to all rows that the triggering statement affects.

Important: If you refer to a column name in the triggering table and do not use a correlation name, the database server makes no special effort to search for the column in the definition of the triggering table. You must always use a correlation name with a column name in SQL statements in a FOR EACH ROW triggered action, unless the statement is valid independent of the triggered action. For more information, see the CREATE TRIGGER statement in the "Informix Guide to SQL: Syntax."

Using the WHEN Condition

As an option, you can precede a triggered action with a WHEN clause to make the action dependent on the outcome of a test. The WHEN clause consists of the keyword WHEN followed by the condition statement given in parentheses. In the CREATE TRIGGER statement, the WHEN clause follows the keywords BEFORE, AFTER, or FOR EACH ROW and precedes the triggered-action list.

When a WHEN condition is present, if it evaluates to true, the triggered actions execute in the order in which they appear. If the WHEN condition evaluates to false or unknown, the actions in the triggered-action list do not execute. If the trigger specifies FOR EACH ROW, the condition is evaluated for each row also.

In the following trigger example, the triggered action executes only if the condition in the WHEN clause is true; that is, if the post-update unit price is greater than two times the pre-update unit price:

CREATE TRIGGER up_price
UPDATE OF unit_price ON stock
REFERENCING OLD AS pre NEW AS post
FOR EACH ROW WHEN(post.unit_price > pre.unit_price * 2)
(INSERT INTO warn_tab VALUES(pre.stock_num, pre.manu_code,
pre.unit_price, post.unit_price, CURRENT))

For more information on the WHEN condition, see the CREATE TRIGGER statement in the Informix Guide to SQL: Syntax.

Probably the most powerful feature of triggers is the ability to call an SPL routine as a triggered action. The EXECUTE PROCEDURE statement, which calls an SPL routine, lets you pass data from the triggering table to the SPL routine and also to update the triggering table with data returned by the SPL routine. SPL also lets you define variables, assign data to them, make comparisons, and use procedural statements to accomplish complex tasks within a triggered action.

You can pass data to an SPL routine in the argument list of the EXECUTE PROCEDURE statement. The EXECUTE PROCEDURE statement in the following trigger example passes values from the quantity and total_price columns of the items table to the SPL routine calc_totpr:

CREATE TRIGGER upd_totpr
UPDATE OF quantity ON items
REFERENCING OLD AS pre_upd NEW AS post_upd
FOR EACH ROW(EXECUTE PROCEDURE calc_totpr(pre_upd.quantity,
post_upd.quantity, pre_upd.total_price) INTO total_price)

Passing data to an SPL routine lets you use it in the operations that the procedure performs.

The EXECUTE PROCEDURE statement in the preceding trigger calls the SPL routine that the following example shows. The procedure uses SPL to calculate the change that needs to be made to the total_price column when quantity is updated in the items table. The procedure receives both the old and new values of quantity and the old value of total_price. It divides the old total price by the old quantity to derive the unit price. It then multiplies the unit price by the new quantity to obtain the new total price.

CREATE PROCEDURE calc_totpr(old_qty SMALLINT, new_qty SMALLINT,
total MONEY(8)) RETURNING MONEY(8);
DEFINE u_price LIKE items.total_price;
DEFINE n_total LIKE items.total_price;
LET u_price = total / old_qty;
LET n_total = new_qty * u_price;
RETURN n_total;
END PROCEDURE;

In this example, SPL lets the trigger derive data that is not directly available from the triggering table.

Within a triggered action, the INTO clause of the EXECUTE PROCEDURE statement lets you update nontriggering columns in the triggering table. The EXECUTE PROCEDURE statement in the following example calls the calc_totpr SPL procedure that contains an INTO clause, which references the column total_price:

FOR EACH ROW(EXECUTE PROCEDURE calc_totpr(pre_upd.quantity,
post_upd.quantity, pre_upd.total_price) INTO total_price);

The value that is updated into total_price is returned by the RETURN statement at the conclusion of the SPL procedure. The total_price column is updated for each row that the triggering statement affects.