Discussion :

[orphenou]

Bonjour à tous.

Mon problème est le suivant, j'aimerais créer une série de triggers me permettant de faire des suppressions en cascade.

J'entend par là que si je supprime un enregistrement dans une table il ira voir dans là table que je lui aurai indiqué si il n'existe pas d'enregistrement ayant une foreign key de valeur identique à l'enregistrement supprimé.

Pour prendre un petit exemple très simple j'aurais trois tables : Père, Mère et Fils. Si j'essaie de supprimer un enregistrement dans la table Père, un trigger se déclenche et cherche dans la table Fils pour voir si il existe des enregistrement ayant comme valeur de foreign key vers Père égale à l'identifiant Père de l'enregistrement que je veux supprimer et dans ce cas, il supprime ces enregistrements pour finir par supprimer l'enregistrement de Père.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create table FILS (
   FILS_AUTO            numeric              identity,
   PERE_AUTO            numeric              not null,
   MERE_AUTO            numeric              not null,
   FILS_NOM             varchar(60)          not null,
   constraint PK_FILS primary key (FILS_AUTO)
)
go
 
create table MERE (
   MERE_AUTO            numeric              identity,
   MERE_NOM             varchar(60)          not null,
   constraint PK_MERE primary key (MERE_AUTO)
)
go
 
create table PERE (
   PERE_AUTO            numeric              identity,
   PERE_NOM             varchar(60)          not null,
   constraint PK_PERE primary key (PERE_AUTO)
)
go

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create procedure Procedure_Cascade_Fils_Pere
	@fk_name numeric
as
	begin	
		delete from Fils
		where Pere_Auto = @fk_name
	end
go
 
create procedure Procedure_Cascade_Fils_Mere
	@fk_name numeric
as
	begin	
		delete from Fils
		where Mere_Auto = @fk_name
	end
go

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
create trigger Mere_Cascade on Mere
for delete
as 
	declare @fk_mere_name numeric
	set @fk_mere_name = old.Mere_Auto
	begin
		exec Procedure_Cascade_Fils_Mere @fk_mere_name
	end
go

Le problème avec mon trigger est que je ne parviens pas à récupérer la valeur de l'identifiant de l'enregistrement que je veux supprimer, ce qui m'empêche de lancer ma procédure stockée.

Si quelqu'un pouvait me venir en aide en me disant si ce que je souhaite faire est possible et si oui comment. Sinon, en me disant si il y aurait moyen de parvenir au même résultat avec une autre méthode.

Merci d'avance.

[WOLO Laurent]

SQL-Serveur Intègre cette fonctionalité en natif, pour quoi vouloir se compliquer la vie.
Il suffit d'activer l'intégrité référentielle !

[orphenou]

comment cela marche-t-il exactement?
je suppose que si je fais cela, quel que soit l'enregistrement que je supprime, sql server ira chercher tous les enregistrements y étant relié pour les supprimer si le champ contenant la foreign key a la contrainte not null.
le problème est que j'aimerais sur certaines tables supprimer l'enregistrement même si le champ contenant la foreign key à la contrainte null. est-ce possible?

[WOLO Laurent]

les null ne sont autorisés sur champs clé étrangère.

[orphenou]

ah bon?
je viens pourtant de tester en mettant on delete cascade sur la contrainte de référence d'une foreign key facultative et je n'ai aucun message d'erreur.
qui plus est, dès que je supprime l'enregistrement sur lequel elle pointe, l'enregistrement comportant la foreign key est lui aussi supprimé.

[WOLO Laurent]

Il est alors clair que je me suis trompé !
J'ai du confondre avec une clé primaire.
Merci d'avoir porté cela à mon attention.

[tikam]

Pour récupérer l'identifiant en cours tu peux piocher dans la table DELETED
(table virtuelle qui copie la ligne (même noms de colonnes)sur la quelle tu fais ta transaction cad ton premier delete qui déclenche le trigger)