DB2 表字段值变更记录
需求描述:业务需要监控业务表中字段值变更情况,以便进行后续处理;
需求分析:使用触发器方式实现
解决办法:
-- 业务表:
CREATE TABLE t
(
USERID INT
, NAME VARCHAR (10)
, MANAUNITID VARCHAR (10)
);
-- 日志表
CREATE TABLE t_log
(
USERID INT
, SOURCE_UNIT VARCHAR (10)
, TARGET_UNIT VARCHAR (10)
, CHANGE_TYPE INT
);
-- 触发器
CREATE TRIGGER TR_T_UPDATE AFTER
UPDATE OF MANAUNITID ON t REFERENCING OLD AS O NEW AS N
FOR EACH ROW
BEGIN ATOMIC
IF coalesce(o.MANAUNITID,'') != coalesce(n.MANAUNITID,'') THEN
INSERT INTO t_log (USERID
, SOURCE_UNIT
, TARGET_UNIT
, CHANGE_TYPE)
VALUES
(N.USERID
, O.MANAUNITID
, N.MANAUNITID
, 1);
END IF;
END;
-- 功能测试
SELECT * FROM T;
SELECT * FROM T_LOG;
-- 写入数据
INSERT INTO T VALUES(1,'hury','101');
INSERT INTO T VALUES(2,'lj','101');
-- 更新非监控列,查看是否记录日志
UPDATE t SET NAME = 'hury2' WHERE USERID = 1;
-- 更新监控列,但值不变,查看是否记录日志
UPDATE t SET MANAUNITID = '101' WHERE USERID = 1;
-- 更新监控列,更新为新值,查看是否记录日志
UPDATE t SET MANAUNITID = '102' WHERE USERID = 1;
-- 空值测试
UPDATE t SET manaunitid = NULL WHERE userid = 2;
UPDATE t SET manaunitid = '101' WHERE userid = 2;
-- 清理测试表
DROP TRIGGER TR_T_UPDATE;
DROP TABLE t;
DROP TABLE t_log;