Update Trigger Executing on Multi Row Update 4

[RFEULINGatRITE]

Hello,

We have written an update trigger that we are having problems with.

This trigger was designed to check and see if a price change in an item table occurred and if so, put an entry into another table.

Problem is, another part of the program can update lots of prices and it does it with a single update statement so for the trigger to work we have to use a cursor and then loop through it to make an entry for each update.

Without the trigger, the query takes about 1 second for 1000 item price changes, with the trigger it takes about 4 minutes. Another HUGE problem is that it totally bogs SQL down, other programs connected to that database freeze. It's not that it uses a lot of processor power to perform the trigger, from the performance monitor its doing tons of disk transfers per second - it seems as though all this has to be done on the hard drive and not in memory for some reason...?

Am I making some common error? Is there a way to shift this burden onto ram so that the query can run faster?

Thanks for the help!!

 

[ESquared]

The best bet would be to rewrite the trigger so that it doesn't use a cursor at all. Would you show us your trigger code?

Another method that just occurred to me is that you could write it as an instead of-update trigger. Then run the cursor through just the inserted table, doing tweaks, then finally do the update all in one.

But from what you've described, I bet you can avoid the cursor entirely.

IF Updated(Price) INSERT INTO OtherTable
SELECT I.Key, NewPrice = I.Price, OldPrice = D.Price
FROM Inserted I INNER JOIN Deleted D ON I.Key = D.Key

 

[RFEULINGatRITE]

ESquared - your idea sounds like a winner if it applies.

Here it is, excuse my code if its dirty.

A few background things - RiteLabel is our table and we want an entry to go into it if a price change occurs or a sale start or end date changes.

ALTER trigger tr_itemUpdate
ON Item FOR UPDATE
AS

DECLARE @itemlookupcode varchar(30)
DECLARE @description varchar(30)
DECLARE @newprice money
DECLARE @newpriceA money
DECLARE @newpriceB money
DECLARE @newpriceC money
DECLARE @newsaleprice money
DECLARE @newstartdate datetime
DECLARE @oldprice money
DECLARE @newENDdate datetime
DECLARE @olddate datetime
DECLARE item_cursorupdate CURSOR

FOR SELECT ItemLookupCode, Description, Price, PriceA, PriceB, PriceC,
SalePrice,SaleStartDate,SaleEndDate FROM inserted
OPEN item_cursorupdate
FETCH NEXT FROM item_cursorupdate
INTO @itemlookupcode, @description, @newprice, @newpriceA, @newpriceB, @newpriceC,
@newsaleprice, @newstartdate, @newENDdate
WHILE @@FETCH_STATUS=0
BEGIN IF update(price)
BEGIN SELECT @oldprice=(SELECT Price FROM deleted WHERE
ItemLookupCode=@itemlookupcode)
IF(@newprice!=@oldprice)
BEGIN INSERT INTO RITELabel VALUES
(@itemlookupcode,@description,@newprice,'Regular',floor(convert(float,getdate())),convert(float,getdate(),108))
END
END
IF update(priceA)
BEGIN SELECT @oldprice=(SELECT PriceA FROM deleted WHERE
ItemLookupCode=@itemlookupcode)
IF(@newpriceA!=@oldprice)
BEGIN INSERT INTO RITELabel VALUES
(@itemlookupcode,@description,@newpriceA,'PriceA',floor(convert(float,getdate())),convert(float,getdate(),108))

END
END
IF update(priceB)
BEGIN SELECT @oldprice=(SELECT PriceB FROM deleted WHERE
ItemLookupCode=@itemlookupcode)
IF(@newpriceB!=@oldprice)
BEGIN INSERT INTO RITELabel VALUES
(@itemlookupcode,@description,@newpriceB,'PriceB',floor(convert(float,getdate())),convert(float,getdate(),108))

END
END
IF update(priceC)
BEGIN SELECT @oldprice=(SELECT priceC FROM deleted WHERE
itemlookupcode=@itemlookupcode)
IF(@newpriceC!=@oldprice)
BEGIN INSERT INTO RITELabel VALUES
(@itemlookupcode,@description,@newpriceC,'PriceC',floor(convert(float,getdate())),convert(float,getdate(),108))

END
END
IF update(saleprice)
BEGIN SELECT @oldprice=(SELECT saleprice FROM deleted WHERE
itemlookupcode=@itemlookupcode)
IF(@newsaleprice!=@oldprice)
BEGIN INSERT INTO RITELabel VALUES
(@itemlookupcode,@description,@newsaleprice,'Sale',floor(convert(float,getdate())),convert(float,getdate(),108))

