??? 項(xiàng)目需要寫(xiě)了幾個(gè)數(shù)據(jù)庫(kù)同步用的
trigger
,就是記錄用戶(hù)的操作到一個(gè)
temp
表,然后每天通過(guò)
webservice
同步到其它系統(tǒng),同步成功清空該
temp
表。自認(rèn)為寫(xiě)的還行,做個(gè)記錄。是
db2
的。
?
--
用戶(hù)組新增觸發(fā)器
--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;
?
? --
更新用戶(hù)組數(shù)據(jù)的觸發(fā)器
?-- 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;
?????????????
?
?????????????
?--
如果已經(jīng)有
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
當(dāng)作
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;??????????
?
?
--
刪除用戶(hù)組觸發(fā)器
--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;
??????
?
??????
?? --
如果沒(méi)有操作記錄,則插入
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
記錄,則整體結(jié)果相當(dāng)于沒(méi)有進(jìn)行任何操作
?????????????
?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';
?????????????
?--
如果沒(méi)有
insert
記錄,則只需記錄最后的
delete
操作
?????????????
?ELSE
?????????????
?
????
? UPDATE TM_USERG_TEMP set ACTION='DELETE' where GROUP_ID=@groupId;
??????
?? END IF;
??????
??
??????
?? END;