Understanding DB2: Learning Visually with Examples (2nd Edition)

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.