A trigger is a special type of stored procedure that is automatically invoked whenever the data in the table is modified. Triggers are invoked in response to INSERT, UPDATE, or DELETE statements. 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 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:
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 locate rows in titleauthor, sales, and roysched.
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.)
Unlike CHECK constraints, triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to roll back updates that attempt to increase a book’s price by more than one percent of its advance.