Pragma14

Autonomous transactions benefits

An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions do SQL operations and commit or Rollback, without committing or rolling back the main transaction. For example, if you write auditing data to a log table, you want to commit the audit data even if the operation you are auditing later fails; if something goes wrong recording the audit data, you do not want the main operation to be rolled back.

For better understanding consider a transaction without Autonomous transaction first:

pragma1
pragma2
pragma3

Both the transactions i.e insert on emp table as well as logs are rolled back.                                  Now consider I attempted to commit transaction on logs table and tried to rollback parent transaction i.e insert on emp.

pragma4
pragma5

So I created a trigger with pragma Autonomous_transaction 

pragma6
pragma7
pragma8

Record inserted into both the tables emp and logs

pragma9

There after I rolled back

pragma10
pragma11

Here main transaction gets rolled back i.e insert on emp table rolled back

pragma12

Child transaction is executing independently even if transaction on emp rolled back.

Author Profile

Tejas
Tejas
Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j
Summary
Autonomous transaction benefits
Article Name
Autonomous transaction benefits
Description
How subprogram works within a transaction when defined as pragma autonomous_transaction? Simple way to understand this concept
Author

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.