Working with RC/Edit avoid unnecessary Trigger activation

Products

• RC Compare for DB2 for z/OS

Issue/Introduction

Do you have DB2 Triggers that process their actions based on whether a specific column's data changes? If so, be sure to follow the steps below to alter the CREATE TRIGGER DDL to avoid the DB2 Trigger action from "triggering" unnecessarily.

Environment

Release:
Component: RCU

Resolution

Since RC/Edit within the RC/Update product automatically updates all columns data no matter which columns data is actually changed, which reduces processing overhead, the changes below to the CREATE TRIGGER syntax will ensure the DB2 Trigger action is only processed when it should be.
 
In addition to the "REFERENCING NEW AS NROW", a line should be inserted right below it including the clause "OLD AS OROW".
 
In the Atomic text, the WHERE clause should also include "AND NROW.colname <> OROW.colname".
The symbol <> represents "not equal to" and "colname" represents the column that needs to have it's data changed for the trigger action to be run.

The following is an example using the IBM DB2 sample table DSN8710.DEPT:
 
    CREATE TRIGGER DEPTRIG1
    AFTER UPDATE OF DEPTNO ON DSN8710.DEPT
    REFERENCING NEW AS NROW
                OLD AS OROW
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC UPDATE DSN8710.EMP
    SET WORKDEPT = NROW.DEPTNO
    WHERE NROW.DEPTNO <>  OROW.DEPTNO; END