-- 用户组新增触发器
            --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;