SQL Developers get a lot of Adhoc tasks specially from the front end development team to insert, update and delete records (DML Operations) into the database. Although these commands are simple to write and execute but if a developer executes a wrong command then it may create unnecessary trouble to the developer.
In this post I am writing the good practice to follow while executing insert, update and delete operations specially when you are doing changes in test or developer environment .
In the figure below there is a table EMPLOYEEDETAILS and assume a developer got an adhoc task to modify DOB for EMP_NO=100 to ‘1965-12-12’ and modify the name ‘Sunitaa’ to ‘Sunita’.
Now developer writes the ADHOC script as follow
UPDATE [EMPLOYEEDETAILS] SET DOB=‘1965-12-12’ WHERE EMP_NO=100
UPDATE [EMPLOYEEDETAILS] SET NAME=‘SUNITA’
And after that when the developer checked the table to see the changes,all the values of the NAME column are updated to ‘Sunita’ as shown in the following figure because in the second command written above, the developer forgot to put the filter condition.
The better way of writing the command would be to write BEGIN TRAN before executing these DML statements as shown below.
BEGIN TRAN
UPDATE [EMPLOYEEDETAILS] SET DOB=‘1965-12-12’ WHERE EMP_NO=100
UPDATE [EMPLOYEEDETAILS] SET NAME=‘SUNITA’
This has created an open transaction which is neither committed nor rollbacked. After that the developer will check the table to see the changes. As all the values of the NAME column are updated to ‘Sunita’, SQL developer can execute rollback command.
So now he just needs to type the following command
ROLLBACK
This command will rollback all the changes done by the transaction.
Now developer can modify the command as follow by writing the filter condition in the second statement as shown below.
BEGIN TRAN
UPDATE [EMPLOYEEDETAILS] SET DOB=‘1965-12-12’ WHERE EMP_NO=100
UPDATE [EMPLOYEEDETAILS] SET NAME=‘SUNITA’ WHERE EMP_NO=105
And after executing the statements above, developer can check the changes by using SELECT command as shown below
Now the output is perfect and developer can commit the open transaction by writing the following code.
COMMIT
So the moral of the story is to it is a good practise to write BEGIN Tran in the beginning of DML statement before executing it. Check the changes if the changes are perfect execute COMMIT otherwise execute ROLLBACK.
Important Note : This practice is good when you are doing changes in test or developer environment. But for production it should not be used or used very cautiously because the resources are locked by the transactions until you run commit or rollback which may impact other transactions running on the production server.
If this is required in production, one could simply put the select statements to verify the changes on the same script with a rollback at the end. Then once the script is run and changes verified, just change the rollback to a commit and re-execute.