PL/SQL

what is pragma autonomous transaction ?

Autonomous Transaction is a feature which maintains the state of its transactions and save it , to affect with the commit or rollback of the surrounding transactions.
Autonomous transaction is the transaction which acts independantly from the calling part and could commit the process done.

A autonomous transaction starts with the first sql statement of the pl/sql block and ends with a commit. It starts within the context of an another transaction called parent transaction and independent of it (parent transaction).To make a transaction autonomous u have to declare PRAGMA AUTONOMOUS_TRANSACTION at the beginning.
The main adv. of using PRAGMA AUTONOMOUS_TRANSATION is that weather the transaction made by the parent may be rolled back due to some error the autonomous transaction has no effect on it. Suppose if there is any error in autonomous transaction … then >>>> what happen? ? ? don’t worry***** It will save all the transactions just before the error occurred. Only the last transaction that has error will be rolled back only if there is no error handler.


ora816 SamSQL :> declare2 Procedure InsertInTest_Table_B3 is4 BEGIN5 INSERT into Test_Table_B(x) values (1);6 Commit;7 END ;8 BEGIN9 INSERT INTO Test_Table_A(x) values (123);10 InsertInTest_Table_B;11 Rollback;12 END;13 /

PL/SQL procedure successfully completed.

ora816 SamSQL :> Select * from Test_Table_A;X----------123ora816 SamSQL :> Select * from Test_Table_B;X----------1

Notice in above pl/sql COMMIT at line no 6 , commits the transaction at line-no 5 and line-no 9. The Rollback at line-no 11 actually did nothing. Commit/ROLLBACK at nested transactions will commit/rollback all other DML transaction before that. PRAGMA AUTONOMOUS_TRANSACTION override this behavior.

Let us the see the following example with PRAGMA AUTONOMOUS_TRANSACTION.

ora816 SamSQL :> declare2 Procedure InsertInTest_Table_B3 is4 PRAGMA AUTONOMOUS_TRANSACTION;5 BEGIN6 INSERT into Test_Table_B(x) values (1); 7 Commit;8 END ;9 BEGIN10 INSERT INTO Test_Table_A(x) values (123);11 InsertInTest_Table_B;12 Rollback;13 END;14 /

PL/SQL procedure successfully completed.

ora816 SamSQL :> Select * from Test_Table_A;

no rows selected

ora816 SamSQL :> Select * from Test_Table_B;

X----------1
What is mutating trigger? How do you resolve it?If a trigger body is trying to update dependent tables, will it get a mutating error?

Mutating trigger has similar concept to that of deadlock. When a table is in the half way of executing a transaction and is the owner of a trigger and if the same table is accessed by the trigger somewhere else in the same time then at that instance mutating trigger occurs.
Mutating trigger is trigger that is currently being modified by DML opertion. For eg. You created a trigger trigger1 on table1 it should fire after update for each row. And you wrote some update statement on the same table (table1) inside the trigger . When you execute the individual update stmt on table1 the trigger get fires and the trigger also is currently being updated the same rows in table1 which is called mutating error and mutating trigger.