I have written a trigger on Scott.emp and also created a package variable p. The package declaration is as fol...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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;
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Azim Fahmi
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.