Informix Guide to SQL: Tutorial

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

Home Contents Index Master Index New Book

Using Triggered Actions

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.

Using BEFORE and AFTER Triggered Actions

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, as the following example shows, to calculate the total quantity on order for all items in the table. The routine 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 routine 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 INFORMIX-Universal Server and the database has logging, the database server rolls back the changes made by both the triggering statement and the triggered actions. For more information on what happens when a trigger fails, see CREATE TRIGGER in the Informix Guide to SQL: Syntax.

Using FOR EACH ROW Triggered Actions

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.

Using the REFERENCING Clause

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, you 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 affected by the triggering statement.

Important: If you refer to a column name in the triggering table without using 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 within a FOR EACH ROW triggered action, unless the statement is valid independent of the triggered action. For more information, see CREATE TRIGGER 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.order_num, 

		pre.unit_price, post.unit_price, CURRENT))

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

Using SPL Routines as Triggered Actions

Probably the most powerful feature of triggers is the ability to call an SPL routine as a triggered action. The EXECUTE FUNCTION 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 that the SPL routine returns. SPL also lets you define variables, assign data to them, make comparisons, and use procedural statements to accomplish complex tasks within a triggered action.

Passing Data to an SPL Routine

You can pass data to an SPL routine in the argument list of an EXECUTE PROCEDURE or EXECUTE FUNCTION statement. Figure 15-2 shows an EXECUTE FUNCTION statement that passes values from the quantity and total_price columns of the items table to the SPL routine calc_totpr:

Figure 15-2

CREATE TRIGGER upd_totpr

UPDATE OF quantity ON items

REFERENCING OLD AS pre_upd NEW AS post_upd

FOR EACH ROW(EXECUTE FUNCTION calc_totpr(pre_upd.quantity,

	post_upd.quantity, pre_upd.total_price) INTO total_price)

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

Figure 15-3 shows the SPL routine calc_totpr, which is executed in the trigger example shown in Figure 15-2. SPL is used in the calc_totpr routine to calculate the change that needs to be made to the total_price column when quantity is updated in the items table.

Figure 15-3

CREATE FUNCTION 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 FUNCTION;

The calc_totpr routine receives both the old and new values of quantity and the old value of total_price and performs the following operations:

Divides the old total price by the old quantity to derive the unit price.
Multiplies the unit price by the new quantity to obtain the new total price.
Returns the new total price.

The EXECUTE FUNCTION statement in the trigger example of Figure 15-2 calls the calc_totpr routine. In this example, SPL allows the trigger to derive data (from the calc_totpr routine) that is not directly available from the triggering table.

Updating Nontriggering Columns with Data from an SPL Routine

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

FOR EACH ROW(EXECUTE FUNCTION 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 routine. The total_price column is updated for each row that the triggering statement affects.

Informix Guide to SQL: TutorialChapter 15: Creating and Using Triggers Home Contents Index Master Index New Book

Using Triggered Actions

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

Home Contents Index Master Index New Book