Solving ON DELETE CASCADE cycles with triggers on MS SQL Server

I have code that is working fine in PostgreSQL and I now have to port it to MS SQL Server. It involves tables with potential cycles on delete/update events and SQL Server is complaining about it:

-- TABLE t_parent
CREATE TABLE t_parent (m_id INT IDENTITY PRIMARY KEY NOT NULL, m_name nvarchar(450));

-- TABLE t_child
CREATE TABLE t_child (m_id INT IDENTITY PRIMARY KEY NOT NULL, m_name nvarchar(450),
    id_parent int CONSTRAINT fk_t_child_parent FOREIGN KEY REFERENCES t_parent(m_id)
    --ON DELETE CASCADE ON UPDATE CASCADE
);

-- TABLE t_link
CREATE TABLE t_link (m_id INT IDENTITY PRIMARY KEY NOT NULL,
    id_parent int CONSTRAINT fk_t_link_parent FOREIGN KEY REFERENCES t_parent(m_id)
    -- ON DELETE CASCADE ON UPDATE CASCADE
    , id_child int CONSTRAINT fk_t_link_child FOREIGN KEY REFERENCES t_child(m_id)
    -- ON DELETE SET NULL ON UPDATE CASCADE
    , link_name nvarchar(450));

I have commented out the ON DELETE/UPDATE constraints that were accepted by PostgreSQL, which show the exact behavior I'm trying to reproduce in MS SQL Server, otherwise I'm getting the error:


Introducing FOREIGN KEY constraint 'fk_t_link_child' on table 't_link' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

So I removed them (equivalent to NO ACTION from the documentation) and decided to go the trigger way (as hinted by several sites) to delete related t_link rows when the related t_parent is deleted:

CREATE TRIGGER trg_delete_CASCADE_t_link_id_parent ON t_parent AFTER DELETE AS BEGIN
    DELETE FROM t_link WHERE id_parent IN (SELECT m_id FROM DELETED)
END;

What I'm trying to have overall is:

• all t_child records deleted when their related t_parent record is deleted (ON DELETE CASCADE), and t_link records related to deleted t_child deleted as well

• all t_link records deleted when their related t_parent record is deleted (ON DELETE CASCADE)

• `t

You're close. AFTER triggers happen after foreign key constraint checking. So you need an INSTEAD OF trigger. That way you can modify the child tables before performing the DELETE on the target table.

eg

-- TABLE t_parent
CREATE TABLE t_parent 
(
  m_id INT IDENTITY PRIMARY KEY NOT NULL, 
  m_name nvarchar(450)
);

-- TABLE t_child
CREATE TABLE t_child 
(
    m_id INT IDENTITY PRIMARY KEY NOT NULL, 
    m_name nvarchar(450),
    id_parent int CONSTRAINT fk_t_child_parent FOREIGN KEY REFERENCES t_parent(m_id)
      ON DELETE CASCADE ON UPDATE CASCADE
);

-- TABLE t_link
CREATE TABLE t_link (m_id INT IDENTITY PRIMARY KEY NOT NULL,
    id_parent int CONSTRAINT fk_t_link_parent FOREIGN KEY REFERENCES t_parent(m_id)
      ON DELETE NO ACTION
    , id_child int CONSTRAINT fk_t_link_child FOREIGN KEY REFERENCES t_child(m_id)
      ON DELETE CASCADE
    , link_name nvarchar(450));

    go

CREATE OR ALTER TRIGGER trg_delete_CASCADE_t_link_id_parent 
ON t_parent INSTEAD OF DELETE 
AS 
BEGIN

    SET NOCOUNT ON
    DELETE FROM t_link WHERE id_parent IN (SELECT m_id FROM DELETED);
    DELETE FROM t_parent WHERE m_id IN (SELECT m_id FROM DELETED);

END;

go

insert into t_parent (m_name) values('toto');
insert into t_link (id_parent, id_child, link_name) values (1, NULL, 'chan');
delete from t_parent where m_id = 1;

This way t_parent->t_child->t_link uses CASCADE DELETES, and t_parent->t_link is handled by the INSTEAD OF trigger.

StackExchange Database Administrators Q#251786, answer score: 4