Implementing a trigger on a status change/insert rec to update a flag

Question

Post reply

Implementing a trigger on a status change/insert rec to update a flag

sqlsurfing

SSCarpal Tunnel

Points: 4883
More actions
December 19, 2018 at 10:02 am

#376664

Hi wondering if someone can give some insight into what works better for a trigger below:
example:
Simplifying
Table Customer
   id int
   Change_Flag bit
   status char(1)
Generally records are
id (some unqiue id) -- this is an indexed column
change_flag 0
status null
During the day we may get new records inserted with status with "X" or updated for the status column with "X"
when status = "X" then change_flag needs to be set to 1
I'm interested in a after insert, update -- which would be a better implementation ?
Or is there a best practice implementation that would be better.
alter trigger trg_up_in_changeflag on example_table after update, insert
as
begin
   IF UPDATE(status)
   begin
      update test_table
set flag =1
from example_table et
where status = 'X'
and EXISTS (select 1 from inserted i wherei.id = et.id)
end
end
or
begin
   IF UPDATE(status)
   begin
      update test_table
set flag =1
from example_table et ,
inserted i
     wherei.id = et.id
end
end
Thanks for the ideas!
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sqlsurfing SSCarpal Tunnel Points: 4883 More actions · Answer 1

This one is not so popular

I'm going to go with

IF UPDATE(status)

begin

update example_table

set flag =1

from example_table et

where status = 'X'

and EXISTS (select 1 from inserted i wherei.id = et.id)

end

The index on 'id' in example_table will make the update quick for setting the flag in option 2 but option one
I believe "EXISTS" i faster as evals true/false rather than the whole join of the query is that right?

I have not wrote T-SQL in awhile so figure some of those more fluent can confirm

thanks!

--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully

ScottPletcher SSC Guru Points: 101192 More actions · Answer 2

I'd add the standard "nocount" setting, but other than that it looks good:

alter trigger trg_up_in_changeflag on example_table after update, insert

as

set nocount on

begin

IF UPDATE(status)

begin

update test_table

set flag =1

from example_table et

where status = 'X'

and EXISTS (select 1 from inserted i where i.id = et.id)

end /*if*/

end /*begin*/

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sqlsurfing SSCarpal Tunnel Points: 4883 More actions · Answer 3

ScottPletcher - Wednesday, December 19, 2018 1:09 PM
I'd add the standard "nocount" setting, but other than that it looks good:
alter trigger trg_up_in_changeflag on example_table after update, insert
as
set nocount on
begin
IF UPDATE(status)
begin
update test_table
set flag =1
from example_table et
where status = 'X'
and EXISTS (select 1 from inserted i where i.id = et.id)
end /*if*/
end /*begin*/

Hey great thanks so much Scott!
Good call on nocount... reduce network traffic with those unneeded messages

--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully

Implementing a trigger on a status change/insert rec to update a flag

Cookies on SQLServerCentral