Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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.
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_TABLECREATE 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)
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.
