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 last published in July 2004

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close