Triggers Compared to Other Data Integrity Methods

Microsoft® SQL Server™ provides two primary mechanisms for enforcing business rules and data integrity: constraints and triggers. Each has benefits that make them useful in special situations. The primary benefit of triggers is that they can contain complex processing logic that uses Transact-SQL code. Therefore, triggers can support all of the functionality of constraints; however, triggers are not always the best method for a given feature.

Entity integrity should always be enforced at the lowest level by indexes that are part of PRIMARY KEY and UNIQUE constraints or are created independently of constraints. Domain integrity should be enforced through CHECK constraints, and referential integrity should be enforced through FOREIGN KEY constraints, assuming their features meet the functional needs of the application.

Triggers are most useful when the features supported by constraints cannot meet the functional needs of the application. For example:

• FOREIGN KEY constraints do not support cascading activities, and they can validate a column value only with an exact match to a value in another column. If your application requires that a delete from one table will also result in a delete of related data in another table, or if the referential integrity requires something other than an exact match, you must use a trigger.
• A CHECK constraint can validate a column value only against a logical expression or another column in the same table. If your application requires that a column value be validated against a column in another table, you must use a trigger.
• Constraints can communicate about errors only through standardized system error messages. If your application requires (or can benefit from) customized messages and more complex error handling, you must use a trigger.

See Also