Professional SQL Server™ 2005 Programming

13.2. Using Triggers for Data Integrity Rules

Although they shouldn't be your first option, triggers can also perform the same functionality as a CHECK constraint or even a DEFAULT. The answer to the question "Should I use triggers or CHECK constraints?" is the rather definitive: "It depends." If a CHECK can do the job, then it's probably the preferable choice. There are times, however, when a CHECK constraint just won't do the job, or when something inherent in the CHECK process makes it less desirable than a trigger. Examples of where you would want to use a trigger over a CHECK include:

• Your business rule needs to reference data in a separate table.

• Your business rule needs to check the delta (difference between before and after) of an update.

• You require a customized error message.

A summary table of when to use what type of data integrity mechanism is provided at the end of Chapter 5.

This really just scratches the surface of things. Since triggers are highly flexible, deciding when to use them really just comes down to whenever you need something special done.

13.2.1. Dealing with Requirements Sourced from Other Tables

CHECK constraints are great — fast and efficient — but they don't do everything you'd like them to. Perhaps the biggest shortcoming shows up when you need to verify data across tables.

To illustrate this, let's take a look at the Products and SalesOrderDetail tables in AdventureWorks as well as the related SpecialOfferProduct table. The relationship looks like ...