EX-
BEGIN TRANSACTION
UPDATE Employee SET Fname = ‘XYZ’ WHERE ID = 1024;
SELECT * FROM Employee WHERE ID = 1024;
ROLLBACK TRANSACTION
But think while updating the table somebody forgot to use the transaction and updated the table by an UPDATEstatement without using the TRANSACTION statements shown in the above example. This may creats a lot of problem to the data and the person too. Those datas been updated in the database can not be recovered by transaction. The only way of getting the previous data back is restoring the backup of the database. If the back up is too not available then it becomes a major issue for the developer as well as for the client.
To avoid these kinds of unwanted, unwelcomed issues in life we can take the precautions before. Spending some 1 to 2 minutes we can make our DML operations secure from these kinds of unwelcomed issues as well as mistakes.
Solutions:
There are two solutions in this problem.
1. We will keep on using “BEGIN TRANSACTION—–ROLLBACK TRANSACTION” and always pay extra care while doing any DML operations, and have tension while working.
2. In order to get relief from the tension we can use some of the setting changes in the sql server tool itself.
Step-1 Open the SQL Server Management Studio.
Step-2 Go to the tools>options in the navigation bar at the top.
Step-3 Go to the query execution potion in the left navigation.
Step-4 Select SQL Server>ANSI option
Step-5 Enable the SET IMPLICIT_TRANSACTIONS
These above 5 steps will make the database more secure for the DML operations. This option will set the internal TRANSACTION on. So that if somebody forgot to use the TRANSACTION in his/her DML operations then he/she can ROLLBACK the transaction and get the previus data back again. Spend 1 to 2 minutes and live a tension free life.
For preview one can check the screen shots in this below link.
http://www.flickr.com/photos/78922413@N06/7064015235/in/photostream/