December 19, 2018 at 10:02 am
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.
end
end
or
inserted i
end
end
Thanks for the ideas!
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
December 19, 2018 at 1:02 pm
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 😀
December 19, 2018 at 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*/
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 19, 2018 at 1:16 pm
ScottPletcher - Wednesday, December 19, 2018 1:09 PMI'd add the standard "nocount" setting, but other than that it looks good:
alter trigger trg_up_in_changeflag
on example_table
after update, insertas
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 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply