在 MS SQL Server 上使用觸發器解決 ON DELETE CASCADE 循環

我的程式碼在 PostgreSQL 中執行良好，現在我必須將它移植到 MS SQL Server。它涉及具有潛在刪除/更新事件週期的表，SQL Server 抱怨它：

-- TABLE t_parent
CREATE TABLE t_parent (m_id INT IDENTITY PRIMARY KEY NOT NULL, m_name nvarchar(450));

-- TABLE t_child
CREATE TABLE t_child (m_id INT IDENTITY PRIMARY KEY NOT NULL, m_name nvarchar(450),
   id_parent int CONSTRAINT fk_t_child_parent FOREIGN KEY REFERENCES t_parent(m_id)
   --ON DELETE CASCADE ON UPDATE CASCADE
);

-- TABLE t_link
CREATE TABLE t_link (m_id INT IDENTITY PRIMARY KEY NOT NULL,
   id_parent int CONSTRAINT fk_t_link_parent FOREIGN KEY REFERENCES t_parent(m_id)
   -- ON DELETE CASCADE ON UPDATE CASCADE
   , id_child int CONSTRAINT fk_t_link_child FOREIGN KEY REFERENCES t_child(m_id)
   -- ON DELETE SET NULL ON UPDATE CASCADE
   , link_name nvarchar(450));

我已經註釋掉了ON DELETE/UPDATEPostgreSQL 接受的約束，這些約束顯示了我試圖在 MS SQL Server 中重現的確切行為，否則我會收到錯誤消息：

在表 ’t_link’ 上引入 FOREIGN KEY 約束 ‘fk_t_link_child’ 可能會導致循環或多個級​​聯路徑。指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 約束。

所以我刪除了它們（相當於NO ACTION從文件中刪除）並決定在刪除相關行時採用觸發方式（如幾個站點所暗示的那樣）刪除相關t_link行t_parent：

CREATE TRIGGER trg_delete_CASCADE_t_link_id_parent ON t_parent AFTER DELETE AS BEGIN
   DELETE FROM t_link WHERE id_parent IN (SELECT m_id FROM DELETED)
END;

我總體上想要的是：

• t_child當相關記錄被刪除時，所有記錄t_parent都被刪除（ON DELETE CASCADE），與t_link已刪除相關的記錄也被t_child刪除
• t_link刪除相關記錄時刪除的所有記錄t_parent( ON DELETE CASCADE)
• t_link.id_child設置為何時刪除或NULL刪除他們的相關t_child記錄 ，如果它使事情變得更容易（或）ON DELETE SET NULL``ON DELETE CASCADE

然後我插入一些測試數據並嘗試：

insert into t_parent (m_name) values('toto');
insert into t_link (id_parent, id_child, link_name) values (1, NULL, 'chan');
delete from t_parent where m_id = 1;

錯誤：DELETE 語句與 REFERENCE 約束“fk_t_link_parent”衝突。衝突發生在數據庫“DBTest”、表“dbo.t_link”、列“id_parent”中。

我猜問題是我的觸發器沒有被呼叫，因為它發生在刪除本身之後，上面的消息失敗了；並且沒有BEFORE DELETE觸發器類型（聽起來像是我想要的）。

現在我不得不說 SQL 都是由一個類似 Java JPA 的程序生成的，該程序必須處理不同的 DBMS（PostgreSQL 的一個子類，SQL Server 的一個子類……）所以我應該保持通用：我可以tON DELETE CASCADE對一個表施加約束並與其他表一起使用觸發器（或您可能知道的任何其他方法）（我可以，但以我試圖避免的程式碼過度複雜化為代價）。

SQL Server 是一個Docker 映像，所以我不確定我是否可以在某處進行調試輸出（除非在sqlcmd命令中）。如果有任何相關性，則版本為 2017。

我看到的唯一解決方法就是刪除參考約束並使用觸發器手動處理它。但是：外鍵約束有什麼意義呢？

---

編輯：在大衛的回答之後，我應該澄清幾點：

和SQL 由程式碼生成CREATE TABLE，CREATE TRIGGER每次添加新表時（來自與 SQL 無關的配置文件）。由於 SQL Server 可能會因為潛在的循環而拒絕創建ON DELETE CASCADE約束，所以我決定只指出FOREIGN KEY約束，然後讓每個引用的表t_parent創建一個FOR DELETE觸發器，每個表都對自己的行執行 CASCADE 或 SET NULL 操作。

建議的INSTEAD OF DELETE觸發器絕對是我正在尋找的機制，但只能為表創建此類觸發器的單個實例（這是有道理的），所以我沒有那樣做。

