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

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.