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@xxxxxxxxx> 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.
Attachment:
pgpd6sxnAqR1q.pgp
Description: PGP signature
