Re: suggestions for maintaining master/child task list

On Mon, 02 May 2005 18:25:37 GMT, "Jack Addington" <jaddington_at_shaw.ca> wrote:

>Thanks for the quick reply and I am using 9iRC2; sorry for leaving that out.
>
>Its not the 'how to update the flags' that I have a problem with, is the way
>of firing of the queries to do it. The application is going to fire off a
>set of row updates that intermingle the parent and child rows. I can't just
>fire an update from the row trigger because it will mutate when it tries to
>look back at the same table - also the next row in the queue could change
>the results.
>
>I essentially need to process only the set that were updated and I don't see
>anyway to flag those except with a temp table or to use the update audit
>flag.
>
>thanks

Even in that case you don't need a temp table. You'll need to buffer the primary keys of the affected rows in a pl/sql *collection* (which is an in memory array structure), a before insert or update for each row trigger buffering the id's and an after insert or update statement trigger to do the actual updates.
In short : don't use temp tables.

--
Sybrand Bakker, Senior Oracle DBA