SQL Server: is it possible to cascade update its own binding table?

Question

It seems that, at least with the express management studio, it is not possible to set a foreign key constraint that refers to the same table in order to do anything when updating or deleting. I have a table where I would like to cascade updates to zero if the row is deleted.

Is it possible?

Thank,

Answer 1

You will need to handle this situation with INSTEAD OF DELETE.

Sort 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