DML Trigger Execution
AFTER triggers are never executed if a constraint violation occurs; therefore, these triggers cannot be used for any processing that might prevent constraint violations.
INSTEAD OF triggers are executed instead of the triggering action. These triggers are executed after the inserted and deleted tables reflecting the changes to the base table are created, but before any other actions are taken. They are executed before any constraints, so can perform preprocessing that supplements the constraint actions.
If an INSTEAD OF trigger defined on a table executes a statement against the table that would usually fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. For example, if a DML trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.
If an INSTEAD OF trigger defined on a view executes a statement against the view that would usually fire the INSTEAD OF trigger again, it is not called recursively. Instead, the statement is resolved as modifications against the base tables underlying the view. In this case, the view definition must meet all of the restrictions for an updatable view. For a definition of updatable views, see Modifying Data Through a View. For example, if a DML trigger is defined as an INSTEAD OF UPDATE trigger for a view, and the trigger executes an UPDATE statement referencing the same view, the UPDATE statement executed by the INSTEAD OF trigger does not call the trigger again. The UPDATE executed by the trigger is processed against the view as if the view did not have an INSTEAD OF trigger. The columns changed by the UPDATE must be resolved to a single base table. Each modification to an underlying base table starts the chain of applying constraints and firing AFTER triggers defined for the table.
DML trigger performance overhead is usually low. The time involved in running a DML trigger is spent mostly in referencing other tables, which can be either in memory or on the database device. The deleted and inserted tables are always in memory. The location of other tables referenced by the trigger determines the amount of time the operation requires.
Note
The use of cursors in DML triggers is not recommended because of the potentially negative impact on performance. Use rowset-based logic rather than cursors to design a DML trigger that affects multiple rows.
DML Trigger Execution and Partitioned Views
In SQL Server 2005, the way UPDATE and DELETE triggers fire on tables that underlie a partitioned view has changed from the way they fire in SQL Server 2000. In SQL Server 2000, when an UPDATE or DELETE statement is issued against a partitioned view, either local or distributed, any UPDATE or DELETE triggers that are defined on the base tables of the view fire. These include triggers on tables that are not affected by the update or delete operation. In SQL Server 2005, an UPDATE or DELETE trigger fires only if the base table on which the trigger is defined is affected by the update or delete operation. This behavior is the same for both AFTER and INSTEAD OF triggers.
For example, consider a partitioned view PV that consists of fields from tables T1 and T2. Additionally, both T1 and T2 have UPDATE triggers defined on them. In SQL Server 2000, an UPDATE statement issued against PV that only affects rows in T1 causes the UPDATE triggers on both T1 and T2 to fire. In SQL Server 2005, only the UPDATE trigger that is defined on T1 fires.
The following tables summarize the behavior changes.
Insert, Update, or Delete Operation on a Local Partitioned View
| SQL Server 2000 | SQL Server 2005 | |
|---|---|---|
INSERT TRIGGER |
Fires only on affected tables. |
Fires only on affected tables. |
UPDATE TRIGGER |
Fires for all tables underlying the partitioned view on which this kind of trigger is defined. |
Fires only on affected tables. |
DELETE TRIGGER |
Fires for all tables underlying the partitioned view on which this kind of trigger is defined. |
Fires only on affected tables. |
Insert, Update, or Delete Operation on a Distributed Partitioned View
| SQL Server 2000 | SQL Server 2005 | |
|---|---|---|
INSERT TRIGGER |
Fires only on affected tables of the distributed partitioned view. Triggers that are defined on tables on the local instance and the remote instance can fire, if the tables are affected. |
Fires only on affected tables of the distributed partitioned view. Triggers that are defined on tables on the local instance and the remote instance can fire, if the tables are affected. |
UPDATE TRIGGER |
Fires for all tables that are underlying the partitioned view on which this kind of trigger is defined. |
Fires for all tables on the remote instance on which this kind of trigger is defined. Fires only on affected tables on the local instance. |
DELETE TRIGGER |
Fires for all tables underlying the partitioned view on which this kind of trigger is defined. |
Fires for all tables on the remote instance on which this kind of trigger is defined. Fires only on affected tables on the local instance. |
See Also
Concepts
Using the inserted and deleted Tables
Cascading Referential Integrity Constraints
Using Nested Triggers
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
5 December 2005 |
|