Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Trigger Help

Any way to make this trigger run better I have attatched an image of the error I get when the trigger fires off
ALTER  trigger [ar].[t_98TO01] 
on [ar].[activwork]
for  update
as

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

Open in new window

SQL-Error.jpg
0
mguptill
Asked:
mguptill
  • 6
  • 3
1 Solution
 
jonaskaCommented:
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

Open in new window

0
 
mguptillAuthor Commented:
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

Open in new window

0
 
LowfatspreadCommented:
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

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LowfatspreadCommented:
more like this then

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

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

begin
   update aw
   set attendance_c = '01'
   from ar.activwork aw
   inner join (Select unique_c ,attendance_c
                 from inserted  
                where signedstatus='s'            
               ) as i
     on i.uniqueid_c = aw.uniqueid_c
  where aw.attendance_c <> '01'
    and (
           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


you need to comment the trigger to actually explain what the two exists are attempting to achieve...
0
 
mguptillAuthor Commented:
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.
0
 
mguptillAuthor Commented:
Your code is good Thanks. I have to figgure out why it's not working how I would like it.
0
 
LowfatspreadCommented:
is it because the attendance test is only linked to the second exists test?

0
 
mguptillAuthor Commented:
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
0
 
mguptillAuthor Commented:
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.
0
 
mguptillAuthor Commented:
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
 
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now