Re: suggestions for maintaining master/child task list

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:84qc711drvm8mrpmq5hgoku125cn5ntu8s_at_4ax.com...
> On Mon, 02 May 2005 16:51:06 GMT, "Jack Addington"
> <jaddington_at_shaw.ca> wrote:
>
>>My guess is this is not a new chore and there are some good solutions out
>>there. Right now I am thinking of something along the lines of
>>
>>1) On RowTrigger capture task_id / parent_task_id to tmp table
>>2) On StatementTrigger process all tasks in tmp table and do the math.
>
> No need for a temp table
>
> issue 2 can be resolved by one update statement
> update <...parent>
> set flagged = 'N'
> where not exists
> (select alll unflagged children)
>
> issue 1 can definitely be resolved by a hierarchical select in
> embedded in a cursor for loop. But depending on version (which you of
> course don't mention), you would think it would be possible to update
> an inline view listing all the siblings that have to be updated.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

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

jack Received on Mon May 02 2005 - 13:25:37 CDT