Most SQL Server runtime errors DO NOT rollback transaction. Solution.

Many developers mistakenly believe that any error inside a transaction will make the transaction fail and automatically rollback. However, this is true only for fatal errors, such as most errors with severity equal to 16 or higher. The commonest runtime error - for example duplicate primary keys, violation of unique constraints and foreign key relationship - aren't fatal and therefore won't rollback the transaction.

To rollback a transaction when an error occurs you must manually check each statement for runtime error using the @@ERROR global variable, as in the following code snippet:

BEGIN TRANSACTION
INSERT authors VALUES (…)
IF (@@ERROR <> 0) GOTO on_error
INSERT publishers VALUES (…)
IF (@@ERROR <> 0) GOTO on_error
COMMIT TRANSACTION
GOTO continue

on_error:
ROLLBACK TRANSACTION

continue:
.....

If the transaction is inside a stored procedure, you might use the procedure's return value to signal success or failure to the caller:

CREATE PROC RunThis AS

BEGIN TRANSACTION

INSERT authors VALUES (...)
IF (@@ERROR <> 0) GOTO on_error
INSERT publishers VALUES (...)
IF (@@ERROR <> 0) GOTO on_error

COMMIT TRANSACTION
-- return 0 to signal success
RETURN (0)

on_error:
ROLLBACK TRANSACTION
-- return 1 to signal failure
RETURN (1)

NOTE: As an alternative, you can use the SET XACT_ABORT ON command, which causes transactions to fail when any runtime error occurs, not just fatal ones. In this case you don't need to test each statement using @ERROR.