DB2 trigger new view doesn't reflect data updated by previous triggers

Question

Let's say I have created the following tables:

create table SAMPLE (
    ID INTEGER,
    COL_A INTEGER,
    COL_B INTEGER
);
create table SAMPLE_CLONE (
    ID INTEGER,
    COL_A INTEGER,
    COL_B INTEGER
);

And I have created the following triggers:

-- Increment COL_A on every update
CREATE TRIGGER INC_COL_A AFTER UPDATE ON SAMPLE
REFERENCING  OLD AS oldrow  NEW AS newrow  
FOR EACH ROW MODE DB2SQL 
WHEN (oldrow.COL_A = newrow.COL_A)
UPDATE SAMPLE SET COL_A = COL_A+1 WHERE ID = oldrow.ID;

-- Replicate inserts from SAMPLE to SAMPLE_CLONE
CREATE TRIGGER REPLICATE_INSERTED_DATA
AFTER INSERT ON SAMPLE
REFERENCING NEW AS newrow
FOR EACH ROW MODE DB2SQL
INSERT INTO SAMPLE_CLONE (ID, COL_A, COL_B) VALUES (newrow.ID, newrow.COL_A, newrow.COL_B);

-- Replicate updates from SAMPLE to SAMPLE_CLONE
CREATE TRIGGER REPLICATE_UPDATED_DATA
AFTER UPDATE ON SAMPLE
REFERENCING NEW AS newrow OLD AS oldrow
FOR EACH ROW MODE DB2SQL
UPDATE SAMPLE_CLONE SET COL_A = newrow.COL_A, COL_B = newrow.COL_B WHERE ID = newrow.ID;

The issue I'm having is that, after I run any update on SAMPLE table, the COL_A latest value incremented by trigger INC_COL_A, is not reflected into the newrow during the trigger REPLICATE_UPDATED_DATA processing. For example, if I have the following data:

INSERT INTO SAMPLE (ID, COL_A, COL_B) VALUES (1, 1, 100);

SAMPLE

ID	COL_A	COL_B
1	1	100

SAMPLE_CLONE

ID	COL_A	COL_B
1	1	100

Then, after running the following command:

UPDATE SAMPLE SET COL_B = 200 WHERE ID = 1;

I get the following results committed on these tables:

SAMPLE

ID	COL_A	COL_B
1	2	200

SAMPLE_CLONE

ID	COL_A	COL_B
1	1	200

Notice that the record on SAMPLE_CLONE.COL_A wasn't replicated by the REPLICATE_UPDATED_DATA trigger because it didn't get the update made by INC_COL_A trigger.

I'm having this issue with DB2 11.5

Answer 1

That's not the correct way to have a update (or insert) trigger change the value being written... You want to make use of a BEFORE update(insert) trigger

-- Increment COL_A on every update
CREATE TRIGGER INC_COL_A BEFORE UPDATE ON SAMPLE
REFERENCING  OLD AS oldrow  NEW AS newrow  
FOR EACH ROW MODE DB2SQL 
WHEN (oldrow.COL_A = newrow.COL_A)
 SET newrow.COL_A = oldrow.COL_A+1 ;