Ask the Expert

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 follows
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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: