Microsoft® SQL Server® 2012 Bible

900

Part VII: Monitoring and Auditing

To demonstrate the trigger, the following UPDATE attempts to change the quantity on

hand from 9 to 10. The new Inventory_Aggregate trigger traps the UPDATE and

resets the quantity on hand back to 9 , but it also writes a transaction of +1 to the

InventoryTransaction table. (If the InventoryTransaction table had transaction

type and comment columns, then the transaction would be recorded as a manual adjust-

ment by user X.) The InventoryTransaction table’s InvTrans_Aggregate trigger sees

the INSERT and properly adjusts the Inventory.QuantityOnHand to 10 :

-- Trigger Test

UPDATE Production.ProductInventory

SET Quantity = 10

WHERE ProductID = 707;

Having performed the manual adjustment, the following query examines the

InventoryTransaction table:

SELECT i.ProductID, it.Value

FROM Production.InventoryTransaction AS it

INNER JOIN Production.ProductInventory AS i

ON i.ProductID = it.InventoryID;

Sure enough, the manual adjustment of 1 has been written to the

InventoryTransaction table:

InventoryCode Value

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

A1 5

A1 -3

707 7

707 1

As the adjustment was being inserted into the InventoryTransaction table, the

InvTrans_Aggregate trigger posted the transaction to the Inventory table. The follow-

ing query double-checks the QuantityOnHand for inventory item 'A1':

SELECT p.ProductID, i.ProductID, i.Quantity

FROM Production.ProductInventory AS i

INNER JOIN Production.Product AS p

ON i.ProductID = p.ProductID

WHERE p.ProductID = 707;

Result:

ProductID ProductID Quantity

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

707 707 298

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

http://www.it-ebooks.info