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,--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 (A91 A9) 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 to @A0100, @I9999
while (@ @FETCH_STATUS = 0)
BEGIN
SET @jz_str = ' '
SET @syncFlag =0
SELECT @syncKey = Guidkey from UsrA01 WHERE [email protected]
IF (SELECT COUNT (*) from inserted a inner joins deleted B on a.a0100 = b.a0100 and a.i9999 = b.i9999 and ((A.A91A2 is nul L and B.A91A2 is null) or (A.A91A2 =b.a91a2)) and ((A.a91aa are null and B.A91AA is null) or (A.A91AA =B.A91AA)) and ((a.a9 1A9 is null and B.A91A9 are 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 [e-mail protected] and a91a7= ' 0 ' ORDER by i9999;
OPEN C_JZ;
FETCH NEXT from C_JZ to @A91A2, @unit, @dept, @post
while (@ @FETCH_STATUS = 0)
BEGIN
IF LEN (@jz_str) >0 and ISNULL (@unit, ') <> "and ISNULL (@dept, ') <> ' and ISNULL (@post, ') <> ' and I Snull (@A91A2, ') <> '
SET @jz_str = @jz_str + '; ' [Email protected]+ ' @ ' + @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 to @A91A2, @unit, @dept, @post
END;
CLOSE C_JZ;
Deallocate C_JZ;
IF EXISTS (SELECT 1 from T_hr_view WHERE [email protected])
BEGIN
UPDATE t_hr_view SET h01st = @jz_str, sdate = GETDATE () WHERE [email protected];
SET @syncFlag = 2;
END;
IF @syncFlag =2
EXEC Pr_up_sync_flag @syncFlag, ', @syncKey, ' USR ', ' A_ '
END
FETCH NEXT from C_usra91 to @A0100, @I9999
END
CLOSE c_usra91
Deallocate c_usra91
END
Trigger--involves a trigger to tune the stored procedure