Benefits and Uses of Triggers

Triggers are powerful objects for maintaining database integrity because the triggers can evaluate data before it has been committed to the database. While the triggers are executing, they can perform a myriad of actions, including the following:

• Compare before and after versions of data

• Roll back invalid modifications

• Read from other tables including those in other databases

• Modify other tables including those in other databases

• Execute local and remote stored procedures

Declarative referential integrity (DRI) was introduced in version 6.0 of SQL Server. DRI is established via foreign keys that are declared as part of the table definition. These foreign keys specify columns in one table that are automatically validated against primary keys or unique index values in another table. This validation is referred to as referential integrity ( RI ), which ensures that proper relationships between tables are enforced.

In previous releases, RI had to be performed by the client application through stored procedures or triggers. No declarative mechanism such as foreign keys was available, but triggers could provide the same type of functionality. Because of this, the vast majority of triggers written over the years were written to perform referential integrity checks.

Because DRI is available now, triggers generally handle more complex integrity concepts, restrictions that cannot be handled through datatypes, constraints, defaults, or rules. Following are some examples of trigger uses:

• Maintenance of duplicate and derived data ”A denormalized database generally introduces data duplications (redundancy). Instead of exposing this redundancy to end users and programmers, you can keep the data in sync through triggers. If the derived data is allowed to be out of sync, you might want to consider handling the refresh through batch processing or some other method instead.

• Complex column constraints ”If a column constraint depends on other rows within the same table or rows in other tables, a trigger is the best method for that column constraint.

• Cascading referential integrity ”You can use triggers to implement actions that maintain referential integrity. This includes cascading actions and the like.

• Complex defaults ”You can use a trigger to generate default values based on data in other columns, rows, or tables.

• Inter-database referential integrity ”When related tables are found in two different databases, triggers can be used to ensure referential integrity across the databases.

You can use stored procedures for all of these tasks , but the advantage of using triggers is that they can fire on all data modifications. Stored procedure code or SQL in application code is only executed when it makes the data modifications. With triggers, all data modifications are subject to the trigger code, except for bulk copy and a few other non-logged actions. Even if a user utilizes an ad hoc tool, such as Query Analyzer, the integrity rules cannot be bypassed after the trigger is in place.

NOTE

Triggers and stored procedures are not mutually exclusive. You can have both a trigger and stored procedures that perform modifications and validation on that same table. If desired, you can perform some of the aforementioned tasks via triggers and other tasks via stored procedures.