当前位置：技术首页 >> 数据库 >> DB2 >> DB2 9数据库中有关同步trigger的记录

DB2 9数据库中有关同步trigger的记录 (1)

2007-01-01 22:26:00 作者：java的足迹　来源：csdn　浏览次数：188 文字大小：【大】【中】【小】

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