Utilizing Triggers within DB2
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 meI 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
- 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.
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