我最終可能會創建儲存過程而不是目前的觸發器，並在每次添加新的引用表（和過程）時更新 INSTEAD OF 觸發器，呼叫每個儲存過程。

你很近。 外鍵約束檢查後AFTER觸發。所以你需要一個觸發器。這樣，您可以在對目標表執行 DELETE 之前修改子表。INSTEAD OF

例如

-- TABLE t_parent
CREATE TABLE t_parent 
(
 m_id INT IDENTITY PRIMARY KEY NOT NULL, 
 m_name nvarchar(450)
);

-- TABLE t_child
CREATE TABLE t_child 
(
   m_id INT IDENTITY PRIMARY KEY NOT NULL, 
   m_name nvarchar(450),
   id_parent int CONSTRAINT fk_t_child_parent FOREIGN KEY REFERENCES t_parent(m_id)
     ON DELETE CASCADE ON UPDATE CASCADE
);

-- TABLE t_link
CREATE TABLE t_link (m_id INT IDENTITY PRIMARY KEY NOT NULL,
   id_parent int CONSTRAINT fk_t_link_parent FOREIGN KEY REFERENCES t_parent(m_id)
     ON DELETE NO ACTION
   , id_child int CONSTRAINT fk_t_link_child FOREIGN KEY REFERENCES t_child(m_id)
     ON DELETE CASCADE
   , link_name nvarchar(450));


   go

CREATE OR ALTER TRIGGER trg_delete_CASCADE_t_link_id_parent 
ON t_parent INSTEAD OF DELETE 
AS 
BEGIN

   SET NOCOUNT ON
   DELETE FROM t_link WHERE id_parent IN (SELECT m_id FROM DELETED);
   DELETE FROM t_parent WHERE m_id IN (SELECT m_id FROM DELETED);

END;

go

insert into t_parent (m_name) values('toto');
insert into t_link (id_parent, id_child, link_name) values (1, NULL, 'chan');
delete from t_parent where m_id = 1;

這種方式 t_parent->t_child->t_link 使用 CASCADE DELETES，並且 t_parent->t_link 由 INSTEAD OF 觸發器處理。

為了簡化 SQL 生成器，我最終刪除了所有外鍵約束並AFTER DELETE在表上使用觸發器來更好地解決t_link記錄的刪除問題（我想在它們parent 和 child被刪除/NULL 時刪除它們）：

CREATE TABLE t_parent (m_id INT IDENTITY PRIMARY KEY NOT NULL, 
   m_name NVARCHAR(450));

t_child表失去它CONSTRAINT xxx FOREIGN KEY，替換為AFTER DELETE​​觸發器：

CREATE TABLE t_child (m_id INT IDENTITY PRIMARY KEY NOT NULL, 
   id_parent INT,
   m_name NVARCHAR(450));

-- ON DELETE SET NULL equivalent
CREATE TRIGGER trg_delete_nulls_t_child_t_parent ON t_parent AFTER DELETE AS BEGIN
   UPDATE t_child SET id_parent = NULL WHERE id_parent IN (SELECT m_id FROM DELETED);
END;

同樣 for t_link，如果兩者和都是 NULL （即已刪除/不存在） ，則ON DELETE SET NULL提升為：CASCADE``id_parent id_child

CREATE TABLE t_link (m_id INT IDENTITY PRIMARY KEY NOT NULL,
   id_parent INT, id_child INT,
   link_name NVARCHAR(450));

-- Trigger ON DELETE SET NULL when t_parent is deleted, or ON DELETE CASCADE if no linked t_child
CREATE TRIGGER trg_delete_nulls_t_link_t_parent ON t_parent AFTER DELETE AS BEGIN
   -- "Promotion" to CASCADE if id_child is also NULL
   DELETE FROM t_link WHERE id_child IS NULL AND id_parent IN (SELECT m_id FROM DELETED);
   -- ON DELETE SET NULL (that might not be triggered if the previous statement has deleted all the records)
   UPDATE t_link SET id_parent = NULL WHERE id_parent IN (SELECT m_id FROM DELETED);
END;

-- Same for t_child deletions vs. t_parent
CREATE TRIGGER trg_delete_nulls_t_link_t_child ON t_child AFTER DELETE AS BEGIN
   UPDATE t_link SET id_child = NULL WHERE id_child IN (SELECT m_id FROM DELETED);
   DELETE FROM t_link WHERE id_parent IS NULL AND id_child IN (SELECT m_id FROM DELETED);
END;

當然，這可能不如手工約束效率低，但是（到目前為止）對於我的案例來說已經足夠好了，並且大大簡化了 SQL 生成器程式碼。

我猜有一些“灰色”行為，例如id_child刪除記錄DELETE FROM t_link ...（UPDATE t_link SET id_child=NULL

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