Using Triggers that Include ROLLBACK TRANSACTION
From Creating and Maintaining Databases
Using Triggers that Include ROLLBACK TRANSACTION
When triggers that include ROLLBACK TRANSACTION statements are executed from a batch, they cancel the entire batch. In the following example, if the INSERT statement fires a trigger that includes a 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, the ROLLBACK TRANSACTION rolls back the entire transaction. In this example, if the INSERT statement fires a trigger that includes a 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