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
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
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.