Utilizing Triggers within DB2

• facebook
• twitter
• google+
• in
• Email

I have been recently challenged with implementing an enhancement to an existing Web application and a batch process. Similar to many applications that I encounter in my everyday professional life, this application is poorly written and lacks any basic documentation and source code comments. I have looked at the source code and tried to figure out where I should insert my code that satisfies the requirement. I also have attempted to review the batch processes that are written in Cobol. Then, it hit me—I should be using triggers. In this article, you will learn what triggers are, how they are defined, and when they are used.

What Are Triggers?

In the DB2 Manual, a trigger is defined as "a set of actions that are activated or triggered by an update operation on a specified base table." This is exactly what I needed to use in my enhancement assignment. The triggers allow you to "fire" an event when some other event occurs.

Benefits of Triggers

I have carefully inspected the source code that I had needed to modify and came to the conclusion that the trigger-based design would be optimal in this situation. The advantage to using triggers is that I don't need to modify both the Web application code and the batch process. Modifying both is not a good solution mainly because both changes would execute exactly the same logic. In addition, the change to the Web-based application would have to be written in Java and the change to the batch process—in Cobol. Using triggers allows a developer not to update any existing source code. It would also allow me not to ask the dreaded question, "Will the rest of the code work after I modify it?" Another advantage is that the regression testing of the modified application does not have to be so vigorous. This is due to the fact that triggers do not have to be explicitly called from the application code. But, these are some of the secondary benefits of triggers. Mainly, triggers are be used to:

• Validate input data by using the SIGNAL SQLSTATE SQL statement, the built-in RAISE_ERROR function, or invoke a UDF to return an SQLSTATE indicating that an error has occurred in case invalid data is discovered.

  Note: Validation of the non-transitional data is usually better handled by check and referential constraints.

  • Post a comment
  • Email Article
  • Print Article

  By contrast, triggers are appropriate for validation of transitional data (validation that requires comparisons between the value before and after an update operation).

• Automatically generate values for newly inserted rows (this is known as a surrogate function). That is, implement user-defined default values, possibly based on other values in the row or values in other tables.
• Read from other tables for cross-referencing purposes.
• Write to other tables for audit-trail purposes.
• Support alerts (for example, through electronic mail messages).

Types of Triggers

When a trigger is created, it is associated with a table. An update to the table includes:

• An UPDATE event
• A DELETE event
• An INSERT event

For example,

CREATE TRIGGER CHEAT_SCORE
AFTER INSERT ON SCORE_TABLE
FOR EACH ROW MODE DB2SQL 
UPDATE SCORE_TABLE SET SCORE = SCORE + 1

In the above example, the trigger CHEAT_SCORE will be "fired" every time a new row is inserted into the SCORE_TABLE. This trigger is adding 1 to the score of every new row. This trigger will get executed no matter which process inserts a row into SCORE_TABLE. The AFTER clause indicates that the trigger will get executed after the insert is completed. You have to keep in mind that a trigger will not get executed if the table is populated from a LOAD command.

In the following example, the CLEANUP_DATA trigger deletes all rows from LOG_TABLE after there is a delete event issued against the SCORE_TABLE. The prefix "OLD" that is used in the delete statement in the trigger points to the deleted SCORE_ID from the SCORE_TABLE.

CREATE TRIGGER CLEANUP_SCORE
AFTER DELETE ON SCORE_TABLE
FOR EACH ROW MODE DB2SQL
DELETE FROM LOG_TABLE WHERE SCORE_ID = OLD.SCORE_ID

And finally, the example shown below defines a trigger that is "fired" when an UPDATE statement is executed. The REMOVE_CHEAT_SCORE trigger will subtract 1 from the SCORE column. This trigger is more refined because it is attached not to the whole table, but only to a specific column of the table.

CREATE TRIGGER REMOVE_CHEAT_SCORE
AFTER UPDATE OF SCORE ON SCORE_TABLE
FOR EACH ROW MODE DB2SQL 
UPDATE SCORE_TABLE SET SCORE = SCORE - 1

Trigger Granularity

When a trigger is executed, it runs according to its Granularity:

FOR EACH ROW: The trigger will be executed for each updated row. In the first example, the CHEAT_SCORE trigger will be executed once per insert into the SCORE_TABLE.

CREATE TRIGGER CHEAT_SCORE
AFTER INSERT ON SCORE_TABLE
FOR EACH ROW MODE DB2SQL
UPDATE SCORE_TABLE SET SCORE = SCORE + 1

FOR EACH STATEMENT: The trigger will be executed only once per trigger event. For example, the GET_SCORE_SUM trigger will execute only once when the following statement is issued:

INSERT INTO SCORE_TABLE (SCORE_ID, SCORE)
SELECT SCORE_ID, SCORE FROM HISTORY_SCORE_TABLE

CREATE GET_SCORE_SUM
AFTER INSERT ON SCORE_TABLE
REFERENCING NEW_TABLE AS NEW_SCORES
FOR EACH STATEMENT MODE DB2SQL
UPDATE SCORE_STATS
SET NBEMP = NBEMP + (SELECT SUM(SCORE) FROM NEW_SCORES)

Page 1 of 2