Creating and Maintaining Databases
Designing Triggers
Microsoft® SQL Server™ 2000 provides two options when designing triggers:
- INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.
- AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server. AFTER triggers can be specified only on tables.
This table compares the functionality of the AFTER and INSTEAD OF triggers.
| Function | AFTER trigger | INSTEAD OF trigger |
|---|---|---|
| Applicability | Tables | Tables and views |
| Quantity per table or view | Multiple per triggering action (UPDATE, DELETE, and INSERT) | One per triggering action (UPDATE, DELETE, and INSERT) |
| Cascading references | No restrictions apply | Are not allowed on tables that are targets of cascaded referential integrity constraints. |
| Execution | After:
|
Before:
In place of:
After:
|
| Order of execution | First and last execution may be specified | Not applicable |
| text, ntext, and image column references in inserted and deleted tables | Not allowed | Allowed |