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

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

CodePudding user response：

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 ;

CodePudding user response：

Triggers that share event, time and target are executed in the order they were created. Each BEFORE trigger can modify NEW ROW, subsequent BEFORE triggers will use that modified row. Each AFTER trigger will all receive the same final row, none of them can modify it.

Here INC_COL_A and REPLICATE_UPDATED_DATA are executed in this order.

So when UPDATE SAMPLE SET COL_B = 200 WHERE ID = 1; is run

DB2 produces an intermediate row with COLB_B = 200
INC_COL_A is triggered, executes UPDATE SAMPLE SET COL_A = 2
This update triggers INC_COL_A again but since COL_A is the same in old and new row nothing happens
REPLICATE_UPDATED_DATA handles the row with COL_A = 2, COL_B = 200 and replicates it into SAMPLE_CLONE
The UPDATE statement inside INC_COL_A is now done, but REPLICATE_UPDATED_DATA still has to handle COL_A = 1, COL_B = 200.

In the end, COL_A = 1 and that follows a logic. Create REPLICATE_UPDATED_DATA before INC_COL_A and your triggers will work as expected.

Checking that actual row values match the old row values would be a solution here, but maybe not what you need in your real app.

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
        and (col_a, col_b) = (old_row.col_a, old_row.col_b);