Triggers and 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

See Also