Trigger Enhancements in SQL Server 2000

3 4

SQL Server 2000 includes two new triggers: the INSTEAD OF trigger and the AFTER trigger. The INSTEAD OF trigger is executed instead of the triggering SQL statement. This overrides the action of the triggering statement. You can specify one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement. The INSTEAD OF trigger can be specified on a table, a view, or both. You can cascade INSTEAD OF triggers by defining views on views, where each view has a separate INSTEAD OF trigger. INSTEAD OF triggers are not allowed on updatable views that include the WITH CHECK option. Before you specify an INSTEAD OF trigger on one of these views, you must remove the WITH CHECK option from the updatable view by using the ALTER VIEW command. See Chapter 18 for more information on creating views.

The AFTER trigger is fired after all operations specified in the triggering SQL statement or statements have executed successfully. This includes all referential cascade actions and constraint checks. If you have multiple AFTER triggers defined on a table for a particular statement or set of statements, you can specify which trigger will fire first and which trigger will fire last. If you have more than two triggers defined, you can specify the firing order of only the first and last to fire. Any other triggers are fired randomly. You set this firing order via the sp_settriggerorder T-SQL statement.

In addition to providing new triggers, SQL Server 2000 allows you to specify triggers on views as well as tables. Previous versions of SQL Server allowed triggers to be specified on tables only. Triggers on views function exactly as do those defined on tables.