DML Trigger Planning Guidelines

  • Article

Microsoft SQL Server 2005 provides 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.

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

INSTEAD OF UPDATE and DELETE triggers 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

varchar(max), nvarchar(max), and varbinary(max) column references in inserted and deleted tables

Allowed

Allowed

text, ntext, and image column references in inserted and deleted tables

Not allowed

Allowed

See Also

Concepts

Using the inserted and deleted Tables

Help and Information

Getting SQL Server 2005 Assistance