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:
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:
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.