Listing 1: Code to Create a Stock-Management Trigger

CREATE TABLE dbo.orders (orderno INT, orderline INT, 
   sku INT, qty INT, custid INT)
GO
CREATE TABLE  dbo.stock (sku INT, qty INT)
GO
INSERT dbo.stock VALUES(1,10)
INSERT dbo.stock VALUES(2,10)
INSERT dbo.stock VALUES(3,10)
INSERT dbo.stock VALUES(4,10)
GO
INSERT dbo.orders VALUES (1,1,1,1,1)
INSERT dbo.orders VALUES (1,2,3,2,1)
INSERT dbo.orders VALUES (1,3,4,2,1)
INSERT dbo.orders VALUES (2,1,1,2,1)
INSERT dbo.orders VALUES (2,2,3,2,1)
GO
CREATE TRIGGER sls_stock_manager
ON dbo.orders
FOR UPDATE, DELETE AS
         /* Zero out stock decrements for updated or 
         deleted orderlines */
         UPDATE dbo.stock
            SET qty = stk.qty + upd.qty -- Old value
            FROM dbo.stock AS stk
            INNER JOIN DELETED AS upd
            ON upd.sku = stk.sku
         /* Set new stock increments */
         -- dbo.orders (orderno INT, orderline INT, sku INT,
         -- qty INT,custid INT)
         UPDATE dbo.stock
            SET qty = stk.qty - ord.qty -- New Value
            FROM dbo.stock AS stk
            INNER JOIN DELETED AS upd -- Use to pick up changes
            ON upd.sku = stk.sku
            INNER JOIN dbo.orders AS ord -- get the new value
            ON (upd.orderno = ord.orderno AND upd.orderline =
               ord.orderline)
GO
/* Play with the data. */
SELECT * FROM dbo.stock
BEGIN TRANSACTION
DELETE FROM dbo.orders WHERE orderno = 2
UPDATE dbo.orders SET qty = 3 WHERE orderno = 1 AND orderline = 3
COMMIT TRANSACTION
SELECT * FROM dbo.stock