Showing posts with label cascade. Show all posts
Showing posts with label cascade. Show all posts

Tuesday, March 27, 2012

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)

Wednesday, March 7, 2012

Define more than one relationship per table?

Why is it not possible to define more than one relationship per table?

i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?

thank you::Why is it not possible to define more than one relationship per table?

This is possible. You are in error here. read the error message.

::i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2
::separate relationships.

THIS is not possible. You can only have one cascade.

::why can't i do this

Wrong location for this question. Ask the developers.

::and what are my alternatives?

Use a trigger.|||thank you for your response,

in my attempts to use a trigger, i can't seem to get around the error of a subquery returning more than one result. how can you implement a cascading delete with this restriction and/or how do your work around it?

thank you|||Triggers.

And get used to set based operation.

::i can't seem to get around the error of a subquery returning more than one result

What subquery?|||'subquery' refers to the DELETE query in my trigger.

more specifically, i have a DELETE trigger in a primary table. in this DELETE trigger, i have a DELETE query that deletes records in a foreign 'many' table. it is this DELETE query that is the 'subquery' in the error message as listed below;

error message
===============
Server: Msg 512, Level 16, State 1, Procedure triggerMyPrimaryTableDelete, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
===============

trigger code
===============
CREATE TRIGGER triggerMyPrimaryTableDelete
ON dbo.MyPrimaryTable FOR DELETE
AS
BEGIN
DELETE MyForeignManyTable
FROM MyForeignManyTable , deleted
WHERE MyForeignManyTable.ID = deleted.ID
END
===============

thank you