• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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

0
mguptill
Asked:
mguptill
  • 5
  • 2
1 Solution
 
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
 
QlemoC++ DeveloperCommented:
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
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
mguptillAuthor Commented:
also what if I wanted only the record touched at that moment to be updated?
0
 
mguptillAuthor Commented:
Thanks
0
 
QlemoC++ DeveloperCommented:
"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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now