I have a table (let's call it tMainTable) which is currently referred by many other table (and their number might just grow over time).
Several of those tables cannot just have the ON DELETE CASCADE clause due to cycling detection.
I know I could remove the ON DELETE CASCADE clause if I write an INSTEAD OF trigger on tMainTable.
But then whenever a new table is added as descendant of tMainTable one would have to edit the trigger which (as a "modularity" addict) is just lame (and risky - imagine an unexperienced user just breaks the whole thing).
So I'm seriously looking for an alternative way to proceed.
And I just have something in my mind.
What if I create a table tMainTableID which just hold a copy of every ID of tMainTable (copy made with AFTER trigger for insertion)?
Then, I create an INSTEAD OF trigger on tMainTable which first delete the corresponding row of tMainTableID, then delete the actual rows of tMainTable.
If I do that, I should have something really nice to work with: a table (tMainTableID) which I can "attach" AFTER DELETE triggers on to delete anything that needs to be deleted before rows of tMainTable get actually deleted.
-- Made by ME on 2014-01-27
CREATE TRIGGER tMainTableID_AFDEL_tTableChild01
AFTER DELETE
ON tMainTableID
AS
BEGIN
DELETE T
FROM deleted AS D
INNER JOIN tTableChild01 AS T ON (
T.RefToMainID = D.ID
)
END
GO
-- Made by ME on 2015-01-01
CREATE TRIGGER tMainTableID_AFDEL_tTableChild02
AFTER DELETE
ON tMainTableID
AS
BEGIN
DELETE T
FROM deleted AS D
INNER JOIN tTableChild02 AS T ON (
T.RefToMainID = D.ID
)
END
GO
-- Made by Iamun Kompetent on 2016-01-01
CREATE TRIGGER tMainTableID_AFDEL_tTableChild03
AFTER DELETE
ON tMainTableID
AS
BEGIN
DELETE T
FROM inserted AS D
INNER JOIN tTableChild02 AS T ON (
D.ID = D.ID
)
END
GO
Do you see anything wrong with this approach or even better, do you know a better way to attain modularity?
-- EDIT: Simple example
create table tTest20140128 (
id int not null primary key
)
go
create table tTest20140128_Child01 (
id int not null primary key references tTest20140128(id) on delete no action
)
go
create table tTest20140128_Child02 (
id int not null primary key references tTest20140128(id) on delete no action
)
go
insert tTest20140128 values (1), (2), (3)
insert tTest20140128_Child01 values (1), (2), (3)
insert tTest20140128_Child02 values (1), (2), (3)
go
delete from tTest20140128 -- Error
go
-- table for holding copies of ids
create table tTest20140128_ID (
id int not null primary key references tTest20140128(id) on delete no action
)
go
insert tTest20140128_ID
select id from tTest20140128
go
-- trigger that keeps tTest20140128_ID up to date for new ids
create trigger tTest20140128_AFINS
on tTest20140128
after insert
as
begin
insert tTest20140128_ID
select id from inserted
end
go
-- Instead of delete (keeps tTest20140128_ID up to date for deleted ids)
create trigger tTest20140128_IODEL
on tTest20140128
instead of delete
as
begin
delete ID
from deleted AS D
inner join tTest20140128_ID AS ID ON (
ID.id = D.id
)
delete from T
from deleted AS D
inner join tTest20140128 AS T on (
T.id = D.id
)
end
go
-- Sorta "attching listeners to event"
-- tTest20140128_Child01
create trigger tTest20140128_tTest20140128_Child01
on tTest20140128_ID
after delete
as
begin
delete T
from deleted as D
inner join tTest20140128_Child01 AS T on (
T.id = D.id
)
end
go
-- tTest20140128_Child02
create trigger tTest20140128_tTest20140128_Child02
on tTest20140128_ID
after delete
as
begin
delete T
from deleted as D
inner join tTest20140128_Child02 AS T on (
T.id = D.id
)
end
go
-- New tests
insert tTest20140128 values (4), (5), (6)
insert tTest20140128_Child01 values (4), (5), (6)
insert tTest20140128_Child02 values (4), (5), (6)
go
select COUNT (*) as [COUNT after insert] from tTest20140128
go
delete from tTest20140128 -- No Error
go
select COUNT (*) as [COUNT" after delete] from tTest20140128
go
drop table tTest20140128_ID
drop table tTest20140128_Child02
drop table tTest20140128_Child01
drop table tTest20140128
