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:
INSERT employee VALUES ('XYZ12345M', 'New', 'M', 'Employee', 1, 1, '9952', '6/1/95')
DELETE employee WHERE emp_id = 'PMA42628M'
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:
BEGIN TRAN
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')
For more information about user-defined transactions, see Transactions in the Microsoft SQL Server Transact-SQL Reference.