項目需要寫了幾個數據庫同步用的 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;
