100 Days of SQL

Day 88 – Triggers

CREATE TRIGGER trUpdateProductInventory
ON ProductInventory
AFTER UPDATE
AS
BEGIN
    UPDATE Products
    SET UnitsInStock = UnitsInStock + (INSERTED.Quantity - DELETED.Quantity)
    FROM Products
    INNER JOIN INSERTED ON Products.ProductID = INSERTED.ProductID
    INNER JOIN DELETED ON Products.ProductID = DELETED.ProductID
END

In this example, a trigger named trUpdateProductInventory is created to update the UnitsInStock column in the Products table whenever the ProductInventory table is updated. The INSERTED and DELETED tables are used to retrieve the old and new values of the Quantity column in the ProductInventory table.