Designing Triggers

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:

•

Constraint processing

•

Declarative referential actions

•

•

Order of execution

inserted and deleted tables creation The triggering action

First and last execution may be specified

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

•

Constraint processing

In place of: •

The triggering action

After: •

inserted and deleted tables creation

Not applicable Allowed