• 触发器--里面涉及触发器调存储过程


    if (object_id('TR_EMP_CHANGE_USRA91', 'TR') is not null)
        drop trigger TR_EMP_CHANGE_USRA91
    go
    create trigger TR_EMP_CHANGE_USRA91
    on USRA91
    AFTER DELETE,INSERT,UPDATE
    AS
    DECLARE
      @strSQL   varchar(1000),
      @outappId varchar(100),
      @A0100    varchar(30),
      @syncFlag char(1),
      @unit     varchar(30),
      @dept     varchar(30),
      @post     varchar(30),
      @jz_str   varchar(300),
      @syncKey  varchar(100),
      @MaxI9999 integer,
      @A91A2    varchar(100),--add by fangkun
      @I9999    integer
    IF NOT EXISTS(SELECT 1 FROM DELETED) OR NOT EXISTS(SELECT 1 FROM INSERTED) OR UPDATE(A91A2) OR UPDATE(A91AA) OR UPDATE(A91A9) OR UPDATE(A91A4) OR UPDATE(I9999)
    BEGIN
      IF EXISTS(SELECT 1 FROM INSERTED)
        DECLARE C_USRA91 CURSOR FOR SELECT A0100,I9999 FROM INSERTED
      ELSE
        DECLARE C_USRA91 CURSOR FOR SELECT A0100,I9999 FROM DELETED
      OPEN C_USRA91
      FETCH NEXT FROM C_USRA91 INTO @A0100,@I9999
      WHILE (@@FETCH_STATUS = 0)
      BEGIN
        SET @jz_str=''
        SET @syncFlag=0
        SELECT @syncKey = GUIDKEY FROM UsrA01 WHERE A0100=@A0100
      IF((select count(*) from inserted a inner join deleted b on a.A0100 = b.A0100 and  a.I9999 = b.I9999  and ((a.A91A2 is null and b.A91A2 is null) or (a.A91A2 =b.A91A2)) and ((a.A91AA is null and b.A91AA is null) or (a.A91AA =b.A91AA)) and ((a.A91A9 is null and b.A91A9 is null) or (a.A91A9 =b.A91A9)) and ((a.A91A4 is null and b.A91A4 is null) or (a.A91A4 =b.A91A4)))=0)
        BEGIN
        DECLARE c_jz CURSOR FOR SELECT A91A2,A91AA,A91A9,A91A4 FROM USRA91 WHERE A0100=@A0100 and A91A7='0' order by i9999;
        OPEN c_jz;
        FETCH NEXT FROM c_jz INTO @A91A2,@unit,@dept,@post
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
          IF LEN(@jz_str) >0 AND ISNULL(@unit,'') <>'' AND ISNULL(@dept,'')<>'' AND ISNULL(@post,'')<>'' AND ISNULL(@A91A2,'')<>''
            SET @jz_str = @jz_str + ';' +@A91A2+ '@'+ @unit + '@' + @dept + '@' + @post
          ELSE IF ISNULL(@unit,'') <>'' AND ISNULL(@dept,'')<>'' AND ISNULL(@post,'')<>'' AND ISNULL(@A91A2,'')<>''
            SET @jz_str = @A91A2+ '@'+ @unit + '@' + @dept + '@' + @post
          FETCH NEXT FROM c_jz INTO @A91A2,@unit,@dept,@post
        END;
        CLOSE c_jz;
        DEALLOCATE c_jz;
        IF EXISTS(SELECT 1 FROM t_hr_view WHERE t_hr_view.UNIQUE_ID=@syncKey)
        BEGIN
          UPDATE t_hr_view SET H01ST = @jz_str,sdate = GETDATE() WHERE t_hr_view.UNIQUE_ID=@syncKey;
          SET @syncFlag=2;
        END;
        IF @syncFlag=2
          EXEC PR_UP_SYNC_FLAG @syncFlag,'',@syncKey,'USR','A_'
    END
        FETCH NEXT FROM C_USRA91 INTO @A0100,@I9999
      END
      CLOSE C_USRA91
      DEALLOCATE C_USRA91
    END

  • 相关阅读:
    WeX5那些坑
    项目总结-微信公众平台Html5
    项目总结-APP中的HTML5
    夜幕团队成员的工资究竟几 K ?
    Docker竟然还能这么玩?商业级4G代理搭建实战!
    今天,大佬云集的夜幕团队正式成立了!
    InnoDB物理行中null值的存储的推断与验证
    探究InnoDB数据页内部行的存储方式
    DAO模式
    JDBC
  • 原文地址:https://www.cnblogs.com/beijingstruggle/p/4904890.html
Copyright © 2020-2023  润新知