After Update Trigger Only Fired for the first record in the IN close

Question

After Update Trigger Only Fired for the first record in the IN close

Nawar.Tabaa

Ten Centuries

Points: 1352
More actions
May 14, 2011 at 11:51 pm

#239536

I have a table that is updates by:
UPDATE table
SET col = val
WHERE PKCol In (Id1, Id2, Id3);
The updates affect all rows, however the table's After Update Trigger is only fired for the first record in the clustered index!
Do you have any explanation for this?
Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

My guess, the trigger is not written to handle multi-row updates. It probably assumes there'll only ever be a single row updated at a time and hence a single row in inserted & deleted.

Post the trigger code, we can help you fix it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nawar.Tabaa Ten Centuries Points: 1352 More actions · Answer 2

CREATE TRIGGER [dbo].[trg_LogStatus]

ON [dbo].[Waybills]

AFTER INSERT,UPDATE

AS

BEGIN

SET NOCOUNT ON;

DECLARE @WaybillId int

DECLARE @ActionBy int

DECLARE @StatusId smallint

SELECT @WaybillId = [WaybillId],

@StatusId = [CurrentStatus],

@ActionBy = [LastActionBy]

FROM inserted

INSERT INTO [dbo].[WaybillStatuses]

([WaybillId] ,[StatusId] ,[StatusDate] ,[ActionBy])

VALUES

(@WaybillId, @StatusId, GETDATE(), @ActionBy)

END

Many thanks

LutzM SSC Guru Points: 107049 More actions · Answer 3

The issue is caused by your variable assignment. This will only affect rows that meet one criteria.

Try the following instead:

CREATE TRIGGER [dbo].[trg_LogStatus]

ON [dbo].[Waybills]

AFTER INSERT,UPDATE

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO [dbo].[WaybillStatuses]

([WaybillId] ,[StatusId] ,[StatusDate] ,[ActionBy])

SELECT [WaybillId],[CurrentStatus],GETDATE(),[LastActionBy]

FROM inserted

END

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Exactly what I thought

Nawar.Tabaa (5/15/2011)
SELECT @WaybillId = [WaybillId],
@StatusId = [CurrentStatus],
@ActionBy = [LastActionBy]
FROM inserted

That assumes there will only ever be one row in the inserted table. When you updated 3 rows, suddenly there were three rows in inserted, the assumption failed and the trigger did not do what the original developer intended.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nawar.Tabaa Ten Centuries Points: 1352 More actions · Answer 5

Nawar.Tabaa

Ten Centuries

Points: 1352

May 15, 2011 at 6:13 am

#1324685

Really many thanks.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

That is the third time in a week I have seen this CRITICAL flaw in trigger code. Scary how common this is...

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service