Click here to Skip to main content
Click here to Skip to main content

Tagged as

Secure way of executing Update statement in SQL Server

, 15 Apr 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Without transaction if any DML statement are executed in the database then that can be rolled back in sql server.

Introduction 

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

Code Base

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

Solutions

There are two solutions to 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 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 SET IMPLICIT_TRANSACTIONS.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

sisir patro
Software Developer
India India
Learn the base not only the requirement related things for quick fixing and make others learn the exact thing always...
Follow on   LinkedIn

Comments and Discussions

 
Questionfor help Pinmemberritukchauhan16-Apr-13 8:16 
GeneralMy vote of 5 Pinprofessionalsisirp8815-Apr-13 5:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411023.1 | Last Updated 15 Apr 2013
Article Copyright 2013 by sisir patro
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid