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

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