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

tacrawford_at_adelphia.net a écrit :

> 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.
>

Indeed... *IF* I get you correctly, then you should drop the autonomous
transaction pragma, and apply the standard pattern :

1. Initialize some package data in a before table trigger
2. Store in the package data the affected (resource slot/project/task ?) with an after row trigger
3. Recompute the starting/ending date in an after table trigger, for the data stored in the package.

This will work in the face of multiple row updates, rollbacks, etc...

You can find the pattern described in excruciating details in the reference I gave earlier...

Take care

• Raoul

> >
> > > 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 Wed Dec 20 2006 - 02:37:38 CST