7.13. Triggers

A trigger is a database object associated to a table or a view that contains some application logic, which is executed automatically upon an INSERT, UPDATE, or DELETE operation on the table or view. For example, you can use a trigger:

• To validate the input in an INSERT statement

• To compare the new value of a row being updated to an old value

• To insert logging information to another table for audit trail purposes when a row is deleted

Triggers can be classified as BEFORE, AFTER, or INSTEAD OF triggers.

BEFORE triggers are activated before any table data is affected by the triggering SQL statement. For example, if you are inserting a row into a table, the BEFORE trigger is activated first, before the INSERT is completed.

AFTER triggers are activated after the triggering SQL statement has successfully completed. For example, if a DELETE operation on table A completed successfully, an AFTER trigger could be invoked to perform an INSERT on table B.

INSTEAD OF triggers are used to perform INSERT, UPDATE, or DELETE operations on views where these operations are otherwise not allowed. Though read-only views cannot be modified, the underlying tables can; thus, by using an INSTEAD OF trigger, you can make sure that logic is triggered when the view is affected, but the action is performed on the tables themselves.

To create a trigger, use the CREATE TRIGGER statement as demonstrated here.

					CREATE TRIGGER default_time
					NO CASCADE BEFORE INSERT ON schedule
					REFERENCING NEW AS n
					FOR EACH ROW
					MODE DB2SQL
					WHEN (n.start_time IS NULL)
					SET n.start_time = '12:00'
				

This example shows a BEFORE trigger that is activated when an INSERT statement is performed on table schedule. If the row being inserted has a value of NULL for column start_time, the code will assign a value of 12:00 and then continue with the INSERT operation. The REFERENCING NEW clause simply indicates a way to identify the new value of a column.

Here is another example, this time for an AFTER trigger.

					CREATE TRIGGER audit_qty
					AFTER UPDATE OF quantity ON inventory
					REFERENCING OLD AS o NEW AS n
					FOR EACH ROW
					MODE DB2SQL
					INSERT INTO sold
					VALUES (n.product_ID, n.daysold, o.quantity - n.quantity)
				

This AFTER trigger can be used in the following scenario. Let's say you administer a convenience store. You would like to know how many items of each product are sold per day; therefore, you perform a count every night and update your database with the new count. With the help of this AFTER trigger, you can easily query the sold table, which is automatically updated when you update the column quantity of table inventory. The number of items sold for the day is obtained by substracting the old quantity value minus the new quantity value.

Next we show an example of an INSTEAD OF trigger.

					CREATE TRIGGER update_view2
					INSTEAD OF UPDATE
					ON view2
					 REFERENCING OLD AS o NEW AS n
					FOR EACH ROW
					MODE DB2SQL
					BEGIN ATOMIC
					UPDATE table2
					SET region = n.region
					WHERE region = o.region;
					END
				

This example demonstrates how a read-only view can still be updated by using INSTEAD OF triggers. In the example, the trigger updates the region column of table table2 when the view view2 (a read-only view) is updated.