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.