Microsoft® SQL Server® 2012 Bible

899

Chapter 36: Creating Triggers

36

Result:

ProductID Shelf Quantity LocationID

707 N/A 297 7

The Inventory Trigger

The quantity values in the Inventory table should never be directly manipulated. Every

quantity adjustment must go through the InventoryTransaction table. However, some

users will want to make manual adjustments to the Inventory table. The gentlest solution

to the problem is to use server-side code to perform the correct operations regardless of the

user’s method:

1. An inventory INSTEAD OF trigger must redirect direct updates intended for the
   Inventory table, converting them into inserts in the InventoryTransaction
   table, while permitting the InvTrans_Aggregate trigger to update the
   Inventory table.


2. The inserts into the InventoryTransaction table then update the Inventory
   table, leaving the correct audit trail of inventory transactions.

As a best practice, the trigger must accept multiple-row updates. The goal is to undo the

original DML UPDATE command while keeping enough of the data to write the change as an

INSERT to the InventoryTransaction table:

CREATE TRIGGER Inventory_Aggregate

ON Production.ProductInventory

INSTEAD OF UPDATE

AS

-- Redirect direct updates

If Update(Quantity)

BEGIN;

UPDATE Production.ProductInventory

SET Quantity = d.Quantity

FROM Deleted AS d

INNER JOIN Production.ProductInventory AS i

ON i.ProductID = d.ProductID;

INSERT Production.InventoryTransaction

(Value, InventoryID)

SELECT

i.Quantity - Inv.Quantity,

Inv.ProductID

FROM Production.ProductInventory AS Inv

INNER JOIN Inserted AS i

ON Inv.ProductID = i.ProductID;

END;

c36.indd 899c36.indd 899 7/31/2012 10:03:10 AM7/31/2012 10:03:10 AM

http://www.it-ebooks.info