December 23, 2015 at 9:36 am
CREATE TRIGGER ToUpdateForABC
ON tbl1
AFTER INSERT
AS
update tbl1 set ConpositeName = 1 where Status = 'Y'
Go
--DROP trigger ToUpdateForABC
----------------------------------------------------------------------
Does anyone know why my simple trigger does not update the last record got inserted to the table? And sorry for posting codes not in IFCode shotcuts. I am not able to get it to work.
Thank you!
December 23, 2015 at 11:22 am
Not sure why the "last row" wasn't affected, since that trigger's going to update every row, which you don't want. You need to join to the inserted pseudo-table to make sure you only update rows that are involved in this specific trigger execution.
CREATE TRIGGER ToUpdateForABC
ON tbl1
AFTER INSERT
AS
SET NOCOUNT ON;
update t1
set ConpositeName = 1
from tbl1 t1
inner join inserted i on i.id = t1.id --change column to match key(s) of table
where t1.Status = 'Y' and (i.status is null or i.status <> 'Y')
GO --end of trigger
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".
December 23, 2015 at 12:07 pm
Thank you ScottPletcher! It works perfectly. I think I should have added the UPDATE in the line of "AFTER INSERT, UPDATE" . I didn't know I can do add more to the After clause.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply