ORA-04091

[rotem_fo]

Hi,
I have a complex problem.
I am having trouble with triggers in Oracle.
I have Table a (parent) and Table b (child).
I want to update a certain column in the parent table, after each delete in the child, by querying all the instances of the parent in the child table. (patient (parent) - study (child) i.e. for each patient there are a number of studies).

I have a trigger after delete on the child table that triggers an update on the parent table at a certain column (which is composed of all child instances for that specific row in the database).

I keep getting ORA-04091 errors because i am performing a select on the table from which the trigger was fired.

What do i do ???

Thanx
Rotem.

[murugappan]

A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement
Make sure that modifications are permanent (in the sense commit has been issued) before executing trigger.

So rewrite the trigger and see.

HTH.

[bensr]

Hi,
I don't know the exact solution by heart, but I've seen
examples by saving all the changes in a PL/SQL table
with the AFTER INSERT/DELETE/UPDATE FOR EACH ROW-trigger and finaly process the contents of the PL/SQL-table in the AFTER INSERT/DELETE/UPDATE table-trigger.

Success