CODEWITHSUNDEEP
sql-server-2008t-sql
user1135534
user1135534

Reputation: 575

SQL rollback transaction

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.

Upvotes: 2

Views: 10579

Answers (5)

mtinyavuz
mtinyavuz

Reputation: 35

--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.

Upvotes: 1

Prince Jea
Prince Jea

Reputation: 5680

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

Upvotes: 3

HLGEM
HLGEM

Reputation: 96640

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.

Upvotes: 1

juergen d
juergen d

Reputation: 204894

replace

ROLLBACK TRAN

with

COMMIT TRAN

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

Upvotes: 1

Gonzalo.-
Gonzalo.-

Reputation: 12682

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

Upvotes: 5

Related Questions