Problem with mutating table

Hi everybody.

I´ve met a curious error while trying to fire a procedure from several triggers.

The statement is quite easy... All I want is to fire a procedure that tracks al the rows in a table that have the same value in a certain field that the row/s modified (inserted,updated or deleted).

So... I´ve made three after triggers for each row in the table so if any row changes in the table, a procedure that makes a query to view what rows whith this special value are left in the table is fired.

This has generated an ORA-04091 error, alerting that the table must be mutating and my procedures/triggers may not view it. I´ve readed in some internet database sites where people says that this problem will be fixed making before triggers instead of after ones.

I´ve tried but... however there is no error, my procedure does not retrieve all information, because it doesn´t view the last row changing (inserted, updated or deleted) in the table.

Any suggestion?
Thanks in advance.
Bardo.

Mutating table problem = bad design problem, IMHO.
Build the procedure into a background job and let the triggers notify the job about the values.
Use dbms_alert or dbms_pipe to notify the job.

Quote:

---

Originally posted by Bardo

So... I´ve made three after triggers for each row in the table so if any row changes in the table, a procedure that makes a query to view what rows whith this special value are left in the table is fired.

This has generated an ORA-04091 error, alerting that the table must be mutating and my procedures/triggers may not view it.

---

This could be happening because you procedure is using DML statements that includes the table that fires the trigger.

If you wanna reference some value for a column in the row of the table that actually fires the trigger, you can use the :old and :new keywords to get it.

HTH

Start using Autonomous transaction that solves mutating table error.