Leveraging SQLScript in Stored Procedures & User Defined Functions through the use of Autonomous transactions
You will learn
The autonomous transaction is independent from the main procedure transaction. Changes made and committed by an autonomous transaction can be stored in persistency regardless of commit/rollback of the main transaction. The end of the autonomous transaction block has an implicit commit.
Please note - This tutorial is based on SPS11
Step 1: Edit previous procedure
Return to the procedure called get_product_by_filter.
Remove the COMMIT statement, and instead wrap the INSERT statement with an AUTONOMOUS TRANSACTION block as shown.
Another “Chuck” record was not inserted. “Chuck Norris’s” record was removed by the rollback (“Is that even possible…?”) by using AUTONOMOUS TRANSACTION blocks, the code within is isolated from the rest of the mainline code and is treated as a separate transaction.