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: Constraint processing Declarative referential actions inserted and deleted tables creation The triggering action	Before: Constraint processing In place of: The triggering action After: inserted and deleted tables creation
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