Greens Technologys Whatsapp
Greens Technologys Whatsapp

Autonomous Transaction in oracle

+91-89399 15577

Autonomous Transaction in oracle

An autonomous transaction is an independent transaction started by another transaction called as main transaction. It let you allow to execute various DML statements, commit and rollback without commiting or rolling back the DML statements issued by the main transaction.

When to use Autonomous_Transaction ?

We use an Autonomous transaction in situations where we do not depend on the status of main transaction means whether it get fails or succeeded. For example, suppose we are having a trigger for auditing purpose on table employee which inserts auditing information into auditing table. All the auditing information that is when we execute update or delete or insert statements on the table employee, these details should get inserted in auditing table irrespective of the main transaction gets completed successfully or it gets failed.





Oracle allows us to write transaction control statements (COMMIT, ROLLBACK, and SAVEPOINT) in trigger but we cannot execute insert, update or delete statements on the table, so that we cannot use COMMIT, ROLLBACK and SAVEPOINT in trigger. It means that if our main transaction issues COMMIT statement then only auditing details get inserted into auditing table else not. But, we should have all the auditing information with us. In this situation we can use autonomous transaction for trigger which will be an independent transaction and allows transaction control statements in trigger.


How to use Autonomous Transaction?

For making a transaction as Autonomous Transaction we need to employ AUTONOMOUS_TRANSACTION pragma in the declaration section of PL/SQL block as shown in below block.

DECLARE
    PRAGMA  AUTONOMOUS_TRANSACTION;
BEGIN
    ...
END;

Below example illustrates how to use AUTONOMOUS_TRANSACTION. We have created two tables EMPLOYEE,TABLE_AUDIT, one sequence TABLE_AUDIT_ID_SEQ.

CREATE TABLE EMPLOYEE
(
    EMP_ID     NUMBER(10),
    EMP_NAME   VARCHAR2(40),
    DEPT_ID     NUMBER(10)
);

CREATE TABLE TABLE_AUDIT
(
  ID            NUMBER(10) NOT NULL,
  ACTION        VARCHAR2(10) NOT NULL,  
  CREATED_TIME  TIMESTAMP
);

CREATE SEQUENCE TABLE_AUDIT_ID_SEQ;

See the below definition for the trigger TIG_AUDIT_EMP. We have not used Autonomous_Transaction in this trigger.

CREATE OR REPLACE TRIGGER TIG_AUDIT_EMP
AFTER INSERT OR UPDATE OR DELETE ON EMPLOYEE
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO TABLE_AUDIT 
    VALUES (TABLE_AUDIT_ID_SEQ.NEXTVAL,'INSERT',SYSTIMESTAMP);
    ELSIF UPDATING THEN
        INSERT INTO TABLE_AUDIT 
    VALUES (TABLE_AUDIT_ID_SEQ.NEXTVAL,'UPDATE',SYSTIMESTAMP);
    ELSE
        INSERT INTO TABLE_AUDIT 
    VALUES (TABLE_AUDIT_ID_SEQ.NEXTVAL,'DELETE',SYSTIMESTAMP);   
    END IF;
END;

Now we are ready with all necessary database objects for our demonstration. I am executing INSERT and UPDATE statement on employee table and commiting the transaction.

SQL> INSERT INTO EMPLOYEE VALUES(1,'RAM','10');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES(2,'GANESH',11);
1 row created.
SQL> UPDATE EMPLOYEE SET DEPT_ID = 15 WHERE EMP_ID = 1;
1 row updated.
SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM EMPLOYEE;
    EMP_ID EMP_NAME     DEPT_ID
---------- ------------ ----------
         1 RAM          15
         2 GANESH       11

SQL> SELECT * FROM TABLE_AUDIT;
        ID ACTION    CREATED_TIME
---------- --------- ----------------------------
         1 INSERT   24-JUL-13 03.26.51.819656 PM
         2 INSERT   24-JUL-13 03.27.39.634371 PM
         3 UPDATE   24-JUL-13 03.28.20.243719 PM

Impressed with our Course Content?
Attend a Free Demo Session to Experience our Quality!


