Trigger Help

Hi

You seem to depend on the column signstatus_c.
You're probably getting the error because you're updating the table containing the trigger.
Try checking for which column getting updated before doing anything.

ALTER  trigger [ar].[t_98TO01] 
on [ar].[activwork]
for  update
as
IF UPDATE(signstatus_c)
BEGIN
    declare @signstatus char (1)
    declare @awuid char (18)

    select @signstatus = inserted.signstatus_c from inserted
    select @awuid = inserted.uniqueid_c from inserted

    --This sets attendance code to 01 when the record is signed
    if @signstatus = 'S' 
    begin   
        update ar.activwork
        set attendance_c = '01'
       where ar.activwork.uniqueid_c = @awuid
       and ar.activwork.attendance_c in('98','99') and ar.activwork.activity_c <> '341'
    end
END

Select allOpen in new window

I gave you the wrong code sorry here  is the code causing the error. sorry for that

USE [BHS]
GO

/****** Object:  Trigger [ar].[t_98TO01]    Script Date: 10/18/2011 11:26:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Walter Cantin>
-- Create date: <Create Date,10/27/2010,>
-- Description:	<Description,This trigger sets the attendance code to 01 when it is 98 and a clinician signs the service,Does work on CaseManagement services>
-- =============================================
alter trigger [ar].[t_98TO01] 
on [ar].[activwork]
for  update
as

declare @signstatus char (1)
declare @awuid char (18)
declare @activity char (5)
declare @staff char (11)

select @signstatus = inserted.signstatus_c from inserted
select @awuid = inserted.uniqueid_c from inserted
select @activity = inserted.activity_c from inserted
select @staff = inserted.staffcode_c from inserted


--This sets attendance code to 01 when the record is signed


if @signstatus = 'S' 
begin
   update aw
   set attendance_c = '01'
   from ar.activwork aw 
   join inserted i on i.uniqueid_c = aw.uniqueid_c
  where exists ( select * from ar.activwork aw2 where aw2.uniqueid_c = i.uniqueid_c and aw2.activity_c <> '341')
  or exists (select * from ar.activwork aw2 where aw2.uniqueid_c = i.uniqueid_c and aw2.activity_c in ('641','651','661','671','681') and aw2.staffcode_c <> '3699')
  
 and  i.attendance_c in('98','99') 
end

Select allOpen in new window

you need to allow for multiple updates occurring on the initiating statement

also you only want to update when the value needs to change ... that was causing your trigger to be
invoked recursively and eventually exhausting the recursion limit (32)

ALTER  trigger [ar].[t_98TO01] 
on [ar].[activwork]
for  update
as

--This sets attendance code to 01 when the record is signed

begin
   update a
   set attendance_c = '01'
    from ar.activwork as a
    inner join (select distinct unique_c 
                  from inserted
                 where signstatus_c='S'
               ) as i
      on a.uniqueid_c = i.uniqueid_c
  where a.attendance_c in ('98','99')
  and a.activity_c <> '341' 
   and attendance_c <> '01'
end

Select allOpen in new window

Thanks Lowfat i'll give this a shot and get back to you. Had a dentist Apt. sorry it took awhile to get back to you.

Your code is good Thanks. I have to figgure out why it's not working how I would like it.

is it because the attendance test is only linked to the second exists test?

What it's doing is -- enter this activity 341 and when they sign it (which updates signedstatus='s'  it should stay a 98. instead it changes to an 01. I think it is because the <> 341 changes all activities to 01 attendance other than the 341. I need to figgure out how I can do both checks. I do want all but 341 to change but I only want the 641,651 etc activities to change to an 01 when the staff code is not 3699
 exists ( select uniqueid_c
                   from ar.activwork aw2
                  where aw2.uniqueid_c = i.uniqueid_c
                    and aw2.activity_c <> '341')
         or ( exists (select uniqueid_c
                        from ar.activwork aw2
                       where aw2.uniqueid_c = i.uniqueid_c
                         and aw2.activity_c in ('641','651','661','671','681')  
                         and aw2.staffcode_c <> '3699'
                     )
             and i.attendance_c in ('98','99')
             )
        )
end

I can open another case if youd like lowfat I wouldnt
 want you to work on this unless you are getting points for it. I gave you the points now because you figgured out the error issue. this is a seperate issue. would you like me to open another. i think it would only be fair if you want to help out with this issue.

Sorry put the wrong code in ugh what i meant to say is

What it's doing is -- enter this activity 641 and when they sign it (which updates signedstatus='s'  it should stay a 98. instead it changes to an 01. I think it is because the <> 341 changes all activities to 01 attendance other than the 341. I need to figgure out how I can do both checks. I do want all but 341 to change but I only want the 641,651 etc activities to change to an 01 when the staff code is not 3699
 exists ( select uniqueid_c
                   from ar.activwork aw2
                  where aw2.uniqueid_c = i.uniqueid_c
                    and aw2.activity_c <> '341')
         or ( exists (select uniqueid_c
                        from ar.activwork aw2
                       where aw2.uniqueid_c = i.uniqueid_c
                         and aw2.activity_c in ('641','651','661','671','681')  
                         and aw2.staffcode_c <> '3699'
                     )
             and i.attendance_c in ('98','99')
             )
        )
end