Triggers and Transactions

When a trigger is defined, the action it specifies on the table to which it applies is always implicitly part of a transaction, along with the trigger itself.

Triggers are often used to roll back an entire transaction if an error is detected, or they can be used to roll back the effects of a specific data modification:
  • When the trigger contains the rollback transaction command, the rollback aborts the entire batch, and any subsequent statements in the batch are not executed.

  • When the trigger contains the rollback trigger, the rollback affects only the data modification that caused the trigger to fire. The rollback trigger command can include a raiserror statement. Subsequent statements in the batch are executed.

Since triggers execute as part of a transaction, the following statements and system procedures are not allowed in a trigger:
  • All create commands, including create database, create default, create index, create procedure, create rule, create table, create trigger, and create view

  • All drop commands

  • alter database and alter table

  • truncate table

  • grant and revoke

  • update statistics

  • sp_configure

  • load database and load transaction

  • disk init, disk refit, disk reinit, disk remirror, , disk unmirror

  • select into

If a desired result (such as a summary value) depends on the number of rows affected by a data modification, use @@rowcount to test for multirow data modifications (an insert, delete, or update based on a select statement), and take appropriate actions. Any Transact-SQL statement that does not return rows (such as an if statement) sets @@rowcount to 0, so the test of @@rowcount should occur at the beginning of the trigger.