Re: Re:ORA-4091: Mutating Tables...
From: <kruger_rc_at_corning.com>
Date: 21 Jun 94 10:25:34 -0500
Message-ID: <1994Jun21.102534.619_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.
>
Date: 21 Jun 94 10:25:34 -0500
Message-ID: <1994Jun21.102534.619_at_corning.com>
In article <CrH81y.A0s_at_iceonline.com>, kenl_at_icebox.iceonline.com (Ken Levine) writes:
>> >>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.
>
Thanks,
This looks like an approach that I can use in my application.
- Ray Kruger
> 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 Tue Jun 21 1994 - 17:25:34 CEST