i have one simple table with following columns: id, name and parentID
我有一个简单的表，包含以下列:id、name和parentID。
i created relationship diagram between id and parentID (on a same table), like simple tree, on the same table, but when i tried to user cascade delete it was disabled for me
我创建了id和parentID之间的关系图(在同一个表上)，就像在同一个表上的简单树一样，但是当我尝试用户级联删除时，它为我禁用了
i know that it will be recursive delete if i will delete parent it will delete his children
我知道，如果我删除父类，它会递归地删除它会删除它的子类
have i any options for anable cascade delete without triggers?
我有什么选项可以在没有触发器的情况下进行级联删除吗?
3 个解决方案
#1
3
No, SQL Server does not allow recursive and/or multiple cascade paths.
不，SQL Server不允许递归和/或多个级联路径。
You can use a stored procedure to delete bit by bit, or use a trigger. Sorry.
您可以使用存储过程逐位删除数据，或者使用触发器。对不起。
#2
1
best to use an after trigger for multiple actions.
最好对多个操作使用after触发器。
#3
1
One option that may work for you:
一个对你有用的选择:
- Place ParentID and ChildID in a separate table, remove ParentID from the base table
- 将ParentID和ChildID放在单独的表中，从基表中删除ParentID
- Cascade delete based solely on ID-->ParentID
- 级联删除仅基于ID——>ParentID
The problem is that when you delete a child, you won't delete the link from its parent, and when you delete a parent, you won't delete any links from its children to any grandchildren.
问题是，当你删除一个子代时，你不会从它的父类中删除这个链接，当你删除一个父类时，你也不会从它的子代中删除到任何子代的链接。
However, you can get around this in your app by always using an INNER JOIN against both the ParentID and ChildID, so any leftover fluff in the Parent/Child table will be ignored.
但是，您可以在应用程序中通过始终使用内部连接来对付ParentID和ChildID来绕过这个问题，因此父/子表中所有剩余的绒毛都将被忽略。
You can then run a stored procedure on whatever timed basis you want to clean out any Parent/Child relationships where either the parent or child do not exist. Rinse and repeat each time you run it, since a simple DELETE FROM parentchild WHERE NE(parent) OR NE(child) won't be recursive.
然后，您可以运行一个存储过程，在任何您希望清除父或子不存在的任何父/子关系的定时基础上。每次运行时都要清洗并重复一次，因为从parentchild简单删除NE(父)或NE(子)将不会递归。
#1
3
No, SQL Server does not allow recursive and/or multiple cascade paths.
不，SQL Server不允许递归和/或多个级联路径。
You can use a stored procedure to delete bit by bit, or use a trigger. Sorry.
您可以使用存储过程逐位删除数据，或者使用触发器。对不起。
#2
1
best to use an after trigger for multiple actions.
最好对多个操作使用after触发器。
#3
1
One option that may work for you:
一个对你有用的选择:
- Place ParentID and ChildID in a separate table, remove ParentID from the base table
- 将ParentID和ChildID放在单独的表中，从基表中删除ParentID
- Cascade delete based solely on ID-->ParentID
- 级联删除仅基于ID——>ParentID
The problem is that when you delete a child, you won't delete the link from its parent, and when you delete a parent, you won't delete any links from its children to any grandchildren.
问题是，当你删除一个子代时，你不会从它的父类中删除这个链接，当你删除一个父类时，你也不会从它的子代中删除到任何子代的链接。
However, you can get around this in your app by always using an INNER JOIN against both the ParentID and ChildID, so any leftover fluff in the Parent/Child table will be ignored.
但是，您可以在应用程序中通过始终使用内部连接来对付ParentID和ChildID来绕过这个问题，因此父/子表中所有剩余的绒毛都将被忽略。
You can then run a stored procedure on whatever timed basis you want to clean out any Parent/Child relationships where either the parent or child do not exist. Rinse and repeat each time you run it, since a simple DELETE FROM parentchild WHERE NE(parent) OR NE(child) won't be recursive.
然后，您可以运行一个存储过程，在任何您希望清除父或子不存在的任何父/子关系的定时基础上。每次运行时都要清洗并重复一次，因为从parentchild简单删除NE(父)或NE(子)将不会递归。