dcsimg
June 18, 2018
Hot Topics:

Utilizing Triggers within DB2

  • December 6, 2005
  • By Aleksey Shevchenko
  • Send Email »
  • More Articles »

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.





Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date