END
Else IF update(salestartdate)
BEGIN SELECT @olddate=(SELECT salestartdate FROM deleted WHERE
itemlookupcode=@itemlookupcode)
IF(@newstartdate!=@olddate)
BEGIN INSERT INTO RITELabel VALUES
(@itemlookupcode,@description,@newsaleprice,'Sale',floor(convert(float,getdate())),convert(float,getdate(),108))

END
Else IF update(saleENDdate)
BEGIN SELECT @olddate=(SELECT saleENDdate FROM deleted WHERE
itemlookupcode=@itemlookupcode)
IF(@newENDdate!=@olddate)
BEGIN INSERT INTO RITELabel VALUES
(@itemlookupcode,@description,@newsaleprice,'Sale',floor(convert(float,getdate())),convert(float,getdate(),108))

END END
END
END
FETCH NEXT FROM item_cursorupdate INTO @itemlookupcode, @description, @newprice,
@newpriceA, @newpriceB, @newpriceC, @newsaleprice,@newstartdate,@newENDdate

END close item_cursorupdate
deallocate item_cursorupdate

 

[RFEULINGatRITE]

It also appears that you may have misunderstood me a bit Esquared.

I still need the update to occur to the table; I just want to know more about when it happened and such. It’s so that we can print labels for all price changes that occurred in a specific period in time.

If I could, I'd change the source code of the program that performs the price changes so that this would be unnecessary- unfortunately, our product only integrates with it so that info is not available to me.

 

[miq]

HI,
Threre is no need to use cursors even no need to specifically program trigger to handle multi rows because it handles them quite easily usually without additinal code.

Esquard code should work for your update trigger.

IF Updated(PriceA) INSERT INTO RITElabel
SELECT I.itemlookupcode, NewPrice = I.PriceA, OldPrice = D.PriceA
FROM Inserted I INNER JOIN Deleted D ON I.itemlookupcode = D.itemlookupcode

B.R,
miq

 

[ESquared]

Using my code in a regular trigger will still allow the update... of course you still want the update to run.

As for the instead-of-update trigger suggestion, as in any trigger of this type you'd have to perform the update yourself. The idea was that if you absolutely had to use a cursor, you could process through the inserted/deleted tables instead of through your actual physical tables.

 

[hawkdernacht]

On behalf of RFEULINGatRITE, I would like to thank both miq and ESquared. A modified version of your ideas has moved our trigger from 10 minutes to 4 seconds. Thank you both very much.

Regaurds

The Nighthawk,
"Don't try to reinvent the wheel, unless you can make it better.

 

[RFEULINGatRITE]

Hats off people! I owe you both one!

 

[ESquared]

RFEULINGatRITE,

I followed your logic as best I could, and made a single query version for you just for fun. Does this work? I assumed you wanted just the time value in the last expression.

ALTER trigger tr_itemUpdate ON Item FOR UPDATE AS

IF Update(Price) OR IF Update(PriceA) OR IF Update(PriceB) OR IF Update(PriceC) OR IF Update(SalePrice) OR IF Update(salestartdate) OR IF Update(Saleenddate)
INSERT RITELabel
SELECT
  I.ItemLookupCode,
  I.Description
  N.Type,
  ThePrice = 
    CASE N.Num
    WHEN 1 THEN I.Price
    WHEN 2 THEN I.PriceA
    WHEN 3 THEN I.PriceB
    WHEN 4 THEN I.PriceC
    WHEN 5 THEN I.SalePrice
    END
  TheDate = DateAdd(d, DateDiff(d, 0, GetDate()), 0),
  TheTime = GetDate() - DateAdd(d, DateDiff(d, 0, GetDate()), 0)
FROM
  Inserted I INNER JOIN Deleted D ON I.ItemLookupCode = D.ItemLookupCode
  INNER JOIN (
    SELECT Num = 1, Type = 'Regular' UNION
    SELECT Num = 2, Type = 'PriceA' UNION
    SELECT Num = 3, Type = 'PriceB' UNION
    SELECT Num = 4, Type = 'PriceC' UNION
    SELECT Num = 5, Type = 'Sale'
  ) N ON 
    CASE N.Num WHEN 1 THEN I.Price WHEN 2 THEN I.PriceA WHEN 3 THEN I.PriceB WHEN 4 THEN I.PriceC WHEN 5 THEN I.SalePrice END
    <>
    CASE N.Num WHEN 1 THEN D.Price WHEN 2 THEN D.PriceA WHEN 3 THEN D.PriceB WHEN 4 THEN D.PriceC WHEN 5 THEN D.SalePrice END
    OR (N.Num = 5 AND (I.salestartdate <> D.salestartdate OR I.saleenddate <> D.saleenddate))

Note that if any of these columns can contain Null values, the change from Null to a value will not be captured.

Also note that my previous IF Updated() was incorrect, it's IF Update().

Oh, and this probably would be more straightforward if I'd just used a bunch of unions, but this was more fun.