There are a few people or we can say there is not even a single person who does no mistakes in his life. Mistakes are unwelcomed by everybody. Nobody wants to do mistakes in his/her life. But the other part of this statement is also true, mistakes made people learn the exact things and made people to be alert more in their life as well as their doings. From my mistakes i have learned some of the tricks and discovered some of the techniques that can prevent the commonly doing mistakes. This will lead us to live a better and less tensed life.
Always while doing any database operations in the live server we take lot's of precautions
in order to avoid mistakes. But still we can't guaranty that our transaction is secure. While updating any statements we are generally advised to use the
BEGIN TRANSACTION and
ROLLBACK TRANSACTION for any DML
UPDATE Employee SET Fname = 'XYZ' WHERE ID = 1024;
SELECT * FROM Employee WHERE ID = 1024;
But think while updating the table somebody forgot to use the transaction and updated the table by an
UPDATE statement without using the
TRANSACTION statements shown in the above example. This may creates a lot of problem to the data and the person too. Those data 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, unwelcome 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 unwelcome issues as well as mistakes.
There are two solutions to this problem:
- We will keep on using "
BEGIN TRANSACTION-----ROLLBACK TRANSACTION" and always pay extra care while doing any DML operations, and have tension while working.
- In order to get relief from the tension we can use some of the setting
changes in 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 above five steps will make the database more secure for the DML operations. This option will set the internal TRANSACTION on. So that if somebody forgot
TRANSACTION in his/her DML operations then he/she can
ROLLBACK the transaction and get the previous data back again. Spend 1 to 2 minutes and live a tension free life.
For preview one can check the screenshots here.