Trigger not working as expected during batch update

Question

Trigger not working as expected during batch update

bobbevers

Newbie

Points: 5
More actions
February 6, 2010 at 8:20 am

#229677

I have this trigger on a table that I am told is getting updated through batch updates.
ALTER TRIGGER [dbo].[UpdateLastComm]
ON [dbo].[RTD_Latest]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @mid varchar(13)
select @mid = deleted.MeterID from deleted
UPDATE MetersLastComm set DateStamp = GetDate() WHERE MeterID = @mid
END
The problem I am having is that although 10 -20 records are getting updated at a time, only 1 record gets updated in my other table. I am very new to this so can anybody help me out with what I'm doing wrong here.
Thanks in adbance
Bob

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

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

Bueyes SSC Eights! Points: 871 More actions · Answer 1

I think that you are using a single instruction to update the records, so the trigger is set only with one value, the trigger works well if you update one record at time.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

bobbevers (2/6/2010)
I have this trigger on a table that I am told is getting updated through batch updates.
ALTER TRIGGER [dbo].[UpdateLastComm]
ON [dbo].[RTD_Latest]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @mid varchar(13)
select @mid = deleted.MeterID from deleted
UPDATE MetersLastComm set DateStamp = GetDate() WHERE MeterID = @mid
END
The problem I am having is that although 10 -20 records are getting updated at a time, only 1 record gets updated in my other table. I am very new to this so can anybody help me out with what I'm doing wrong here.
Thanks in adbance
Bob

A trigger in SQL Server only fires once for any given UPDATE query no matter how many rows are affected. In other RDBMSs, such as Oracle, you have to use a FOR EACH ROW because the values in the trigger "tables" are only available one at a time. In SQL Server, all the values are available.

To make your trigger work for 0, 1, or more than 1 rows, you need to write your trigger code in a set based fashion sans any row value variables. Like this (if I read your code correctly)...

ALTER TRIGGER [dbo].[UpdateLastComm]

ON [dbo].[RTD_Latest]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

UPDATE dbo.MetersLastComm

SET DateStamp = GETDATE()

FROM dbo.MetersLastComm u

INNER JOIN Deleted d

WHERE u.MeterID = d.MeterID;

END

And, yes... SQL Server has not 1, but 2 from clauses available to it. The first one above is implicit and the second one is explicit.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)