O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required