SQL Server级联 [英] SQL Server Cascading

问题描述

我正在制作一个网站，用户可以发布帖子，然后用户可以对这些帖子进行评论。我有一个有3个表的数据库。一个包含用户信息，一个包含发布信息，最后一个包含评论信息。

I am making a website where users can post 'Posts' and then users can 'Comment' on these posts. I have a database with 3 tables. One contains User Information, one contains Post Information and the final one contains Comment Information.

我想设置规则，以便如果用户被删除，他们的所有帖子并删除注释，如果用户删除其中一个帖子，则所有相关联的注释都将被删除。然而，这设置了多个CASCADE路径。

I want to set up rules so that if a User is deleted, all of their posts and comments are deleted, and if the user deletes one of their posts then all of the associated comments are deleted. This however sets up 'Multiple CASCADE paths'.

我一直在寻找一个解决方案，并找到一些有关触发器的信息。他们会使用最好的东西吗？如果我使用它们，我是否必须更改所有由触发器完成的CASCAD？

I have been looking for a solution and found some information on triggers. Would they be the best thing to use? If I used them, would I have to change all of the CASCADES to be done by triggers?

谢谢

Clivest

推荐答案

另一个选择是创建两个专用的存储过程，根据需要执行这些删除步骤

Another option would be to create two dedicated stored procedures that would do these delete steps as needed

CREATE PROCEDURE dbo.DeleteUser @UserID VARCHAR(50)
AS BEGIN
   DELETE FROM dbo.Comments
   WHERE Author = @UserID

   DELETE FROM dbo.Posts
   WHERE Author = @UserID

   DELETE FROM dbo.User
   WHERE UserID = @UserID
END

，第二个规则：

CREATE PROCEDURE dbo.DeletePost @PostID INT
AS BEGIN
   DELETE FROM dbo.Comments
   WHERE PostID = @PostID

   DELETE FROM dbo.Posts
   WHERE ID = @PostID
END

在这种情况下，您可以完全控制真正发生的情况，级联删除没有意外的惊喜，也不需要使用触发器。

In this case, you have total control over what really happens, there's no unexpected surprises from cascading deletes, and no need to use triggers either.

这篇关于SQL Server级联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！