Working with Triggers
A trigger is a special kind of stored procedure that runs when you modify data in a specified table using one or more of the data modification operations: UPDATE, INSERT, or DELETE. Special triggers known as INSTEAD OF triggers can specify modifications to a view's underlying base tables.
Triggers can query other tables and can include complex SQL statements. They are primarily useful for enforcing complex business rules or requirements. For example, you can control whether to allow a new order to be inserted based on a customer's current account status.
Triggers are also useful for enforcing referential integrity, which preserves the defined relationships between tables when you add, update, or delete the rows in those tables. However, the best way to enforce referential integrity is to define primary key and foreign key constraints in the related tables. If you use Table Designer, you can create a relationship between tables to automatically create a foreign key constraint. For details, see Table Relationships (Visual Database Tools).
Triggers are useful in these ways:
Triggers are automatic. They are activated immediately after any modification to the table's data, such as a manual entry or an application action.
Triggers can cascade changes through related tables in the database. For example, you can write a delete trigger on the title_id column of the titles table to cause a deletion of matching rows in other tables. The trigger uses the title_id column as a unique key to locate matching rows in the titleauthor, sales, and roysched tables.
Triggers can enforce restrictions that are more complex than those defined with Check Constraints (Visual Database Tools). Unlike check constraints, triggers can reference columns in other tables. For example, a trigger can roll back updates that attempt to apply a discount (stored in the discounts table) to books (stored in the titles table) with a price of less than $10.
For more information and examples of triggers, see the documentation for your database server. If you are using Microsoft SQL Server, see "CREATE TRIGGER" in SQL Server Books Online.
In This Section
Using INSTEAD OF Triggers On Views
How to: Show Trigger Properties