Discussion :

[Hqlz87]

Bonjour à tous !

Je travaille actuellement sur une table destinée à stocker les messages d'un forum.

Cette table s'appelle T_message et elle est composée (pour simplifier) de deux champs :

- MES_id : clé primaire, id du message
- MES_fk_MES_parent : clé étrangère, id du message parent

Une contrainte d'intégrité est établie entre les champs MES_fk_MES_parent et le champ MES_id de cette table, de façon à ce qu'un message ne puisse pas avoir un parent qui n'existe pas.

Le principe de stockage des messages est que chaque message posté peut être le message initial d'un sujet (dans ce cas, MES_fk_MES_parent est NULL) ou une réponse à un autre message (dans ce cas, MES_fk_MES_parent est non NULL)

Voilà pour les présentations. Il doit toutefois être possible de supprimer n'importe quel message de la table, et lorsqu'un message est supprimé, ses fils, sous-fils, etc doivent être supprimés également. (Il peut y avoir N niveaux de fils)

A première vue, l'option "Effacer en cascade les enregistrements correspondants" n'est pas disponible pour une relation sur une seule et même table. En tous cas, je ne peux pas l'activer.

J'ai donc entrepris de créer un Trigger qui s'activera lors de la suppression d'un message, et qui aura pour effet d'être rappelé récursivement pour supprimer les messages fils. Voici 2 versions du Trigger que j'ai testé :

1ère Version : FOR DELETE

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
CREATE Trigger T_message_Trigger_suppression
ON T_message
FOR Delete
 
AS
 
DECLARE @idfils int		-- id des messages fils du message supprimé
DECLARE @idmessage int	   -- id du message supprimé
 
-- On récupère l'id du message supprimé
SET @idmessage = (SELECT MES_id FROM deleted)
 
-- Désactivation de la contrainte d'intégrité qui empêcherait la suppression de messages
ALTER TABLE T_message NOCHECK CONSTRAINT FK_T_message_T_message
 
-- Suppression en cascade des messages fils
 
-- Création du curseur qui contient les id des fils du message supprimé
DECLARE C_fils CURSOR LOCAL
FOR
	SELECT MES_id FROM T_message WHERE MES_fk_MES_parent = @idmessage
 
OPEN C_fils
FETCH C_fils INTO @idfils
 
-- Parcours du curseur
WHILE @@fetch_Status = 0 
BEGIN
 
	-- Suppression des fils
	DELETE T_message WHERE MES_id = @idfils
	FETCH C_fils INTO @idfils
 
END 
 
-- Fermeture du curseur et libération de la mémoire
CLOSE C_fils
DEALLOCATE C_fils
 
-- Réactivation de la contrainte d'intégrité
ALTER TABLE T_message CHECK CONSTRAINT FK_T_message_T_message

Dans ce cas, la suppression du message semble se faire avant la désactivation de la contrainte d'intégrité, puisque j'obtiens le message d'erreur

Conflit entre l'instruction DELETE et la contrainte COLUMN SAME TABLE REFERENCE 'FK_T_message_T_message'. Le conflit est survenu dans la base de données 'test1', table 'T_message', column 'MES_fk_MES_parent'

2ème Version : INSTEAD OF DELETE

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
CREATE Trigger T_message_Trigger_suppression
ON T_message
INSTEAD OF Delete
 
AS
 
DECLARE @idfils int		-- id des messages fils du message supprimé
DECLARE @idmessage int	   -- id du message supprimé
 
-- On récupère l'id du message supprimé
SET @idmessage = (SELECT MES_id FROM deleted)
 
-- Désactivation de la contrainte d'intégrité qui empêcherait la suppression de messages
ALTER TABLE T_message NOCHECK CONSTRAINT FK_T_message_T_message
 
-- Suppression en cascade des messages fils
 
-- Création du curseur qui contient les id des fils du message supprimé
DECLARE C_fils CURSOR LOCAL
FOR
	SELECT MES_id FROM T_message WHERE MES_fk_MES_parent = @idmessage
 
OPEN C_fils
FETCH C_fils INTO @idfils
 
-- Parcours du curseur
WHILE @@fetch_Status = 0 
BEGIN
 
	-- Suppression des fils
	DELETE T_message WHERE MES_id = @idfils
	FETCH C_fils INTO @idfils
 
END 
 
-- Fermeture du curseur et libération de la mémoire
CLOSE C_fils
DEALLOCATE C_fils
 
-- Suppression du message dont la suppression a été empêchée par INSTEAD OF
DELETE T_message WHERE MES_id = @idmessage
 
-- Réactivation de la contrainte d'intégrité
ALTER TABLE T_message CHECK CONSTRAINT FK_T_message_T_message

Cette fois-ci, la contrainte d'intégrité est bien désactivée lors de la suppression du message, mais la suppression des fils dans la boucle ne rappelle pas le Trigger par récursivité : je ne peux supprimer qu'un niveau de fils.


Mon problème est donc là : je n'arrive pas à trouver un Trigger qui puisse à la fois désactiver temporairement la contrainte d'intégrité et se rappeler de manière récursive.

A ce propos, est-ce vraiment normal que mon Trigger en INSTEAD OF ne se rappelle pas récursivement ?

Merci de vous être intéressés à mon problème, bonne journée

[Hqlz87]

Personne n'aurait une petite idée là dessus ?

[SQLpro]

1) vérifiez que l'option de récursivité des trigger est bien activée
2) les trigger MS SQL Server étant after vous ne pourrez pas le faire par ce biais
3) le moyen le plus simple est plutôt d'utiliser :
a) soit une reque^te de suppression récursive intyroduite par CTE (version 2005)
b) soit en modélisant votre arbre de façon intervallaire afin d'éviter toute récursivitée

Lisez les papiers que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/s...te-recursives/
http://sqlpro.developpez.com/cours/arborescence/

A +