June 21, 2011 at 12:59 pm
Hi
I have come up with this
ALTER TRIGGER Trigger6
ON dbo.ReceiveOrder
FOR UPDATE
AS
UPDATE Consumables
SET Quantity = Consumables.Quantity + ReceiveOrder.Quantity_Received
FROM Consumables INNER JOIN
ReceiveOrder ON Consumables.Product = ReceiveOrder.Product
This almost does what I want. I have Quantity_Received=25, Quantity=100 but when it triggers I get the new Quantity=150 any ideas.
Thanks
June 21, 2011 at 1:08 pm
Why is the trigger not using the deleted/inserted tables? As written it's going to affect the entire table if one row is changed.
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
June 22, 2011 at 1:41 am
Well I'm new to SQL. I'll check out inserted tables.
James
June 22, 2011 at 9:07 am
You have to be very careful with this type of trigger. It's an UPDATE only trigger, which means if someone updates the date received field by itself in the ReceiveOrder table then the quantity received will be added to the consumables again. Every time a value, any value, is changed, the quantity will be added again.
You probably need a trigger like this, and then figure out what logic you need on an update.
CREATE TRIGGER trg_ReceiveOrder_Update_ConsQty
ON dbo.ReceiveOrder
FOR INSERT
AS
UPDATE c
SET c.Quantity = isnull(c.Quantity,0) + i.Quantity_Received
FROM Consumables c
INNER JOIN inserted i ON c.Product = i.Product
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply