Im going through a project I have taken over, and on the database side I have noticed that the previous programmers have written a bunch of triggers to delete child records. The thing is, these records already have a a foreign key relationship with the parent record I am deleting. The delete triggers are nothing but simple delete statements for the child records.
我正在进行我接手的一个项目，在数据库方面，我注意到以前的程序员编写了一些触发器来删除子记录。问题是，这些记录已经与我删除的父记录有一个外键关系。delete触发器只是子记录的简单的delete语句。
Is there a benefit to writing a trigger to delete child records, or can I just change it to cascade on delete and be fine?
写一个删除子记录的触发器有什么好处吗?或者我可以把它改成在删除时进行级联操作吗?
Im using MSSQL 2008.
我使用该软件2008。
4 个解决方案
#1
15
CASCADE DELETE in MSSQL Server can only cascade to a single table. If you have two tables with foreign key relationships to a dimension table, you can only cascade delete to one of them. (This is to prevent deletes cascading through multiple paths and creating conflicts, much as C++ allows multiple inheritance but C# only allows single inheritance)
MSSQL服务器中的级联删除只能级联到单个表。如果有两个表具有维度表的外键关系，那么只能将delete级联到其中一个表中。(这是为了防止删除通过多个路径的级联和创建冲突，就像c++允许多个继承，而c#只允许单个继承一样)
When this is the case, you are forced to use triggers or specifically handle the case in your code.
当出现这种情况时，您将*使用触发器或在代码中专门处理这种情况。
For this reason I have seen many people opt for using triggers in all cases. Even when there is only one foreign table. This ensures consistency and so people know what to look for when maintaining the database.
由于这个原因，我看到许多人选择在所有情况下使用触发器。即使只有一个外表。这确保了一致性，因此人们知道在维护数据库时应该寻找什么。
If one could cascade a delete to more than one table I would say it would be the most preferable option. This limitation, however, muddies the waters and I'm currently more in favour of triggers owning all such behaviours. The overhead in using triggers for cascaded deletes and updates is only minor in terms of coding, but does allow for standard practices that are truely generic.
如果可以将一个删除操作串接到多个表中，我认为这将是最可取的选择。然而，这种限制使事情变得混乱，我现在更倾向于触发所有这些行为。对于级联删除和更新使用触发器的开销在编码方面只是很小的一部分，但确实允许真正通用的标准实践。
EDIT:
编辑:
You might want to move the 'accepted answer' to someone else, I've worked out I was wrong abot the above.
你可能想把“公认的答案”转移到别人身上，我发现我在上面说的上面错了。
You CAN have multiple fact tables have ON DELETE CASCADE Foreign Key Contraints to a signle Dimension table.
您可以让多个事实表具有删除级联外键约束到一个显著维度表。
What you Can't do is have one Fact Table have have ON DELETE CASCADE Foreign Key Constraints to multiple Dimension Tables.
您不能做的是，有一个事实表具有对多个维度表的删除级联外键约束。
So for example...
- Dimension Table [Person] (id INT IDENTITY, )
- Dimension Table [Exam] (id INT IDENTITY, )
- Face Table [Exam_Score] (person_id INT, exam_id INT, score INT)
举个例子…-维度表[Person] (id INT IDENTITY，) -维度表[考试](id INT IDENTITY，) - Face Table [Exam_Score] (person_id INT, exam_id INT, score INT)
If either the Person or the Exam are deleted, you'd want the associated Exam_Score record(s) to also be deleted.
如果这个人或者考试被删除了，你会希望相关的考试成绩记录也被删除。
This is not possible using ON DELETE CASCADE in MS SQL Server, thus the need for triggers.
在MS SQL Server中，不可能使用DELETE CASCADE，因此需要触发器。
(Apologies to Mehrdad who tried to explain this to me but I completely missed his point.)
(向试图向我解释此事的梅尔达德道歉，但我完全没有理解他的意思。)
#2
10
Stay away from unnecessary triggers.
远离不必要的触发器。
Go with ON DELETE CASCADE if that's all you need to do.
如果你需要做的就是删除级联。
#3
1
I would use cascade on delete, but that is only if you definitely want to delete the child if the parent is deleted.
我将在delete中使用级联，但这只是如果您在删除父节点时确实想要删除它。
If you have any conditional logic (I only delete the child if deleted on a Sunday) then use a trigger.
如果您有任何条件逻辑(我只删除周日删除的子逻辑)，那么使用触发器。
I would just change it to cascade on delete, on a development system, then run my unit tests and make certain that nothing breaks.
在开发系统上，我只需将它更改为cascade on delete，然后运行我的单元测试，确保没有任何东西损坏。
#4
1
I almost agree with Dems here except I use ON DELETE CASCADE (and ON UPDATE CASCADE for that matter) referential actions wherever possible and only resort to using triggers where necessary. I'd also consider revoking the permissions from the tables and forcing the use of 'helper' stored procs for deletes and updates.
我几乎同意这里的调制解调器，除了我在删除级联(以及更新级联)中尽可能使用引用操作，并且只在必要时使用触发器。我还考虑从表中撤销权限，并强制使用“helper”存储的procs进行删除和更新。
Call me an optimist but I believe a) my code will survive porting to a future release of MS SQL Server and b) the SQL Server team will one day soon get around to fixing the 'one cascade path' limitation, at which point I'll replace the triggers with cascade referential actions :)
叫我一个乐观主义者,但我相信我的代码移植到将来的版本的MS SQL Server和b)SQL Server团队很快就会有一天去修复一个级联路径的限制,此时我将取代触发级联引用行为:)
#1
15
CASCADE DELETE in MSSQL Server can only cascade to a single table. If you have two tables with foreign key relationships to a dimension table, you can only cascade delete to one of them. (This is to prevent deletes cascading through multiple paths and creating conflicts, much as C++ allows multiple inheritance but C# only allows single inheritance)
MSSQL服务器中的级联删除只能级联到单个表。如果有两个表具有维度表的外键关系，那么只能将delete级联到其中一个表中。(这是为了防止删除通过多个路径的级联和创建冲突，就像c++允许多个继承，而c#只允许单个继承一样)
When this is the case, you are forced to use triggers or specifically handle the case in your code.
当出现这种情况时，您将*使用触发器或在代码中专门处理这种情况。
For this reason I have seen many people opt for using triggers in all cases. Even when there is only one foreign table. This ensures consistency and so people know what to look for when maintaining the database.
由于这个原因，我看到许多人选择在所有情况下使用触发器。即使只有一个外表。这确保了一致性，因此人们知道在维护数据库时应该寻找什么。
If one could cascade a delete to more than one table I would say it would be the most preferable option. This limitation, however, muddies the waters and I'm currently more in favour of triggers owning all such behaviours. The overhead in using triggers for cascaded deletes and updates is only minor in terms of coding, but does allow for standard practices that are truely generic.
如果可以将一个删除操作串接到多个表中，我认为这将是最可取的选择。然而，这种限制使事情变得混乱，我现在更倾向于触发所有这些行为。对于级联删除和更新使用触发器的开销在编码方面只是很小的一部分，但确实允许真正通用的标准实践。
EDIT:
编辑:
You might want to move the 'accepted answer' to someone else, I've worked out I was wrong abot the above.
你可能想把“公认的答案”转移到别人身上，我发现我在上面说的上面错了。
You CAN have multiple fact tables have ON DELETE CASCADE Foreign Key Contraints to a signle Dimension table.
您可以让多个事实表具有删除级联外键约束到一个显著维度表。
What you Can't do is have one Fact Table have have ON DELETE CASCADE Foreign Key Constraints to multiple Dimension Tables.
您不能做的是，有一个事实表具有对多个维度表的删除级联外键约束。
So for example...
- Dimension Table [Person] (id INT IDENTITY, )
- Dimension Table [Exam] (id INT IDENTITY, )
- Face Table [Exam_Score] (person_id INT, exam_id INT, score INT)
举个例子…-维度表[Person] (id INT IDENTITY，) -维度表[考试](id INT IDENTITY，) - Face Table [Exam_Score] (person_id INT, exam_id INT, score INT)
If either the Person or the Exam are deleted, you'd want the associated Exam_Score record(s) to also be deleted.
如果这个人或者考试被删除了，你会希望相关的考试成绩记录也被删除。
This is not possible using ON DELETE CASCADE in MS SQL Server, thus the need for triggers.
在MS SQL Server中，不可能使用DELETE CASCADE，因此需要触发器。
(Apologies to Mehrdad who tried to explain this to me but I completely missed his point.)
(向试图向我解释此事的梅尔达德道歉，但我完全没有理解他的意思。)
#2
10
Stay away from unnecessary triggers.
远离不必要的触发器。
Go with ON DELETE CASCADE if that's all you need to do.
如果你需要做的就是删除级联。
#3
1
I would use cascade on delete, but that is only if you definitely want to delete the child if the parent is deleted.
我将在delete中使用级联，但这只是如果您在删除父节点时确实想要删除它。
If you have any conditional logic (I only delete the child if deleted on a Sunday) then use a trigger.
如果您有任何条件逻辑(我只删除周日删除的子逻辑)，那么使用触发器。
I would just change it to cascade on delete, on a development system, then run my unit tests and make certain that nothing breaks.
在开发系统上，我只需将它更改为cascade on delete，然后运行我的单元测试，确保没有任何东西损坏。
#4
1
I almost agree with Dems here except I use ON DELETE CASCADE (and ON UPDATE CASCADE for that matter) referential actions wherever possible and only resort to using triggers where necessary. I'd also consider revoking the permissions from the tables and forcing the use of 'helper' stored procs for deletes and updates.
我几乎同意这里的调制解调器，除了我在删除级联(以及更新级联)中尽可能使用引用操作，并且只在必要时使用触发器。我还考虑从表中撤销权限，并强制使用“helper”存储的procs进行删除和更新。
Call me an optimist but I believe a) my code will survive porting to a future release of MS SQL Server and b) the SQL Server team will one day soon get around to fixing the 'one cascade path' limitation, at which point I'll replace the triggers with cascade referential actions :)
叫我一个乐观主义者,但我相信我的代码移植到将来的版本的MS SQL Server和b)SQL Server团队很快就会有一天去修复一个级联路径的限制,此时我将取代触发级联引用行为:)