We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

ORA-04091 mutating table problem in Before trigger.

sblang
sblang asked
on
Medium Priority
10,191 Views
Last Modified: 2007-11-27
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)!
Comment
Watch Question

Commented:
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.

Commented:
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.
CERTIFIED EXPERT
Top Expert 2008

Commented:
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.

Commented:
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
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Ora_TechieDatabase Administrator
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.