Microsoft® SQL Server® 2012 Bible

898

Part VII: Monitoring and Auditing

AS

UPDATE Production.ProductInventory

SET Quantity += i.Value

FROM Production.ProductInventory AS Inv

INNER JOIN Inserted AS i

ON Inv.ProductID = i.InventoryID;

Return;

The next batch tests the InvTrans_Aggregate trigger by inserting a transaction and

observing the InventoryTransaction and Inventory tables:

INSERT Production.InventoryTransaction (InventoryID, Value)

SELECT ProductID, 5

FROM Production.ProductInventory

WHERE ProductID = 707;

INSERT Production.InventoryTransaction (InventoryID, Value)

SELECT ProductID, -3

FROM Production.ProductInventory

WHERE ProductID = 707;

INSERT Production.InventoryTransaction (InventoryID, Value)

SELECT ProductID, 7

FROM Production.ProductInventory

WHERE ProductID = 707;

The following query views the data within the InventoryTransaction table:

SELECT i.ProductID, it.Value

FROM Production.InventoryTransaction AS it

INNER JOIN Production.ProductInventory AS i

ON i.ProductID = it.InventoryID;

Result:

InventoryCode Value

--------------- ------

A1 5

A1 -3

A1 7

The InvTrans_Aggregate trigger should have maintained a correct quantity-on-hand

value through the inserts to the InventoryTransaction table. Indeed, the next query

proves the trigger functioned correctly:

SELECT P.ProductID, I.Shelf, I.Quantity, I.LocationID

FROM Production.ProductInventory AS I

INNER JOIN Production.Product AS P

ON I.ProductID = P.ProductID

WHERE P.ProductID = 707;

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

http://www.it-ebooks.info