Re: Trouble with recursive trigger

On Wed, Nov 16, 2005 at 07:43:16AM +0100, Martijn van Oosterhout wrote:

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

Yep I suspect it's something like this. I don't see why, as to me if
the trigger hasn't completed yet then the row should still be
there. And if that's not the case (the row is in some sort of
half-deleted limbo state) then I'd expect some sort of sensible error,
not a quiet failure of the subsequent completion of the trigger to
actually delete the row.

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

The referential integrity means that if I delete a row in 'the middle'
I need to delete the children myself first. If I let a cascade deal
with that then I don't get the opportunity to update rows further up
the tree to reflect the fact there are now less replies.

I can't see any particular flaw in my method so I'd really like to get
to the heart of why this doesn't work.

- Justin

--
Justin Hawkins | justin(at)hawkins(dot)id(dot)au
| http://hawkins.id.au