项目需要写了几个数据库同步用的
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;