Question

Write a trigger titled tgrExcessInventory for Production Inventory table to ensure the quantity can never exceed 800 units. Adventure Works 2016CTP

Then modify the trigger created in step 1 to execute its check code only if the quantity column is updated.

Answer 1

CREATE TRIGGER trgExcessInventory

On production.ProductInventory

FOR UPDATE

AS

IF EXISTS

(SELECT 'True'

FROM Inserted i

JOIN Deleted d

ON i.productID= d.ProductID

AND i.locationID=d.LocationID

WHERE (d.quantity+ i.quantity) >=800 OR

i.quantity >=800

)Begin

RAISERROR( 'Cant increase supply where units would be over 800 units',16,1)

ROLLBACK TRAN

END