Two foreign keys on same table: how to implement on delete cascade?

Question

I have a table with two columns. Each of them is a foreign key to the same second table:

CREATE TABLE [dbo].[TBL_TOGETHER]
(
    [ID1] [int] NULL FOREIGN KEY REFERENCES [TBL_ANOTHER_TABLE](ID),
    [ID2] [int] NULL FOREIGN KEY REFERENCES [TBL_ANOTHER_TABLE](ID)
)

But now I can not delete entities from TBL_ANOTHER_TABLE anymore:

The DELETE statement conflicted with the REFERENCE constraint "FK__TBL_ASD__4DE98D56".

My question is: what is the best solution to implement ON DELETE CASCADE at TBL_TOGETHER?

I tried to add ON DELETE SET NULL or ON DELETE CASCADE on the foreign key, but it does not work because of cycle or multiple cascade paths.

I have tried to add a delete trigger on TBL_ANOTEHR_TABLE, but it still conflicts with the foreign key:

ALTER TRIGGER REMOVE_FORENGKEY
ON TBL_ANOTHER_TABLE 
FOR DELETE
AS
BEGIN
    UPDATE TBL_TOGETHER
    SET TBL_TOGETHER.ID1 = NULL
    FROM TBL_TOGETHER
    JOIN deleted ON TBL_TOGETHER.ID1 = deleted.ID;

    UPDATE TBL_TOGETHER
    SET TBL_TOGETHER.ID2 = NULL
    FROM TBL_TOGETHER
    JOIN deleted ON TBL_TOGETHER.ID2 = deleted.ID;
END

I have tried an INSTEAD OF DELETE-trigger but this approach also doesn't work because the referenced table ALSO contains a foreign key with cascade constraints.

ALTER TRIGGER REMOVE_FORENGKEY
ON TBL_ANOTHER_TABLE 
INSTEAD OF DELETE
AS
BEGIN
    UPDATE TBL_TOGETHER
    SET TBL_TOGETHER.ID1 = NULL
    FROM TBL_TOGETHER
    JOIN deleted ON TBL_TOGETHER.ID1 = deleted.ID;

    UPDATE TBL_TOGETHER
    SET TBL_TOGETHER.ID2 = NULL
    FROM TBL_TOGETHER
    JOIN deleted ON TBL_TOGETHER.ID2 = deleted.ID;

    DELETE TBL_ANOTHER_TABLE 
    FROM TBL_ANOTHER_TABLE 
    JOIN deleted ON TBL_ANOTHER_TABLE.ID = deleted.ID 
    WHERE TBL_ANOTHER_TABLE.ID = deleted.ID;
END

Error:

Cannot alter INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'REMOVE_FORENGKEY' on table 'TBL_ANOTHER_TABLE '. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE.

Answer 1

use this trigger : this trigger acts instead of delete. therefore you must delete record(s) after set references to null.

 CREATE TRIGGER Trigger_TBL_ANOTHER_TABLE
    ON [dbo].[TBL_ANOTHER_TABLE]
    INSTEAD OF DELETE
    AS 
    BEGIN

        SET NOCOUNT ON;

        UPDATE  t
        SET     t.ID1 = NULL
        from [dbo].[TBL_TOGETHER] t inner join
        deleted d on d.ID = t.ID1

        UPDATE  t
        SET     t.ID2 = NULL
        from [dbo].[TBL_TOGETHER] t inner join
        deleted d on d.ID = t.ID2

        DELETE  t
        from [dbo].[TBL_ANOTHER_TABLE] t inner join
        deleted d on d.ID = t.ID

    END
    GO