Sorry for replying to my own mail so many times but I've finally
tracked this down... somewhat.
* Ashley Clark in "Re: [GENERAL] why doesn't this work?" dated
* 2000/12/09 22:17 wrote:
> But I've found another quirk... I have a set of functions that insert
> and delete rows in a table (while maintaining a tree structure), these
> work fine in a transaction but doing the same combination of steps in a
> transaction by hand doesn't work. What's going on here?
This isn't quite true. It's not *exactly* the same commands, I was
executing the functions from a windows frontend I'm working on and
after calling the insert_node function it proceeded to do an update on
the resulting row. If I do an update on a record before I try to delete
it it works, but if I don't it bombs the transaction. Is this expected
behaviour? Why?
I was getting ready to show this for a simple case, but apparently
there's still something I'm missing because it doesn't work. I'd really
love to know what's going on. The more I look at this the more I get
confused as to what is actually happening.
If anyone wants to try I've attached my testing code, it will create a
database to do all this in.
Well shit, I just finished copying the relevant parts over and it works
in a separate database where there are no foreign keys defined but
what's even worse is that it also now works on the table where nothing
has changed either. This is *really* aggravating.
AHA! It seems that when a table has a foreign key in another table then
this problem surfaces (I think). So now I can actually ask, Is this
supposed to happen?
Again, sorry for the long message.
--
creaky halls