数据库同步trigger的记录

pesome

    项目需要写了几个数据库同步用的trigger，就是记录用户的操作到一个temp表，然后每天通过webservice同步到其它系统，同步成功清空该temp表。自认为写的还行，做个记录。是db2的。
<!----><o:p> </o:p>
--用户组新增触发器
--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
<o:p> </o:p>
  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;
<o:p> </o:p>
  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;
<o:p> </o:p>
  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;          
<o:p> </o:p>
<o:p> </o:p>
--删除用户组触发器
--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;