Share via

  • Article

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.

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