Utilizing Triggers within DB2, Page 2
Trigger Activation Time
As you saw in previous examples, you can define a trigger as BEFORE update event or AFTER update event. This clause dictates whether a trigger gets "fired" before or after the update event. You can use this clause to help you "react" to the update event in a different way. The BEFORE triggers are generally used to:
- Perform validation of input data.
- Automatically generate values for newly inserted rows.
- Read from other tables for cross-referencing purposes.
The AFTER triggers are generally used for application processing. These operations include but are not limited to:
- Performing "follow-up" update operations in the database.
- Performing actions outside the database; for example, to support alerts.
Note: Actions performed outside the database are not rolled back if the trigger is rolled back.
When you want to access the value of a column being updated from within a trigger, you can do so by using Transition Variables. There are two transition variables that you can use:
OLD: This variable specifies an old state of the row; that is, the value of the row/column before the update event occurred.
NEW: This variable specifies a new or current state of the row; that is, the value of the row/column after the update occurred.
In the following example, the PROD_STATUS_CODE_CHANGE trigger executes the STATUS_CHANGE_PROC stored procedure. Only the PRODUCT_STATUS_CODE is changed from DELETED to some other status, or is DELETED from some other status.
CREATE TRIGGER PROD_STATUS_CODE_CHANGE AFTER UPDATE OF PRODUCT_STATUS_CODE ON PRODUCT_TABLE REFERENCING OLD AS OLD_ROW NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL WHEN (OLD_ROW.PRODUCT_STATUS_CODE = 'DELETED' OR NEW_ROW.PRODUCT_STATUS_CODE = 'DELETED') BEGIN ATOMIC CALL STATUS_CHANGE_PROC (NEW_ROW.PRODUCT_ID); END;
The above example references key word WHEN. You have to remember that a trigger, once attached to a table, will always be called when an update event occurs. The WHEN clause determines whether or not a trigger's body gets executed. In this example, the trigger CALL_PARENTS only inserts a row into a PENDING_CALLS table if student's score is below 65.
CREATE TRIGGER CALL_PARENTS AFTER UPDATE OF SCORE ON SCORE_TABLE REFERENCING NEW AS NEW_SCORE FOR EACH ROW MODE DB2SQL WHEN (NEW_SCORE.SCORE < 65) BEGIN ATOMIC INSERT INTO PENDING_CALLS VALUES (NEW_SCORE.STUDENT_ID); END;
Trigger and update events are part of the same call
This is a very important aspect of trigger-based development. Even though triggers are "fired" by the DBMS and not explicitly evoked by the application, the execution is tied to the update event that initiated a trigger call. In other words, the control from the SQL statement will not return to the caller until a trigger tied to the table that is being updated finishes its execution.
Turning On/Off triggers in DB2
Unfortunately, DB2 triggers, once defined, cannot be deactivated. That is why it is a good idea to build in a mechanism to control trigger execution without actually dropping a trigger if you do not want it to run. For example, you can define a table with two columns—the trigger name and a yes/no indicator. This table then can be used by the WHEN clause of a trigger. In the example below, the GET_SCORE_SUM trigger executes only if the yes/no indicator is set to 'YES'.
CREATE GET_SCORE_SUM AFTER INSERT ON SCORE_TABLE REFERENCING NEW_TABLE AS NEW_SCORES FOR EACH STATEMENT MODE DB2SQL WHEN ('YES' IN SELECT INVOKE_INDICATOR FROM TRIGGER_CONTROL WHERE TRIGGER_NAME = 'GET_SCORE_SUM') BEGIN ATOMIC -- SQL CODE END;
In this article, you covered the basic concepts of triggers. You also reviewed several benefits of using triggers. This article just scratches the surface of trigger-based development, but it is enough to get you started.
About the Author
Aleksey Shevchenko has been working with object-oriented languages for over seven years. He has been implementing Enterprise IT solutions for Wall Street and the manufacturing and publishing industries.