sql-server - SQL Server : Is it possible to cascade update a self referencing table?

Question

看起来，至少通过 management studio express，不可能设置引用同一个表的外键约束来执行任何更新或删除操作。
我有一个表，如果删除一行，我想将更新级联到 null。

这可能吗？

谢谢，

Answer 1

您需要使用 INSTEAD OF 来处理这种情况。删除触发器。

就像是:

CREATE TRIGGER tr_IOD_YourTable ON YourTable
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON

    UPDATE yt
        SET ChildForeignKey = NULL
        FROM deleted d
            INNER JOIN YourTable yt
                ON d.PrimaryKeyColumn = yt.ChildForeignKey

    DELETE FROM yt
        FROM deleted d
            INNER JOIN YourTable yt
                ON d.PrimaryKeyColumn = yt.PrimaryKeyColumn
END