When to Use Triggers

3 4

Triggers, like constraints, can be used to maintain data integrity and business rules, but a trigger should not replace a constraint where a constraint alone is sufficient. (Constraints are explained in Chapter 16.) For example, you don't need to create a trigger that checks for the existence of a value in a primary key column in one table to determine whether a value can be inserted into a corresponding column of another table—a FOREIGN KEY constraint would work best in this situation. However, you would create a trigger to cascade changes throughout related tables in the database. For example, you might create a DELETE trigger on the title_id column in the titles table of the pubs database that would delete rows in the sales, roysched, and titleauthor tables when a corresponding row in titles is deleted. (We'll see how to create this DELETE trigger in the next section.)

You can also use triggers to enforce more complex checks on data than are possible when you use the CHECK constraint. (CHECK constraints are explained in detail in Chapter 16.) This complexity is possible because triggers can reference columns in tables other than the table on which they are defined, whereas CHECK constraints are limited to a single table.

Triggers are also useful for performing multiple actions in response to one data modification event. If you create multiple triggers for a single type of data modification event, all of the triggers will fire when that event occurs. (Remember, if multiple triggers are defined on a table or view and for an event, each trigger must have a unique name.)

You can create a single trigger that will fire for multiple types of data modification events. The trigger will fire once each time an event for which it is defined occurs. Therefore, a trigger that is defined on a specified table or view for inserts, updates, and deletes will fire each time any of these events occurs on the table or view.

SQL Server creates two special, temporary tables when you create a trigger. You can reference these tables when you write the T-SQL code that composes the trigger definition. These tables are always in memory and are local to the trigger, and each trigger can access only its own temporary tables. A trigger's temporary tables are copies of the database table on which the trigger is defined. You can use the temporary tables to see the effect a data modification event had on the original table. You will see examples of these special tables (called the deleted and inserted tables) in the next section.