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;