Microsoft SQL Server 2012 Bible

Transaction-Aggregation Handling

Triggers can maintain denormalized aggregate data.

A common example of this is an inventory system that records every individual transaction in an InventoryTransaction table, calculates the inventory quantity on hand, and stores the calculated quantity-on-hand in the Inventory table for performance.

Index views are another excellent solution to consider for maintaining aggregate data. They're documented in Chapter 45, “Indexing Strategies.”

To protect the integrity of the Inventory table, implement the following logic rules when using triggers:

• The quantity on hand in the Inventory table should not be updatable by any process other than the inventory transaction table triggers. Any attempt to directly update the Inventory table's quantity should be recorded as a manual adjustment in the InventoryTransaction table.
• Inserts in the InventoryTransaction table should write the current on-hand value to the Inventory table.
• The InventoryTransaction table should not allow updates. If an error is inserted into the InventoryTransaction table, an adjusting entry should be made to correct the error.

The AdventureWorks2012 database includes a simplified inventory system. To demonstrate transaction-aggregation handling, the following triggers implement the required rules. The first script creates a sample valid inventory item for test purposes:

USE AdventureWorks2012; ...