SQL Server supporte les cascade delete sur les contraintes foreign key mais n'accepete pas qu'une même table soit la cible de plusieurs cascades. C'est pourtant pratique, lorsque l'on a une table de relation entre deux entités, de savoir que la relation est supprimée automatiquement lorsque n'importe laquelle des deux lignes de référence est supprimée. La solution c'est de recourir à un trigger instead of delete sur les tables de référence pour faire la cascade à la main.
Voici un petit exemple de trigger instead-of delete dans un classique des situations problématiques qui conduisent à interdir les cascades : une référence en diamant où la table D référence les tables B et C qui ont toutes les deux des références sur A, avec descente dans la clé primaire (!) :
Create table TableA
(idxA numeric identity not null,
labelA varchar(50),
constraint pk_TableA primary key(idxA) )
go
Create table TableB
(idxB numeric identity not null,
idxA numeric,
labelB varchar(50),
constraint pk_TableB primary key(idxB),
constraint fk_TableB_ref_TableA foreign key(idxA) references TableA(idxA)
)
go
Create table TableC
(idxC numeric identity not null,
idxB numeric,
idxA numeric,
labelC varchar(50),
constraint pk_TableC primary key(idxC),
constraint fk_TableC_ref_TableB foreign key(idxB) references TableB(idxB) on delete cascade,
constraint fk_TableC_ref_TableA foreign key(idxA) references TableA(idxA) )
go
Create table TableD
(idxD numeric identity not null,
idxA numeric,
idxC numeric,
labelD varchar(50),
constraint pk_TableD primary key(idxD),
constraint fk_TableD_ref_TableA foreign key(idxA) references TableA(idxA),
constraint fk_TableD_ref_TableC foreign key(idxC) references TableC(idxC) on delete cascade )
go
CREATE TRIGGER IOD_TableA ON TableA
INSTEAD OF DELETE
AS
BEGIN
delete TableD from TableD t, deleted d where t.IdxA = d.IdxA
delete TableC from TableC t, deleted d where t.IdxA = d.IdxA
delete TableB from TableB t, deleted d where t.IdxA = d.IdxA
delete TableA from TableA t, deleted d where t.idxA = d.idxA
END
go