您的位置: 旅游网 > 网红

DB2 9数据库中有关同步trigger的记录_a

发布时间:2020-01-16 11:31:55

项目需要写了几个数据库同步用的 trigger ,就是记录用户的操作到一个 temp 表,然后每天通过Webservice 同步到其它系统,同步成功清空该 temp 表。自认为写的还行,做个记录。是 DB2 的。

-- 用户组新增触发器 --DROP TRIGGER TG_USERG; CREATE TRIGGER LIBING.TG_USERG AFTER INSERT ON LIBING.TM_USERG REFERENCING NEW AS NROW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC declare @groupId integer; declare @name varchar(30); declare @descn varchar(100); declare @syntype varchar(4); declare @ddlsql varchar(1024); declare @isprimary char(1); declare @updateTime timestamp; declare @createTime timestamp; declare @createBy integer; declare @updateBy integer; declare @groupType integer; declare @adminType integer; declare @appId integer; declare @oldGroupId integer; set @groupId=NROW.GROUP_ID; set @name=NROW.name; set @descn=NROW.descn; set @syntype=NROW.syn_type; set @ddlsql=NROW.ddlsql; set @isprimary=NROW.isprimary; set @updateTime=NROW.update_time; set @createTime=NROW.create_time; set @createBy=NROW.create_by; set @updateBy=NROW.update_by; set @groupType=NROW.group_type; set @adminType=NROW.admin_type; set @appId=NROW.app_id; INSERT INTO TM_USERG_TEMP(GROUP_ID,NAME,DESCN,DDLSQL,ISPRIMARY,UPDATE_TIME,CREATE_TIME, CREATE_BY,UPDATE_BY,GROUP_TYPE,ADMIN_TYPE,APP_ID,ACTION) VALUES (@groupId,@name,@descn,@ddlsql,@isprimary,@updateTime,@createTime,@createBy,@updateBy,@groupType,@adminType,@appId,'INSERT'); END; -- 更新用户组数据的触发器 - DROP TRIGGER TG_USERG_UPDATE; CREATE TRIGGER TG_USERG_UPDATE AFTER UPDATE ON TM_USERG REFERENCING NEW AS NROW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC declare @groupId integer; declare @name varchar(30); declare @descn varchar(100); declare @syntype varchar(4); declare @ddlsql varchar(1024); declare @isprimary char(1); declare @updateTime timestamp; declare @createTime timestamp; declare @createBy integer; declare @updateBy integer; declare @groupType integer; declare @adminType integer; declare @appId integer; set @groupId=NROW.GROUP_ID; set @name=NROW.name; set @descn=NROW.descn; set @syntype=NROW.syn_type; set @ddlsql=NROW.ddlsql; set @isprimary=NROW.isprimary; set @updateTime=NROW.update_time; set @createTime=NROW.create_time; set @createBy=NROW.create_by; set @updateBy=NROW.update_by; set @groupType=NROW.group_type; set @adminType=NROW.admin_type; set @appId=NROW.app_id; -- 如果已经有 update 则只记录最后一条 update IF EXISTS(SELECT GROUP_ID FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId AND ACTION='UPDATE') THEN UPDATE TM_USERG_TEMP SET GROUP_ID=@groupId, NAME=@name,DESCN=@descn,DDLSQL=@ddlsql, ISPRIMARY=@isprimary,UPDATE_TIME=@updateTime, CREATE_TIME=@createTime,CREATE_BY=@createBy, UPDATE_BY=@updateBy,GROUP_TYPE=@groupType, ADMIN_TYPE=@adminType,APP_ID=@appId,ACTION='UPDATE' where GROUP_ID=@groupId AND ACTION='UPDATE'; -- 如果有 insert 则把后面的 update 当作 insert ELSEIF EXISTS(SELECT GROUP_ID FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId AND ACTION='INSERT') THEN UPDATE TM_USERG_TEMP SET GROUP_ID=@groupId, NAME=@name,DESCN=@descn,DDLSQL=@ddlsql, ISPRIMARY=@isprimary,UPDATE_TIME=@updateTime, CREATE_TIME=@createTime,CREATE_BY=@createBy, UPDATE_BY=@updateBy,GROUP_TYPE=@groupType, ADMIN_TYPE=@adminType,APP_ID=@appId,ACTION='INSERT' where GROUP_ID=@groupId AND ACTION='INSERT'; ELSE INSERT INTO TM_USERG_TEMP(GROUP_ID,NAME,DESCN,DDLSQL,ISPRIMARY,UPDATE_TIME,CREATE_TIME, CREATE_BY,UPDATE_BY,GROUP_TYPE,ADMIN_TYPE,APP_ID,ACTION) VALUES (@groupId,@name,@descn, @ddlsql,@isprimary,@updateTime,@createTime,@createBy,@updateBy,@groupType,@adminType,@appId,'UPDATE'); end if; END; -- 删除用户组触发器--DROP TRIGGER TG_USERG_DELETE;CREATE TRIGGER TG_USERG_DELETE AFTER DELETE ON TM_USERG REFERENCING OLD AS OROW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC declare @groupId integer; declare @name varchar(30); declare @descn varchar(100); declare @syntype varchar(4); declare @ddlsql varchar(1024); declare @isprimary char(1); declare @updateTime timestamp; declare @createTime timestamp; declare @createBy integer; declare @updateBy integer; declare @groupType integer; declare @adminType integer; declare @appId integer; set @groupId=OROW.GROUP_ID; set @name=OROW.name; set @descn=OROW.descn; set @syntype=OROW.syn_type; set @ddlsql=OROW.ddlsql; set @isprimary=OROW.isprimary; set @updateTime=OROW.update_time; set @createTime=OROW.create_time; set @createBy=OROW.create_by; set @updateBy=OROW.update_by; set @groupType=OROW.group_type; set @adminType=OROW.admin_type; set @appId=OROW.app_id; -- 如果没有操作记录,则插入 delete 记录 IF NOT EXISTS(SELECT GROUP_ID FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId) THEN INSERT INTO TM_USERG_TEMP(GROUP_ID,NAME,DESCN,DDLSQL,ISPRIMARY,UPDATE_TIME,CREATE_TIME, CREATE_BY,UPDATE_BY,GROUP_TYPE,ADMIN_TYPE,APP_ID,ACTION) VALUES (@groupId,@name,@descn, @ddlsql,@isprimary,@updateTime,@createTime,@createBy,@updateBy,@groupType,@adminType,@appId,'DELETE'); -- 如果有 insert 记录,则整体结果相当于没有进行任何操作 ELSEIF EXISTS(SELECT GROUP_ID FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId and ACTION='INSERT') THEN DELETE FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId and ACTION='INSERT'; -- 如果没有 insert 记录,则只需记录最后的 delete 操作 ELSE UPDATE TM_USERG_TEMP set ACTION='DELETE' where GROUP_ID=@groupId; END IF; END;

丁桂薏芽健脾吃多久
关节肿大能吃藤黄健骨丸吗
治疗勃起功能障碍的好方法
老君炉藤黄健骨丸治骨质疏松吗
猜你会喜欢的
猜你会喜欢的