Utilizing Triggers within DB2

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.

Transition Variables

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;

Triggered Action

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 Hints

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;

Conclusion

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.