Re:ORA-4091: Mutating Tables...

>
>Whenever I try this (creating a trigger that calls a procedure to check
>status in detail for all 'm1' row, the error (ORA-04091 : mutating table)
>says something to the effect that I can't access the same table that I've
>put a trigger on.
>
>Any suggestions would be appreciated...
>
>Thanks,
>
>Ray Kruger
>kruger_rc_at_corning.com
>
Ray, you've hit THE mutating table problem. It is documented in the triggers chapter of the App Developers manual. The way we have dealt with this is as follows:
1) in a before insert/update (of status) statment trigger on the detail, initialize a binary integer variable to be used as a PL/SQL table pointer. Set it to 0. Note that BINARY INTEGER is a PL/SQL type.

2) in a before update (of status) on row trigger, increment the binary integer variable by 1 for each master record affected by the update statement. Then store the primary key value for that master record in a PL/SQL table in the entry pointed to by the binary variable.. Note that a PL/SQL table is an internal array containing the values for one column, and is more like a PRO*C host array than a table.

3) in an after update(of status) statement trigger, step through each tabulated master primary key in 2, selecting the statuses from its related details and updating the master if required. Mutating tables CAN be read and changed by an after statement trigger because the action of the statement itself on each of the affected rows has been completed by this point. You cannot select from the table during a row trigger on that table because you may be trying to read a row that is about to be changed but has not yet been changed.

This method takes a bit of work but it can be adapted to virtually any mutating table problem.

Ken Levine
Vancouver, BC  

update trigger (not on row but on statement, do your quewVR>~?~?~?~?~?~?~?~?~/vry and then update the master status if appropriate Received on Thu Jun 16 1994 - 08:19:32 CEST