MSSQL: how to have multiple cascades with referencing the same table

Question

Maybe a complicated situation but this is a simplified version of my model:

Situation:

drop table table4
drop table table2
drop table table3
drop table table1
drop table table0

create table table0 (
    id integer not null primary key
)

create table table1 (
    id integer not null primary key
)

create table table2 (
    id integer not null primary key,
    table0_id integer not null,
    table1_id integer not null
)

create table table3 (
    id integer not null primary key,
    table1_id integer not null
)

create table table4 (
    id integer not null primary key,
    table2_id integer not null,
    table3_id integer not null
)

alter table table2 add constraint fk_table2_table0 foreign key (table0_id)
references table0 (id) on delete cascade on update no action

alter table table2 add constraint fk_table2_table1 foreign key (table1_id)
references table1 (id) on delete cascade on update no action

alter table table3 add constraint fk_table3_table1 foreign key (table1_id)
references table1(id) on delete cascade on update no action

alter table table4 add constraint fk_table4_table2 foreign key (table2_id)
references table2(id) on delete cascade on update no action

alter table table4 add constraint fk_table4_table3 foreign key (table3_id)
references table3(id) on delete no action on update no action

GO
CREATE TRIGGER WhenRowFromTable3IsDeleted ON table3
    FOR DELETE       
AS
BEGIN
    DELETE FROM table4 WHERE table3_id = (SELECT id FROM DELETED)
END
GO

INSERT INTO table0 (id) VALUES (1)
INSERT INTO table1 (id) VALUES (1)
INSERT INTO table2 (id, table0_id, table1_id) VALUES (1, 1, 1)
INSERT INTO table3 (id, table1_id) VALUES (1, 1)
INSERT INTO table4 (id, table2_id, table3_id) VALUES (1,1,1)

DELETE FROM table3 WHERE id = 1

SELECT * FROM table1, table0

Results in: The DELETE statement conflicted with the REFERENCE constraint "fk_table4_table3". The conflict occurred in database "testing", table "dbo.table4", column 'table3_id'.

Question

How to make it possible to remove a record from table3? I have tried it with trigger FOR DELETE as you can see but this results in a FK constraint error (so the FOR DELETE is actually a AFTER DELETE). I also tried too use the INSTEAD DELETE but this can't be used because the parent (table2) also got a ON DELETE CASCADE.

Answer 1

By creating a trigger that first deletes the sub-level table records and then itself after using the instead of delete you can trigger the sub triggers like a cascading pattern executing in the right order.

This solves the issue of cascade by adding a little more logic. Also you may want to use the primary key instead of the ID here for correct record selections on bigger keys.

drop table table4

drop table table2

drop table table3

drop table table1

drop table table0



create table table0 (

   id integer not null primary key

)



create table table1 (

   id integer not null primary key

)



create table table2 (

   id integer not null primary key,

   table0_id integer not null,

   table1_id integer not null

)



create table table3 (

   id integer not null primary key,

   table1_id integer not null

)



create table table4 (

   id integer not null primary key,

   table2_id integer not null,

   table3_id integer not null

)



alter table table2 add constraint fk_table2_table0 foreign key (table0_id)

references table0 (id) on delete cascade on update no action



alter table table2 add constraint fk_table2_table1 foreign key (table1_id)

references table1 (id) on delete cascade on update no action



alter table table3 add constraint fk_table3_table1 foreign key (table1_id)

references table1(id) on delete no action on update no action



alter table table4 add constraint fk_table4_table2 foreign key (table2_id)

references table2(id) on delete cascade on update no action



alter table table4 add constraint fk_table4_table3 foreign key (table3_id)

references table3(id) on delete no action on update no action



GO

CREATE TRIGGER WhenRowFromTable3IsDeleted ON table3

   INSTEAD OF DELETE      

AS

BEGIN

   DELETE FROM table4 WHERE table3_id = (SELECT id FROM DELETED)

    DELETE FROM table3 WHERE id = (SELECT id FROM DELETED)

END

GO

CREATE TRIGGER WhenRowFromTable1IsDeleted ON table1

   INSTEAD OF DELETE      

AS

BEGIN

   DELETE FROM table3 WHERE table1_id = (SELECT id FROM DELETED)

    DELETE FROM table1 WHERE id = (SELECT id FROM DELETED)

END

GO



INSERT INTO table0 (id) VALUES (1)

INSERT INTO table1 (id) VALUES (1)

INSERT INTO table2 (id, table0_id, table1_id) VALUES (1, 1, 1)

INSERT INTO table3 (id, table1_id) VALUES (1, 1)

INSERT INTO table4 (id, table2_id, table3_id) VALUES (1,1,1)



DELETE FROM table3 WHERE id = 1



SELECT * FROM table1, table0