MS SQL Basics Tutorial: INSTEAD of Triggers

In the fifth section of this chapter, we have noted that SQL Server 2000 supports after and instead of two types of triggers. Where the instead OF triggers is a newly added feature of SQL Server 2000, after triggers are equivalent to triggers in previous versions. When a instead type trigger for an action (INSERT, DELETE, UPDATE) is defined for a table or view and the appropriate action is performed, the trigger SQL statement itself is run, although the trigger is triggered but the corresponding action is not executed.

The main advantage of the INSTEAD of triggers is that views that cannot be modified can support modifications. Typical examples are split views (partitioned view). To improve query performance, a split view is usually a result set from more than one table, but it is also not supported for view updates. The following example shows how to use the instead OF triggers to support modifications to the base table referenced by the split view.

The INSERT statement can be successfully executed at this point insert into saleview values (' jul001 ', ' First Division VCD ', 200). Another advantage of the INSTEAD of triggers is that the rest of the execution is discarded by using a logical statement to perform a portion of the batch. For example, you can define a trigger to perform another part of the trigger when it encounters an error.

See the full set of "MS SQL Basics Tutorials"