項(xiàng)目需要寫了幾個(gè)數(shù)據(jù)庫(kù)同步用的 trigger ，就是記錄用戶的操作到一個(gè) temp 表，然后天天通過(guò)webservice 同步到其它系統(tǒng)，同步成功清空該 temp 表。自認(rèn)為寫的還行，做個(gè)記錄。是 db2 的。-- 用戶組新增觸發(fā)器--DROP TRIGGER TG_USERG;CREATE TRIGGER LIBING.TG_USERG AFTER INSERT ON LIBING.TM_USERGREFERENCING NEW AS NROWFOR EACH ROWMODE DB2SQL　BEGIN ATOMICdeclare @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;-- 更新用戶組數(shù)據(jù)的觸發(fā)器- DROP TRIGGER TG_USERG_UPDATE;CREATE TRIGGER TG_USERG_UPDATE AFTER UPDATE ON TM_USERGREFERENCING NEW AS NROWFOR EACH ROWMODE DB2SQLBEGIN ATOMICdeclare @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;-- 假如已經(jīng)有 update 則只記錄最后一條 updateIF EXISTS(SELECT GROUP_ID FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId AND ACTION='UPDATE') THEN UPDATETM_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 當(dāng)作 insertELSEIF　EXISTS(SELECT GROUP_ID FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId AND ACTION='INSERT') THENUPDATE 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;　-- 刪除用戶組觸發(fā)器--DROP TRIGGER TG_USERG_DELETE;CREATE TRIGGER TG_USERG_DELETE AFTER DELETE ON TM_USERGREFERENCING OLD AS OROWFOR EACH ROWMODE DB2SQLBEGIN ATOMICdeclare @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;-- 假如沒(méi)有操作記錄，則插入 delete 記錄IF NOT EXISTS(SELECT GROUP_ID FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId) THENINSERT 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 記錄，則整體結(jié)果相當(dāng)于沒(méi)有進(jìn)行任何操作ELSEIF EXISTS(SELECT GROUP_ID FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId and ACTION='INSERT') THENDELETE FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId and ACTION='INSERT';-- 假如沒(méi)有 insert 記錄，則只需記錄最后的 delete 操作ELSEUPDATE TM_USERG_TEMP set ACTION='DELETE' where GROUP_ID=@groupId;END IF;END;