asked on

ORA-04091 mutating table problem in Before trigger.

I had read in reply to a similar question that the way around the mutating table problem was to create a before trigger to collect the data needed in global package variable.

I followed this advice but I am still getting the ora-04091 error.

I created before trigger that calls a function in a package that also contains a public pl/sql table to store the information I need in the after trigger. The function contains a cursor that includes a reference to the trigger table.

Am I correct in assuming that I cannot query the table being updated period! (i.e. in either the before or after trigger)!

mudumbe

Am I correct in assuming that I cannot query the table being updated period! (i.e. in either the before or after trigger)!

-- Not entirely!

You can make a standalone function/procedure or package.function/procedure and access the table. However, you MUST set the "purity level" to atleast WNDS (Write No Database State). You set the purity level by using

pragma restrict_references(<function/procedure>, WNDS);

Then you can reference it in the trigger.

concon

Hi sblang,

you can use Autonomous transaction to query trigger table inside trigger.

this is a nice article about autonomous transactions and table mutations.

http://techrepublic.com.com/5100-6313-5034684.html

Regards.

schwertner

You do not need to use a cursor.
Define an PL/SQL table and a variable which will keep the number of the entries in the table.
At the end run a procedure which simply reads the number variable and scans sequentially the table.

n4nazim

Hi,

You are correct!!!!. You cannot fire an SELECT statement from the same table on which the trigger is running.

This is because when the trigger is running the table is internally set to be in MODIFIED state and hence an MUTATING ERROR will occur.

Here is a nice link on mutating triggers pl do go thru

http://www.ittepic.edu.mx/eBooks/computacion/80oreilly/books/oracle/prog2/ch20_02.htm

Rgds,
NHM

ASKER CERTIFIED SOLUTION

andrewst

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Ora_Techie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial