I have written a trigger on Scott.emp and also created a package variable p. The package declaration is as fol...
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;