Q

Resolving errors ORA-04091, ORA-06512 and ORA-04088

I have written a trigger on Scott.emp and also created a package variable p. The package declaration is as fol

lows

Create or replace Package pkg as
p number;
end;

The trigger that was written was:

create or replace trigger trigg 
before insert or update of deptno on emp 
BEGIN SELECT DEPTNO INTO pkg.P  
FROM EMP WHERE EMPNO=7839; 
END; 
/

But when I ran a query against it:

update emp set deptno=40 where empno=7369;

It throws the error:

ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.UPD", line 2
ORA-04088: error during execution of trigger 'SCOTT.UPD'

I can't use new and old operators here because I don't want to access the row that is processed. Please provide a solution for it.
 

The fact that you are selecting rows from the same table in the trigger that is being fired is causing the ORA-04091 error. Starting with Oracle 8i you can use PRAGMA autonomous transaction that will ensure that your trigger will not give a mutating table error. I think it is potentially dangerous solution to use PRAGMA autonomous transaction in your trigger statement and will recommend against it just from a design perspective. However, if you are adamant about your code, try using PRAGMA autonomous transaction.

Create a procedure or a function with the following statement in the declaration section:

pragma AUTONOMOUS_TRANSACTION;

Create or replace procedure call_from_my_trigger_trigg Is
pragma AUTONOMOUS_TRANSACTION;
BEGIN
      SELECT DEPTNO 
           INTO pkg.P  
       FROM EMP 
  WHERE EMPNO=7839; 
END;
/

Create or replace trigger trigg before insert or update of deptno on emp is
Begin
 Call_from_my_trigger_trigg;
End;


 

This was first published in July 2004

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close