Mutating Table Exception - A hack

The problem :

create or replace triggers test_trig
after insert or delete or update on emp
for each row
declare

    cursor c1 is

         select 'x'
         from emp
         where ....;

    open c1;
    fetch c1 into l_temp;
    close c1;
end;
/

You can create this trigger, but will raise ORA-4091 "Mutating table" when trigger is executed by a triggering statement. The limitation is that, in an After row / for each row trigger, you cannot select from a table thats 'mutating' ( foreign key tables are included too ).

One of the soultions is to create a DB link ( to the same database ) and use this inside the DB trigger.

• Create a database link to the same database. The connect username should be the owner of the table. create database link test_db_link connect to scott identified by tiger using 'SQL net string';
• create or replace package test_pack as cursor c1 is select 'x' from emp_at_test_db_link; end test_pack;
  /
  

create or replace trigger test_trig
after insert or update or delete on scott.emp for each row
declare

    L_temp varchar2(1);
begin

    open test_pack.c1;
    fetch test_pack.c1 into L_temp;
    close test_pack.c1;
end;
/

For some reason, if you place the select across the DB link inside the trigger body, the trigger fails to compile. Try referencing the cursor thro' a package and it works fine.

There is one other thing thats to be done, after the transaction completes. Execute 'alter session close database link test_db_link'. Probably execute this thro' the DB trigger, using dynamic SQL.

Just another solution - probably impractical to the purists !!

The other solutions to this problems need atleast 3 database triggers, at different levels, and a PL/SQL table.

Any thoughts !!

-- 
Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia
Email : ssubrama_at_nibucorp.ccdn.otc.com.au / avion_at_ozemail.com.au

Disclaimer : All opinions are truly and just mine.