AUTONOMOUS TRANSACTION

AUTONOMOUS TRANSACTION

Prior to Oracle8i, there was no way in which some SQL operations within a transaction could be committed independent of the rest of the operations. Oracle allows this, however, through autonomous transactions. An  autonomous transaction is a transaction that is started within the context of another transaction, known as parent transaction, but is independent of it. The autonomous transaction can be committed or rolled back regardless ot the state of the parent transaction.

Ex:

CREATE OR REPLACE TRIGGER AUTONOMOUS_TRANSACTION_TRIGGER

after insert on student

DECLARE

pragma autonomous_transaction;

BEGIN

update student set marks = 555;

commit;

END AUTONOMOUS_TRANSACTION_TRIGGER;

Output:

SQL> select * from student;

        NO  NA      MARKS

       ----- ----- -- ----------

         1    a         111

         2    b         222

         3    c         300

SQL>  insert into student values(4,'d',444);

SQL> select * from student;

        NO  NA      MARKS

        ---- ------ -- ----------

         1    a         555

         2    b         555

         3    c         555

         4    d         444

RESTRICTIONS ON AUTONOMOUS TRANSACTION

Ø  If an autonomous transaction attempts to access a resource held by the main transaction, a deadlock can occur in you program.

Ø  You cannot mark all programs in a package as autonomous with a single PRAGMA declaration. You must indicate autonomous transactions explicity in each program.

Ø  To exit without errors from an autonomous transaction program that has executed at least one INSERT or UPDATE or DELETE, you must perform an explicit commit or rollback.

Ø  The COMMIT and ROLLBACK statements end the active autonomous transaction, but they do not force the termination of the autonomous routine. You can have multiple COMMIT and/or ROLLBACK statements inside your autonomous block.

Ø  You can not rollback to a savepoint set in the main transaction.

Ø  The TRANSACTIONS parameter in the oracle initialization file specifies the maximum number of transactions allowed concurrently in a session. The default value is 75 for this, but you can increase the limit.

MUTATING TABLES

There are restrictions on the tables and columns that a trigger body may access. In order to define these restrictions, it is necessary to understand mutating and constraining tables.

A mutating table is table that is currentlty being modified by a DML statement and the trigger event also DML statement. A mutating table error occurs when a row-level trigger tries to examine or change a table that is already undergoing change.

A constraining table is a table that might need to be read from for a referential integrity constraint.

Ex:

CREATE OR REPLACE TRIGGER MUTATING_TRIGGER

before delete on student

for each row

DECLARE

ct number;

BEGIN

select count(*) into ct from student where no = :old.no;

END MUTATING_TRIGGER;

Output:

SQL> delete student where no = 1;

delete student where no = 1

       *

ERROR at line 1:

ORA-04091: table SCOTT.STUDENT is mutating, trigger/function may not see it

ORA-06512: at "SCOTT.T", line 4

ORA-04088: error during execution of trigger 'SCOTT.T'

HOW TO AVOID MUTATING TABLE ERROR ?

Ø  By using autonomous transaction

Ø  By using statement level trigger