Using Triggers to Define Business Processes

A trigger is a special kind of stored procedure that goes into effect when you modify data in a specified table. Triggers are often created to enforce business rule consistency among logically related data in different tables.

An advantage of triggers is that they are automatic ¾ they work no matter what caused the data modification ¾ a clerk's entry or an application action. Each trigger is specific to one or more of the data modification operations UPDATE, INSERT, or DELETE. The trigger is executed once per SQL statement; it "fires" immediately after the data modification statements are completed. The trigger and the statement that fires it are treated as a single transaction that can be rolled back from within the trigger. If a severe error is detected, the entire transaction automatically rolls back.

Triggers are useful in these ways:

• Triggers can cascade changes through related tables in the database.

  For example, a delete trigger on the title_id column of the titles table causes a corresponding deletion of matching rows in other tables, using the title_id column as a unique key to locating rows in titleauthor, sales, and roysched.

• Triggers can disallow or roll back changes that violate referential integrity, canceling the attempted data modification transaction.

  Such a trigger might go into effect when you try to 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, declarative referential integrity (DRI) is usually used for this purpose.)

• 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 roll back updates that attempt to increase a book's price by more than 1 percent of its advance, might be checked with a SELECT to another table.

• Triggers can also find the difference between the state of a table before and after a data modification and take action(s) based on that difference.

Note Except for the deltitle trigger, the triggers discussed in this chapter are not part of the pubs database included with SQL Server. To work with the examples shown, you must create each trigger by using the CREATE TRIGGER statement. Note that each new trigger for the same operation (INSERT, UPDATE, or DELETE) on a table or column overwrites the previous one without warning. For details about CREATE TRIGGER, see the CREATE TRIGGER statement in the Microsoft SQL Server Transact-SQL Reference.