Saturday, 17 August 2013

DB2 Trigger

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. 
              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
            FOR EACH ROW
            MODE DB2SQL
            BEGIN ATOMIC
                     UPDATE table2
                     SET region = n.region
                     WHERE region = o.region;

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.

Created with Artisteer

No comments:

Post a Comment