Both tables are having the all the transaction data. But what if we issue DML Statement and ROLLBACK the transaction.

SQL> INSERT INTO EMPLOYEE VALUES (4,'Sagar',20);
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES(5,'Prampod',21);
1 row created.
SQL> DELETE FROM EMPLOYEE WHERE EMP_ID = 1;
1 row deleted.
SQL> ROLLBACK;
Rollback complete.

SQL> SELECT * FROM TABLE_AUDIT;
        ID ACTION    CREATED_TIME
---------- --------- ----------------------------
         1 INSERT   24-JUL-13 03.26.51.819656 PM
         2 INSERT   24-JUL-13 03.27.39.634371 PM
         3 UPDATE   24-JUL-13 03.28.20.243719 PM

TABLE_AUDIT is not having the above transaction statements data. But we should have all the transaction data whether transaction gets COMMIT or ROLLBACK.

For this we need to make trigger transaction as Autonomous_Transaction. See the below updated trigger defition.

CREATE OR REPLACE TRIGGER TIG_AUDIT_EMP
AFTER INSERT OR UPDATE OR DELETE ON EMPLOYEE
FOR EACH ROW
DECLARE
    PRAGMA  AUTONOMOUS_TRANSACTION;
BEGIN
    IF INSERTING THEN
        INSERT INTO TABLE_AUDIT 
    VALUES (TABLE_AUDIT_ID_SEQ.NEXTVAL,'INSERT',SYSTIMESTAMP);
    ELSIF UPDATING THEN
        INSERT INTO TABLE_AUDIT 
    VALUES (TABLE_AUDIT_ID_SEQ.NEXTVAL,'UPDATE',SYSTIMESTAMP);
    ELSE
        INSERT INTO TABLE_AUDIT 
    VALUES (TABLE_AUDIT_ID_SEQ.NEXTVAL,'DELETE',SYSTIMESTAMP);   
    END IF;

    COMMIT;

END;

I am executing the above rollback statements once again.

SQL> INSERT INTO EMPLOYEE VALUES (4,'Sagar',20);
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES(5,'Prampod',21);
1 row created.
SQL> DELETE FROM EMPLOYEE WHERE EMP_ID = 1;
1 row deleted.
SQL> ROLLBACK;
Rollback complete.

SQL> SELECT * FROM TABLE_AUDIT;
        ID ACTION     CREATED_TIME
---------- ---------- ------------------------------
         1 INSERT     24-JUL-13 03.26.51.819656 PM
         2 INSERT     24-JUL-13 03.27.39.634371 PM
         3 UPDATE     24-JUL-13 03.28.20.243719 PM
         8 INSERT     24-JUL-13 04.28.15.410866 PM
         9 INSERT     24-JUL-13 04.28.22.401043 PM
        10 DELETE     24-JUL-13 04.28.27.615402 PM

Now, we are having all the transactional data even if we have rollback our main transaction.



  • 100% Practical Training

  • Hands
    On
    Training

  • 100%
    Job
    Gurantee


PRAGMA in Oracle PL SQL

Our Reviews 5 Star Rating: Recommended - Best IT Training in Chennai

5  out of 5  based on 12263 ratings.

Google          5 Star Rating: Recommended - Best IT Training in Chennai

Facebook      5 Star Rating: Recommended - Best IT Training in Chennai

UrbanPro       5 Star Rating: Recommended - Best IT Training in Chennai

Sulekha         5 Star Rating: Recommended - Best IT Training in Chennai

Yet5              5 Star Rating: Recommended - Best IT Training in Chennai

Justdial          5 Star Rating: Recommended - Best IT Training in Chennai


Oracle SQL Training in Chennai

Oracle SQL Training in Chennai
best Oracle SQL Training center in chennai

"I thought I knew Oracle SQL until I took this course. My company sent me here against my will. It was definitely worth and I found out how many things I was doing wrong. Karthik is awesome. but i got a lot inspired by you. I will keep in touch and will always try to learn from you as much as I can. Thanks once again Karthik"


Greens Technologys Overall Reviews


Greens Technologys Overall Reviews

5 out of 5 based on 17,981 ratings. 17,981 user reviews.





Relevant Courses