Transactions in SQL

Follow this tutorial to learn how to use Transactions in SQL and see how we can roll back to previous changes done in database.

Don’t forget to check out our site http://howtech.tv/ for more free how-to videos!
http://youtube.com/ithowtovids – our feed
http://www.facebook.com/howtechtv – join us on facebook
https://plus.google.com/103440382717658277879 – our group in Google+

A transaction in SQL is basically the process of one or more changes being made in the database.
In this tutorial we will learn the three basic controls of transactions in SQL which are COMMIT, ROLLBACK, and SAVEPOINT

Step 1- Commit Command
The Commit command is used to save the changes made in the database. To see its implementation, start by first beginning the transaction.
Before starting any Transaction in SQL, we have to write the Begin Transaction statement. After that, write the actual action which is required, for example delete from the employee table, where the salary is greater than $2500.
After that, write the Commit keyword which confirms that the query is logically correct and can be executed.
The Query in this case would be:
begin trandelete from EMP
where SAL 2500
commit

Now when we run the query, a message appears below the Query Editor which shows the number of rows that have been affected by our query.

Step 2- Effect of Commit Command
After that, fetch all the records from the employee table and it can be seen that all the records which have a salary above $2500 have been deleted.

Step 3- Save Points
Now let’s examine the SAVE POINT command. To understand the concept, we will take an example of a departmental table.
Start writing the query by beginning a transaction and then entering in the “Save Transaction” command followed by the transaction name.
After that, let’s delete a record with the Department number as 10 and then again save the transaction. This time, define the save point as point2.
With that done, delete another department, and save the transaction, defining it as point3.
Actual Query would be like this:
begin tran
save tran point1

delete from DEPT
where DEPTNO=10
save tran point2
delete from DEPT
where DEPTNO=20
save tran point3
After executing it, 2 rows would get affected.

Step 4- Departments Deleted
Now, see the department table to view the changes. For that, let’s fetch all the records from the table. It can be seen that department “10” and “20” has been deleted from the table.

Step 5- Roll Back
If we want to track back to the previous changes, we can use the save points created earlier along with the Rollback transaction command.
The rollback transaction allows us to remove all the modifications made to the data, either from the start of the transaction or to a defined save point.
For that, we will use the rollback transaction statement and define the save point after that. Over here, let’s roll back to point number 2.
The Query would be:
rollback tran point2
select * from DEPT
Once we execute the query, and fetch all the records from the table, it can be seen that the table now contains the data up till point number 2 in the query.

And this is how we can use Transactions in SQL.