数据库中有关同步的

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

-- 用户组新增触发器 --DROP TRIGGER TG_USERG; CREATE TRIGGER _USERG AFTER INSERT ON _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=OUP_ID; set @name=; set @descn=scn; set @syntype=n_type; set @ddlsql=lsql; set @isprimary=primary; set @updateTime=date_time; set @createTime=eate_time; set @createBy=eate_by; set @updateBy=date_by; set @groupType=oup_type; set @adminType=min_type; set @appId=p_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=OUP_ID; set @name=; set @descn=scn; set @syntype=n_type; set @ddlsql=lsql; set @isprimary=primary; set @updateTime=date_time; set @createTime=eate_time; set @createBy=eate_by; set @updateBy=date_by; set @groupType=oup_type; set @adminType=min_type; set @appId=p_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=OUP_ID; set @name=; set @descn=scn; set @syntype=n_type; set @ddlsql=lsql; set @isprimary=primary; set @updateTime=date_time; set @createTime=eate_time; set @createBy=eate_by; set @updateBy=date_by; set @groupType=oup_type; set @adminType=min_type; set @appId=p_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;