数据库同步trigger的记录

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