Proper way to use a transaction around multiple inserts or updates

Question

What is the proper way to test for insert/update failures and rollback this transaction if there are any? I don't think what I have will work since my inserts/updates are 3 separate statements and the @@ROWCOUNT will only reflect the last statement executed.

BEGIN TRANSACTION Script;
GO

INSERT INTO TableA (id) VALUES (1)
INSERT INTO TableB (id) VALUES (1)
UPDATE TableC SET id=1 WHERE id=2
GO

IF (@@ROWCOUNT=3 AND @@ERROR=0)
    BEGIN
    COMMIT
    END
ELSE
    BEGIN
    PRINT 'Error: Rolling back transaction'
    ROLLBACK TRANSACTION Script
    END
GO

Answer 1

If you put SET XACT_ABORT ON before you start transaction, in case of an error, rollback will be issued automatically.

SET XACT_ABORT ON

begin transaction

INSERT INTO TableA (id) VALUES (1)
INSERT INTO TableB (id) VALUES (1)
UPDATE TableC SET id=1 WHERE id=2

commit transaction

If you want to do rollback yourself, use try .. catch block.

begin transaction

begin try

  INSERT INTO TableA (id) VALUES (1)
  INSERT INTO TableB (id) VALUES (1)
  UPDATE TableC SET id=1 WHERE id=2

  commit transaction

end try

begin catch
  raiserror('Message here', 16, 1)
  rollback transaction
end catch

Answer 2

I don't know what version you're on, but there has been try/catch since SQL 2005:

begin transaction
begin try
   INSERT INTO TableA (id) VALUES (1)
   INSERT INTO TableB (id) VALUES (1)
   UPDATE TableC SET id=1 WHERE id=2
end try
begin catch
   SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
   while(@@trancount > 0)
   begin
      rollback transaction
   end
end catch
if (@@trancount <> 0)
begin
   commit transaction;
end