How to rollback if any update is not success?

Question

I have written a transaction like:

    BEGIN TRAN

    UPDATE  [Table1]
    SET [Name] = 'abcd'
    WHERE   [ID] = 1

    UPDATE  [Table2]
    SET [Product] = 'efgh'
    WHERE   [ID] = 10

    UPDATE  [Table3]
    SET [Customar] = 'ijkl'
    WHERE   [ID] = 11

Now I want to rollback if any UPDATE is not success. For example in Table2 if there is no Product with ID=10 the transaction should be rolled back. How to do this?Please note that I am using SQLServer 2000.

Answer 1

SQL Server 2000. You don't need rollback if you use SET XACT_ABORT ON

SET XACT_ABORT ON --to ensure rollback
BEGIN TRAN -- @@TRANCOUNT + 1

UPDATE  [Table1]
SET [Name] = 'abcd'
WHERE   [ID] = 1
IF @@ROWCOUNT = 0 ROLLBACK TRAN

IF @@TRANCOUNT > 0
BEGIN
    UPDATE  [Table2]
    SET [Product] = 'efgh'
    WHERE   [ID] = 10

    IF @@ROWCOUNT = 0 ROLLBACK TRAN
END

IF @@TRANCOUNT > 0
BEGIN
    UPDATE  [Table3]
    SET [Customar] = 'ijkl'
    WHERE   [ID] = 11

    IF @@ROWCOUNT = 0 ROLLBACK TRAN
END

IF @@TRANCOUNT > 0 COMMIT TRAN

Answer 2

Before each UPDATE statement you need to do BEGIN TRAN and after each of your UPDATE statement, you need to do this -

if @@Error > 0
 THEN 
 BEGIN
   ROLLBACK TRAN
 END
Else
 BEGIN
   COMMIT TRAN
 END