SQL rollback transaction

Question

I'm trying to delete a data in a tables

BEGIN TRAN 

DELETE FROM TABLEA
DELETE FROM TABLEB

ROLLBACK TRAN 

But when I check the data in the table, the data do exist.

Solution 1

If you are rollbacking a transaction, you are cancelling the uncomitted changes. If you open a transaction, delete, and rollback, the data will exists.

If you want to commit the transaction (delete the info), then do

BEGIN TRAN 

DELETE FROM TABLEA
DELETE FROM TABLEB

COMMIT TRAN 

Solution 2

Here are some explanation about TRANSACTION in SQL Server

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

Also bear in mind that using TRANSACTION temporarily locks the table so you must be careful.

 BEGIN TRAN 
    Statement1
    Statement2
 COMMIT TRAN

If one of the statement fails or have some error the transaction will rollback and ignoring all the statement and there will be no changes

When you use ROLLBACK TRAN it erase the all data modification made from the start of the transaction use COMMIT TRAN instead

Solution 3

replace

ROLLBACK TRAN 

with

COMMIT TRAN 

and the data will be deleted. rollback tran means canceling all changes made in the transaction.

Solution 4

BEGIN TRY
BEGIN TRAN   

DELETE FROM TABLEA 

DELETE FROM TABLEB  

COMMIT TRAN  
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

 IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
END CATCH

If you have more than one action in a transaction, you should always use a try-catch block to rollback everything is one part of the transaction fails.

Solution 5

--As a summary

BEGIN TRAN 

DELETE FROM TABLEA
DELETE FROM TABLEB

ROLLBACK/COMMIT TRAN

--ROLLBACK: in the name rollback, it will cancel the process.

--COMMIT: in the name commit, it will confirm the process.

View more solutions

Comments

[marc_s]

Ahem..... well what did you expect ? You're deleting from two tables, in the context of a transaction. You then roll back that transaction - this means: all the changes made within that transaction are not applied to the database. So those data rows are not really deleted - that's the whole point of having transactions!

[Jay Sullivan]

But he said it "do" exist...