數(shù)據(jù)庫同步trigger的記錄

??? 項目需要寫了幾個數(shù)據(jù)庫同步用的 trigger ，就是記錄用戶的操作到一個 temp 表，然后每天通過 webservice 同步到其它系統(tǒng)，同步成功清空該 temp 表。自認為寫的還行，做個記錄。是 db2 的。

?

-- 用戶組新增觸發(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;

?

? -- 更新用戶組數(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 當作 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;??????????

?

?

-- 刪除用戶組觸發(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;

?????? ?

?????? ?? -- 如果沒有操作記錄，則插入 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;

posted on 2006-10-30 17:06 pesome 閱讀(1412) 評論(0)  編輯  收藏 所屬分類: 數(shù)據(jù)庫