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 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; ...