开发者

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

开发者 https://www.devze.com 2023-01-28 14:06 出处:网络
It would appear that, at least through management studio express, it is not possible to set a foreign key constraint that references the same table to do anything on update or delete.

It would appear that, at least through management studio express, it is not possible to set a foreign key constraint that references the same table to do anything on update or delete. I have a table where I would like to cascade updates to null if a row is removed.

Is this poss开发者_如何学JAVAible?

Thanks,


You would need to handle this situation with an INSTEAD OF DELETE trigger.

Something like:

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
0

精彩评论

暂无评论...
验证码 换一张
取 消