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