SQL INSERT TRIGGER only updating first record inserted

Question

Post reply

SQL INSERT TRIGGER only updating first record inserted

awells-648404

SSC Veteran

Points: 263
More actions
February 20, 2008 at 8:28 am

#184104

I have an INSERT, UPDATE trigger on a table which populates two calculated fields for that table.
I have a sp that inserts on average 400+ records per batch. The INSERT trigger is only firing for the FIRST record inserted.
Any help is much appreciated. (Code Below)
Thanks
Anthony
THIS IS MY INSERT:
INSERT INTO xGradingDetail (DBID, ID, GradingMasterID, StockNo, BundleNo, Sheets, Length, Width, PalletNo, Price)
SELECT DBID, ID, SessionID, StockNumber, BundleNumber, BundleSheets, BundleLength, BundleWidth, PalletNumber, Price
FROM vw_Grading_MeasurementDetail
THIS IS MY TRIGGER CODE ON THE DESTINATION TABLE
If Update(Sheets) Or Update([Length]) Or Update(Width)
BEGIN
SELECT @ID = ID, @Sheets = Sheets, @Length = [Length], @Width = Width FROM Inserted
SELECT @SqM = dbo.fnBundleSqM(@Sheets, @Length, 13,@Width,13)
SELECT @SqFt = dbo.fnBundleSqFt(@Sheets, @Length, 13,@Width,13)
UPDATE xGradingDetail SET SqFt = @SqFt, SqM = @SqM
WHERE ID = @ID
END

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

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

dragos_sv Default port Points: 1478 More actions · Answer 1

hi

that is because the trigger, as you wrote it, deals with only one record.

try rewriting it using joins

if you do not figure it out, i'll try to help later this evening

regards,

dragos

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

Triggers fire once per statement - not once per row affected.

You need to rewrite your trigger so it can deal with more than one row per statement.

/Kenneth

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 3

As mentioned above, you've written the statement to deal with one row.

You cannot use local variables for the values or you only get them for one row. What you really want to do is something like

UPDATE xGradingDetail

from inserted i

SET SqFt = dbo.fnBundleSqFt(i.Sheets, i.Length, 13,i.Width,13)

WHERE ID = i.ID

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 4

awells (2/20/2008)
I have an INSERT, UPDATE trigger on a table which populates two calculated fields for that table.
I have a sp that inserts on average 400+ records per batch. The INSERT trigger is only firing for the FIRST record inserted.
Any help is much appreciated. (Code Below)
Thanks
Anthony
THIS IS MY INSERT:
INSERT INTO xGradingDetail (DBID, ID, GradingMasterID, StockNo, BundleNo, Sheets, Length, Width, PalletNo, Price)
SELECT DBID, ID, SessionID, StockNumber, BundleNumber, BundleSheets, BundleLength, BundleWidth, PalletNumber, Price
FROM vw_Grading_MeasurementDetail
THIS IS MY TRIGGER CODE ON THE DESTINATION TABLE
If Update(Sheets) Or Update([Length]) Or Update(Width)
BEGIN
SELECT @ID = ID, @Sheets = Sheets, @Length = [Length], @Width = Width FROM Inserted
SELECT @SqM = dbo.fnBundleSqM(@Sheets, @Length, 13,@Width,13)
SELECT @SqFt = dbo.fnBundleSqFt(@Sheets, @Length, 13,@Width,13)
UPDATE xGradingDetail SET SqFt = @SqFt, SqM = @SqM
WHERE ID = @ID
END

You have a couple of problems. First, you're assigning @ID, @Sheets, etc. but since there are multiple rows being inserted/updated, you're only getting one value for @ID, @Sheets, etc and thus only updating one row. Second, you should be using the inserted pseudo-table which contains the records that were inserted/updated.

If Update(Sheets) Or Update([Length]) Or Update(Width)

BEGIN

UPDATE xGradingDetail

set SqFt = dbo.fnBundleSqM(i.Sheets, i.Length, 13,i.Width,13),

SqM = dbo.fnBundleSqFt(i.Sheets, i.Length, 13,i.Width,13)

from xGradingDetail join inserted as i on i.ID = xGradingDetail.ID

END

I don't know how complex dbo.fnBundleSqM()/Ft() are, but you could eliminate the trigger altogether by defining .SqFt and .SqM as computed columns on xGradingDetail.

alter table xGradingDetail drop column SqFt

-- compute with actual formula

alter table xGradingDetail add SqFt as Sheets * Length * Width ...

-- or compute with function

alter table xGradingDetail add SqFt as dbo.fnBundleSqM(Sheets, Length, 13,Width,13)

awells-648404 SSC Veteran Points: 263 More actions · Answer 5

All suggestions would work perfectly. I am investigating the calculated column approach as well. The SqFt and SqMeter functions are not intensive in the least so it is definitely an alternate solution.

Thanks to all of you.