ORA-04091 Table is mutating, trigger/function may not see it

We have written a series of stored procedures that are designed to move the data that is on a staging table into a production version of that table. The stored procedures perform several tasks, including replacing blank values with default values, checking for out of range values, setting foreign key ID columns and then either inserting the data into the production table or updating an existing row on the production table.

We also wrote a front end to these stored procedures in VB. The front end would take data from a text file, move it into the staging table and then call the stored procedures. This all works without problems.

We have decided it would be nice if we could allow our users to write directly to the staging table. This is especially nice if the data they wish to move into the production database is in another database on the same instance of Oracle. With the above front end, they would have to export to a text file, and then re-import using the VB front end. If they could just write the data directly to the staging table, it would save them some hassle. We still want our stored procedures to run so that we can verify the correctness of their data, etc.

Our idea was to write an insert trigger on the staging table. The insert trigger would do some preliminary checking, and, if all was OK, it would then call the existing set of stored procedures. This is where things go bad. When I insert a row onto my staging table, the trigger fires. The trigger then correctly calls the stored procedure. But as soon as my stored procedure does an update against the staging table, the "ORA-04091 table is mutating" error occurs. My guess is that Oracle does not like the fact that the stored procedure is doing a "mass" update instead of updating just the one row that was inserted (using the "new" logical row).

Is there a way around this? I have already done this very thing using a Sybase SQL Anywhere database with no problems at all. I am saving MSSQL 6.5 for last because it is usually the biggest pain. Imagine my surprise when Oracle told me that I was "mutating" things.

Todd Dixon
Gyrus Systems, Inc. Received on Fri May 01 1998 - 10:43:43 CDT