19 First Prep

CREATE TRIGGER tr_orders ON Orders AFTER UPDATE
AS
BEGIN TRANSACTION
BEGIN
DECLARE @oldStatus BIT =( SELECT Delivered FROM deleted)
DECLARE @newStatus BIT = (SELECT Delivered FROM inserted)
DECLARE @tranId INT = (SELECT OrderId FROM deleted)
DECLARE @stock INT = (SELECT p.StockQty FROM Orders AS o
JOIN OrderParts AS op ON op.OrderId = o.OrderId
JOIN Parts AS p ON p.PartId=op.PartId
WHERE o.OrderId = @tranId)
DECLARE @quantity INT = (SELECT op.Quantity FROM Orders AS o
JOIN OrderParts AS op ON op.OrderId = o.OrderId
JOIN Parts AS p ON p.PartId=op.PartId
WHERE o.OrderId = @tranId)
IF(@oldStatus = 0 AND @newStatus = 1)
BEGIN
UPDATE Parts
SET @stock +=@quantity
END
COMMIT
END