How to manage the transaction in SQL stored procedures and functions

Transaction refers to the logically connected SQL commands containing the INSERT, UPDATE, SELECT, DELETE statements, and even more. Transactions differ from an Adhoc SQL statement in a way that they are a group of SQL statements that work in such a way that either all the statements are executed or none of them gets executed and the ones which have been executed gets rolled back, in case an exception occurs. Transactions follow the ACID properties.

• A : A for Atomicity
• C : C for Consistency
• I : I for Isolation
• D : D for Durability

PROCESS FLOW IN A TRANSACTION

BEGIN TRANSACTION

SQL STATEMENTS

If (error occurs)

ROLLBACK TRANSACTION

Else

COMMIT TRANSACTION

For executing transactions through a stored procedure, we first need to create a stored procedure. We create a stored procedure name ‘employee’ to update the names of the employees with a particular Id in the ‘Empl’ table. The table ‘Empl’ is given below

Now we create a stored procedure to update the names of the employees.

We put the SQL statements in a try block and finally committed the transaction. This way if an error occurs, then the control moves to the catch block and the transaction would be rolled back instead of executing a few of the statements which might have made changes to the database. We even added print statements to verify if the commit and rollback commands work well. Now we finally execute the stored procedure.

The commands have been successfully executed. Now we can notice the modifications in the ‘Empl’ table

Now if we intentionally try to generate the exception to verify the work of rollback command, we can insert a record with an id that is already existing in the table. Due to the violation of the primary key constraint, the command would be rolled back as the id has to be unique

Therefore, we altered the above-stored procedure as follows

We tried to insert a record with id=99, although the id 99 is already existing in the table. Now we execute the stored procedure.

As an exception has been encountered, the transaction was successfully rolled back. When the control moved inside the transaction statements, the first DML statement it encountered was successfully executed. As soon as, the control reached the second DML statement it encountered an exception due to violation of the primary key constraint. So the control moved to the catch block and rolled back undoing the changes done, as a result of the first DML statement. And no changes have been made to the table

We have successfully updated the records using the transactions through stored procedures. Similarly, following the above steps, we can even execute transactions through SQL functions.