Re: procedure called by trigger can't see new data

     I have a table that contains project data (table 1). I also have a table that contains the starting/ending dates of each project (table 2). When I modify the required_weeks column, I need to re-calculate the starting/ending dates for all projects in that resource slot.

     What had been done was an AFTER UPDATE trigger on the project table (table 1) that called a stored proc to calculate the starting/ending dates for all projects in the same resource slot of the modified project.

     These starting/ending dates were queried from the project table (table 1), math was applied, then they were written to the dates table (table 2).

     The problem that exists is that apparently the data is not committed when the trigger runs, causing the stored proc to query the OLD data when it is trying to calculate the starting/ending dates.

hasta_l3_at_hotmail.com wrote:
> tacrawford_at_adelphia.net a écrit :
>
> > The "pragma autonomous_transaction" keeps this from happening. The
> > other problem is that while the user updates one record with a
> > particular keyfield (name), I need to calculate all records that have
> > the same value in the keyfield (name).
> >
> > Mark D Powell wrote:
> > >
> > > When you call a procedure from a database table trigger you normally
> > > pass the procedure the row data that it is to work with. If you try to
> > > work with the table that the trigger is defined on then you will likely
> > > end up with a "mutating table" error.
> > >
>
> Dear,
>
> Note that the "mutating table " is actually protecting us from
> nasty mistakes. You may be on a dangereous path if you are
> trying to circumvent it with an autonomous transaction ...
>
> Tom Kyte discusses the issue at length in his
> (highly recommended) books. If you don't have them,
> you may want to browse the articles at
> http://asktom.oracle.com - search for "mutating table"
>
> You will - notably - find there an article documenting a
> pattern to achieve your goal.
>
> Regards
>
> --- Raoul
Received on Tue Dec 19 2006 - 15:10:48 CST