Delete Cascade on cyclic relationship

If I have 3 tables with cyclic relationship like this. Table A has a fk
referencing Table B, table C has a fk referencing table B and table A has a
fk referencing table C.
|--<--fk--|
| |
v |
A--fk-->B--fk-->C
Question is how do I handle "Delete Cascade". How do I write triggers to
handle this?
Thanks,
Tom DOn Sun, 23 Jan 2005 22:35:52 -0800, tom d wrote:

>If I have 3 tables with cyclic relationship like this. Table A has a fk
>referencing Table B, table C has a fk referencing table B and table A has a
>fk referencing table C.
> |--<--fk--|
> | |
> v |
> A--fk-->B--fk-->C
>
>Question is how do I handle "Delete Cascade". How do I write triggers to
>handle this?
>Thanks,
>Tom D
Hi Tom,
I just replieed to your question about the self-referencing table. The
trick for this cyclic relationship is essentially the same. If you are
sure that the nest level won't pass 32, just make a trigger for each of
the tables that will delete from the referencing table all rows
referencing a deleted row, make sure you enable nested and recursive
triggers and don't forget to start each trigger with
IF @.@.ROWCOUNT=0
RETURN
If you think the 32 level nesting limit might cause problems, use the
alternative approach outlined in my other message. This time, you'll have
to use three temp tables and run through a three-step cycle to add rows to
each of these temp tables in turn.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)