DML Trigger Planning Guidelines
There are two options when designing DML 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. AFTER triggers can be specified only on tables.
This table compares the functionality of the AFTER and INSTEAD OF triggers.
INSTEAD OF trigger
Tables and views
Quantity per table or view
Multiple per triggering action (UPDATE, DELETE, and INSERT)
One per triggering action (UPDATE, DELETE, and INSERT)
No restrictions apply
INSTEAD OF UPDATE and DELETE triggers are not allowed on tables that are targets of cascaded referential integrity constraints.
In place of:
Order of execution
First and last execution may be specified
varchar(max), nvarchar(max), and varbinary(max) column references in inserted and deleted tables
text, ntext, and image column references in inserted and deleted tables