Learning Visually with Examples [Electronic resources] نسخه متنی

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.