Enforcing Business Rules with Triggers

Microsoft® SQL Server™ 2000 provides two primary mechanisms for enforcing business rules and data integrity: constraints and triggers. A trigger is a special type of stored procedure that automatically takes effect when the data in a specified table is modified. A trigger is invoked in response to an INSERT, UPDATE, or DELETE statement. A trigger can query other tables and can include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.

Triggers are useful in these ways:

• Triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints.
  
  
• Triggers can enforce restrictions that are more complex than those defined with CHECK constraints.

  Unlike CHECK constraints, triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to compare to the inserted or updated data and to perform additional actions, such as modify the data or display a user-defined error message.

• Triggers can also evaluate the state of a table before and after a data modification and take action(s) based on that difference.
  
  
• Multiple triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to the same modification statement.

Triggers Compared to Constraints

Constraints and triggers each have 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 (RI) 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 can validate a column value only with an exact match to a value in another column, unless the REFERENCES clause defines a cascading referential action.
  
  
• 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.

Triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently through cascading referential integrity constraints.

• Triggers can disallow or roll back changes that violate referential integrity, thereby canceling the attempted data modification. Such a trigger might go into effect when you change a foreign key and the new value does not match its primary key. For example, you can create an insert trigger on titleauthor.title_id that rolls back an insert if the new value does not match some value in titles.title_id. However, FOREIGN KEY constraints are usually used for this purpose.
  
  
• If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution but prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed.

See Also

Cascading Referential Integrity Constraints

CHECK Constraints

Data Integrity

Stored Procedures

Using Constraints, Defaults, and Null Values