Re: Trouble with recursive trigger

On Wed, Nov 16, 2005 at 11:45:45AM +1030, Justin Hawkins wrote:
> Hi all,
>
> I am writing a bulletin board style system, which stores posts in a
> hierachy.

<snip>

> However, only the ultimate child (the post with no children posts)
> gets deleted, despite the debugging NOTICE's seeming to indicate that
> the right thing is happening.

Just a thought, maybe it has something to do with the UPDATE updating a
row where the trigger is running. So, think of the execution like
this:

# DELETE FROM post WHERE id = 3002;
trigger> DELETE FROM post WHERE parent = 3002;
*recurses*
trigger#2> DELETE FROM post WHERE parent = 3003;
*recurses*

...
trigger#5> DELETE FROM post where parent = 3005;
*recurses*
trigger#6> DELETE FROM post where parent = 3006; -- Does nothing
trigger#6> UPDATE post SET replies = replies - 1 WHERE id = 3005;

See this last line, it's updating the row while the delete trigger is
running. I don't know the semantics but what's probably happening is
that the original row the trigger ran on *was* deleted, but the UPDATE
created a new one which hasn't been deleted.

No ideas how to fix it though. Search the docs for a reference... Also,
what if it's an AFTER DELETE trigger?

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.