[ How to rollback if any update is not success? ]
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