Professional SQL Server™ 2005 Programming

13.3. Other Common Uses for Triggers

In addition to the straight data integrity uses, triggers have a number of other uses. Indeed, the possibilities are fairly limitless, but here are a few common examples:

• Updating summary information

• Feeding de-normalized tables for reporting

• Setting condition flags

13.3.1. Updating Summary Information

Sometimes we like to keep aggregate information around to help with reporting or to speed performance when checking conditions.

Take, for instance, the example of a customer's credit limit versus their current balance. The limit is a fairly static thing and is easily stored with the rest of the customer information. The current balance is another matter. We can always figure out the current balance by running a query to total all of the unpaid balances for any orders the customer has, but think about that for a moment. Let's say that you work for Sears, and you do literally millions of transactions every year. Now think about how your table is going to have many millions of records for your query to sort through and that you're going to be competing with many other transactions in order to run your query. Things would perform an awful lot better if we could just go to a single place to get that total — but how to maintain it?

We certainly could just make sure that we always use a stored procedure for adding and paying order records, and then have the sproc update the customer's current balance. But that would mean that we would have to be sure ...