幾個外鍵和級聯刪除SQL Server

在 SQL Server 2014 中，我試圖在 3 FK 上添加CASCADE DELETING（我想將欄位設置為 null，但相同）。如果我在一個關係中添加級聯刪除，它工作正常。如果我添加更多級聯刪除，它不起作用（循環檢測到錯誤消息）。

在上圖中，您可以看到使用者表和任務表（西班牙語中的“Tareas”）。所以，我需要完成的是，當使用者被刪除時，我需要將 Tasks 中的標記欄位設置為 NULL。

這在數據庫中很常見，所以我認為有一種方法可以處理這個問題。

就我而言，我的大多數表都有一對欄位，其中包含創建或修改記錄的使用者的 UserId。所以，我需要解決這個模式來將它應用到幾個地方。

您可以創建觸發器來更新相關表中的 UserID 列，例如：

USE Tempdb;

CREATE TABLE dbo.Users
(
   UserID INT NOT NULL CONSTRAINT PK_Users
       PRIMARY KEY CLUSTERED
   , ApplicationID INT NOT NULL
   , Country VARCHAR(255) NOT NULL
   , DefaultTenantID INT NOT NULL
);
GO
CREATE TABLE dbo.Tasks
(
   TaskID INT NOT NULL
   , TenantID INT NOT NULL
   , CreatedBy_UserID INT NULL
       CONSTRAINT FK_Tasks_CreatedBy
       FOREIGN KEY REFERENCES dbo.Users(UserID)
   , ModifiedBy_UserID INT NULL
       CONSTRAINT FK_Tasks_ModifiedBy
       FOREIGN KEY REFERENCES dbo.Users(UserID)
   , AssignedTo_UserID INT NULL
       CONSTRAINT FK_Tasks_AssignedTo
       FOREIGN KEY REFERENCES dbo.Users(UserID)
);
GO
INSERT INTO dbo.Users (UserID, ApplicationID, Country, DefaultTenantID)
VALUES (1, 1, 'CA', 1);

INSERT INTO dbo.Tasks (TaskID, TenantID, CreatedBy_UserID, ModifiedBy_UserID, AssignedTo_UserID)
VALUES (1, 1, 1, NULL, NULL);
GO

CREATE TRIGGER TG_Users_Trigger
ON dbo.Users
INSTEAD OF DELETE
AS
BEGIN
   UPDATE dbo.Tasks 
   SET CreatedBy_UserID = NULL 
   WHERE EXISTS (
       SELECT 1 
       FROM deleted d
       WHERE d.UserID = CreatedBy_UserID
       );
   UPDATE dbo.Tasks 
   SET ModifiedBy_UserID = NULL 
   WHERE EXISTS (
       SELECT 1 
       FROM deleted d
       WHERE d.UserID = ModifiedBy_UserID
       );
   UPDATE dbo.Tasks 
   SET AssignedTo_UserID = NULL 
   WHERE EXISTS (
       SELECT 1 
       FROM deleted d
       WHERE d.UserID = AssignedTo_UserID
       );
   DELETE
   FROM dbo.Users 
   WHERE EXISTS (
       SELECT 1 
       FROM deleted d
       WHERE d.UserID = UserID
       );
END
GO

DELETE 
FROM dbo.Users;

SELECT *
FROM dbo.Users;
SELECT *
FROM dbo.Tasks;

/* - cleanup removed for safety
DROP TRIGGER TG_Users_Trigger;
DROP TABLE dbo.Tasks;
DROP TABLE dbo.Users;
*/

但是，我建議不要從使用者表中刪除行，而是更新一個位列以指示使用者已被刪除。這允許使用者歷史記錄，以及這些使用者在任務表中執行的操作。例如：

CREATE TABLE dbo.Users
(
   UserID INT NOT NULL CONSTRAINT PK_Users
       PRIMARY KEY CLUSTERED
   , ApplicationID INT NOT NULL
   , Country VARCHAR(255) NOT NULL
   , DefaultTenantID INT NOT NULL
   , IsDeleted BIT NOT NULL /* Default this to false */
       CONSTRAINT DF_Users_IsDeleted DEFAULT ((0))
);

CREATE TABLE dbo.Tasks
(
   TaskID INT NOT NULL
   , TenantID INT NOT NULL
   , CreatedBy_UserID INT NULL
       CONSTRAINT FK_Tasks_CreatedBy
       FOREIGN KEY REFERENCES dbo.Users(UserID)
   , ModifiedBy_UserID INT NULL
       CONSTRAINT FK_Tasks_ModifiedBy
       FOREIGN KEY REFERENCES dbo.Users(UserID)
   , AssignedTo_UserID INT NULL
       CONSTRAINT FK_Tasks_AssignedTo
       FOREIGN KEY REFERENCES dbo.Users(UserID)
);

/* Note we are not specifying the state of the IsDeleted column here
   since it defaults to zero, which indicates they are active
*/
INSERT INTO dbo.Users (UserID, ApplicationID, Country, DefaultTenantID)
VALUES (1, 1, 'CA', 1);

INSERT INTO dbo.Tasks (TaskID, TenantID, CreatedBy_UserID, ModifiedBy_UserID, AssignedTo_UserID)
VALUES (1, 1, 1, NULL, NULL);

/*  This is how we "delete" a user */
UPDATE dbo.Users
SET IsDeleted = 1
WHERE UserID = 1;

/* Any queries that access the Tasks table now need to be cognizant
   of the state of the user (deleted or not)
*/
SELECT T.*
FROM dbo.Tasks T
   INNER JOIN dbo.Users U ON T.CreatedBy_UserID = U.UserID
WHERE U.IsDeleted = 0;

引用自：https://dba.stackexchange.com/questions/102308