Using DML Triggers That Include COMMIT or ROLLBACK TRANSACTION
In Microsoft SQL Server 2000 and SQL Server 2005, the transaction count is incremented within a statement only when the transaction count is 0 at the start of the statement. In SQL Server version 7.0, the transaction count is always incremented, regardless of the transaction count at the start of the statement. Therefore, the value that @@TRANCOUNT returns in triggers may be lower in SQL Server 2000 and SQL Server 2005 than it is in SQL Server 7.0.
In SQL Server 2000 and SQL Server 2005, if a COMMIT TRANSACTION or COMMIT WORK statement is executed in a trigger, and there is no corresponding explicit or implicit BEGIN TRANSACTION statement at the start of the trigger, users might see different behavior than in SQL Server version 7.0. We do not recommend placing COMMIT TRANSACTION or COMMIT WORK statements in a trigger.
When triggers that include COMMIT or ROLLBACK TRANSACTION statements are executed from a batch, they cancel the whole batch. In SQL Server 2005, an error is also returned.
In the following example, if the INSERT statement fires a DML trigger that includes ROLLBACK TRANSACTION, the DELETE statement is not executed because the batch is canceled.
/* Start of Batch */
INSERT employee VALUES ('XYZ12345M', 'New', 'M', 'Employee', 1, 1, '9952', '6/1/95') -- Causes trigger to fire and ROLLBACK TRANSACTION.
DELETE employee WHERE emp_id = 'PMA42628M'
GO
If triggers that include ROLLBACK TRANSACTION statements are fired from within a user-defined transaction, ROLLBACK TRANSACTION rolls back the whole transaction. In the following example, if the INSERT statement fires a trigger that includes ROLLBACK TRANSACTION, the UPDATE statement is also rolled back:
/* Start of Transaction */
BEGIN TRANSACTION
UPDATE employee SET hire_date = '7/1/94' WHERE emp_id = 'VPA30890F'
INSERT employee VALUES ('XYZ12345M', 'New', 'M', 'Employee', 1, 1, '9952', '6/1/95') -- Causes trigger to fire and ROLLBACK TRANSACTION.
See Also
Concepts
Rollbacks and Commits in Stored Procedures and Triggers
Transactions (Database Engine)
Other Resources
ROLLBACK TRANSACTION (Transact-SQL)