Use of Begin, Commit, Rollback Transactions in SQL Server

The SQL Server provides very useful feature which is Begin, Commit, Rollback Transaction.
When we use Begin Transaction before we use DML Queries, we can Commit or Rollback that Transaction after the confirmation. This is very useful if you update anything wrongly then you can rollback that transaction.
For example,As shown below, I am trying to update the NodeID column data to 5 from 1.




begin transaction

update DownLoad_Data set NodeID=5


But after I updated, You can check whether you have been updated properly. But here, I realised I did not mention the Where clause.


select * from DownLoad_Data



So I have to rollback this transaction. For that I can use Rollback Transaction since I used Begin Transaction.


Rollback transaction


So again I changed the Query and run it.


Begin transaction

update DownLoad_Data set NodeID=5 where RecNo=1


Still you can check whether have been updated properly. If it is updated correctly then, Run the Commit Transaction to make all updates permanently.




Commit Transaction

I hope this would have been very useful for you all.

Comments

Post a Comment

Popular posts from this blog

SQL SERVER – TRIM() Function – UDF TRIM()