DB2 触发器新视图不反映先前触发器更新的数据

Question

假设我已经创建了以下表格：

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

我创建了以下触发器：

-- 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;

我遇到的问题是，在SAMPLE表上运行任何更新后，在触发器REPLICATE_UPDATED_DATA处理期间，由触发器INC_COL_A递增的COL_A最新值不会反映到newrow中。例如，如果我有以下数据：

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

样本

100

ID	COL_A	COL_B
1	2

共享事件、时间和目标的触发器按创建顺序执行。每个BEFORE触发器都可以修改NEW ROW，随后的BEFORE触发器将使用该修改行。每个AFTER触发器都将接收相同的最后一行，没有一个可以修改它

这里CCD_ 7和CCD_。

所以当UPDATE SAMPLE SET COL_B = 200 WHERE ID = 1;运行时

1. DB2生成一个具有COLB_B = 200的中间行
2. 触发INC_COL_A，执行UPDATE SAMPLE SET COL_A = 2
3. 此更新再次触发INC_COL_A，但由于COL_A在旧行和新行中相同，因此不会发生任何事情
4. REPLICATE_UPDATED_DATA用COL_A = 2, COL_B = 200处理该行，并将其复制到SAMPLE_CLONE中
5. INC_COL_A中的UPDATE语句现在已经完成，但REPLICATE_UPDATED_DATA仍然需要处理COL_A = 1, COL_B = 200

最后，COL_A = 1和它遵循一个逻辑。在INC_COL_A之前创建REPLICATE_UPDATED_DATA，触发器将按预期工作。

检查实际的行值是否与旧的行值匹配是一个解决方案，但可能不是您在实际应用程序中所需要的。

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);

这不是让更新(或插入(触发器更改正在写入的值的正确方式。。。您想要使用BEFORE更新(插入(触发

-- 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 ;