Need Trigger Help

Hello I am very new to triggers and would like some help with an update trigger. the trigger works fine as is but I need to Modify it to set attendance code to '01' where certain conditions are met. so I need set attendance code where @activity in ('641','651','661','671','681) and @staff <>'3699' = '01
then I need a condition for @activity <>341 = '01' so basically the conditions set all activities to an attendance code of '01' when these conditions are met. I'm stumped and the if then else part.
 Any help will be appreciated
create   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 ar.activwork
   set attendance_c = '01'
  where  ar.activwork.uniqueid_c = @awuid
   and ar.activwork.attendance_c in('98','99')
   
end

Open in new window

mguptillAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mguptillAuthor Commented:
This is what I am conceptualizing it as. does this look good. I am going to test I just want some expert opinions.
Create 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' and @activity in ('641','651','661','671','681') and @staff <> '3699'
begin
   update ar.activwork
   set attendance_c = '01'
  where  ar.activwork.uniqueid_c = @awuid
   and ar.activwork.attendance_c in('98','99')
   
end
else 
if @signstatus = 'S' and @activity not in ('341','641','651','661','671','681')
begin
   update ar.activwork
   set attendance_c = '01'
  where  ar.activwork.uniqueid_c = @awuid
   and ar.activwork.attendance_c in('98','99')
   
end
GO

Open in new window

0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That would work that way (as far as I can see). But having an UPDATE trigger using a single select on INSERTED is faulty. If there are more than one records touched, the trigger will not work anymore. You should always just join with INSERTED instead of selecting a single value out of it:
...
update aw
set attendance_c = '01'
from ar.activwork aw join inserted i on aw.uniqueid_c = i.uniqueid_c
where aw.attendance_c in('98','99')
...

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mguptillAuthor Commented:
I think that might be it let me test this out.  Question should I take out the variables and us the join serted rows?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mguptillAuthor Commented:
also what if I wanted only the record touched at that moment to be updated?
0
mguptillAuthor Commented:
Thanks
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
"The record touched at that moment" - you cannot guarantee that, and this is the issue. The join works the same whether you update a single or multiple rows, and it just works. Designing a trigger in MSSQL as a row level trigger (which does not exist in MSSQL) is a common misunderstanding of its trigger concept. Always remember that the trigger is executed at statement level, and the UPDATE / INSERT or whatever has been executed already completely, changing at least a single row.

Since you've accepted the answer already, I reckon you have managed to incorporate my example snippet into your trigger.
0
mguptillAuthor Commented:
Yes I did thank you? I'm just asking questions out of curiosity more than anything being triggers is a relm i seldom cross into. thank you for your help Qlemo
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.