Help with trigger

Question

Help with trigger

Viewing 4 posts - 16 through 18 (of 18 total)

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

Resender SSCertifiable Points: 7443 More actions · Answer 1

It might have worked for you for a while, but remember for others who haven't worked on it can be confusing if the code you give references to tables that don't exist or you don't keep using alternative names in you follow up posts.

CREATE TRIGGER trig_Consumables

ON ReceiveOrder

FOR UPDATE

AS

BEGIN

UPDATE Consumables SET Quantity = U.Quantity + I.q1 FROM Consumables U INNER JOIN ReceiveOrder I ON U.Product = I.Product

END

So now that we established that, you're saying that it stopped working.

Can you without revealing to much information a scenario when it went wrong

james.murphy SSC Enthusiast Points: 161 More actions · Answer 2

Hi

Sorted. I am using the table in an ASP.NET dynamic data site and there was a naming issue, so the original did work, just the asp.net code was wrong.

Thanks all.

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

Resender (7/5/2011)
It might have worked for you for a while, but remember for others who haven't worked on it can be confusing if the code you give references to tables that don't exist or you don't keep using alternative names in you follow up posts.
CREATE TRIGGER trig_Consumables
ON ReceiveOrder
FOR UPDATE
AS
BEGIN
UPDATE Consumables SET Quantity = U.Quantity + I.q1 FROM Consumables U INNER JOIN ReceiveOrder I ON U.Product = I.Product
END

Why did you replace the inserted table with the real table?

Now if someone does a single row update the ENTIRE of the Consumables table will be updated. I'm sure that's not intended behaviour.

Triggers that don't use inserted, deleted or both are in most cases incorrectly written. A trigger should only work on the rows that were changed, not the entire table.

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

james.murphy SSC Enthusiast Points: 161 More actions · Answer 4

The original statement was fine

UPDATE U SET Quantity = U.Quantity + Inserted.q1 FROM Consumables as U INNER JOIN Inserted ON U.Product = Inserted.Product

James