February 20, 2008 at 8:28 am
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
February 20, 2008 at 8:44 am
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
February 20, 2008 at 8:47 am
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
February 20, 2008 at 8:50 am
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
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 20, 2008 at 8:53 am
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)
February 20, 2008 at 10:48 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply