In SQL server each entry in a database count as a transaction. Through these transactions, SQL Server manages its database. Sometime a user performs such tasks in SQL Server that he did not want to perform. To handle such type of actions SQL Server uses some transaction control commands. These commands are used to control the data integrity and take control on database errors. In this article we will discuss one of the transaction control command ROLLBACK and know how to rollback a transaction in SQL Server.How to use Rollback Command?
The ROLLBACK command in SQL Server generally used to undo the transaction that have not been saved to the database.SYNTAX ROLLBACK;
Now, first of all we will create a table and checking it by running select statement.
After executing the above statement, the student table will create in our database. Now, I will insert a record in this table by executing the insert statement:
The above statement will create a record in student table.
Now I am going to DELETE this record from the student table by executing below statement:
The above statement will remove the record from student table.
Oops! But there is something wrong with this statement, I forgot to place BEGIN TRANSACTION before above two statements.BEGIN TRAN:
If you place the BEGIN TRANSACTION before your statement (Insert, DELETE,Update), The transaction will automatically turn into the explicit transaction and it will lock the table until the transaction is committed or rolled back.
So, Now I will place the begin transaction before the Insert, DELETE, Update statements.
Lets again insert a value in student table
Now, executing the delete statement
The transaction is now deleted. Since the transaction is locked by BEGIN TRANSACTION, so I can rollback the above deleted record by using the ROLLBACK command.
After executing the ROLLBACK command, and running select statement we see that we have successfully recovered our deleted record.
NOTE:The ROLLBACK command only works until the transaction is not COMMITTED.Conclusion:
The article describes how an uncommitted transaction can be rolled back in such situation, when a user does not want to perform it. But you need to keep it in your mind, whenever you perform such task when you want your transactions to be rolled back, your transaction must be locked from BEGIN TRANSACTION and the transaction should not be committed. The BEGIN TRANSACTION locks your transaction until you do not rollback or commit your transaction.