[SQL 2008R2] Trigger de suppression sur une table récursive [Résolu]

[sebRD]

Bonjour, je suis débutant en SQL-Server et triggers.

J'ai une table "Tache" composé de 2 champs:
- id, uniqueidentifier et clé primaire
- Parente, uniqueidentifier qui fait référence à la tache parente
J'ai donc une arborescence structurée de taches (je précise qu'une tache ne peut pas être parente et enfant en même temps d'une autre tache)

Une table "intervenant"
- id, uniqueidentifier et clé primaire

Une table "Tache_intervenant" pour lier des intervenants à des taches
- Tache, uniqueidentifier clé primaire
- Intervenant, uniqueidentifier clé primaire

Je voudrais créer un trigger qui lors de la suppression d'une tache me supprime toutes les taches enfants (récursivement) ainsi que toutes les lignes de ma table "Tache_Intervenant" faisant référence à ma tache et donc aux taches enfants (récursivement aussi)

J'ai fait ceci et j'ai quelques questions:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER Tache_DEL_TRIGGER
   ON  Tache 
   INSTEAD OF DELETE
AS 
BEGIN
--a)je supprime les taches enfant (au premier niveau) de ma tache supprimée
	DELETE Tache WHERE Parente IN (SELECT id FROM Deleted)
--b)je supprime le lignes dans ma table de liaison qui contiennent l'id de la tache
	DELETE Tache_Intervenant WHERE Tache IN (SELECT id FROM Deleted)
--c)apres avoir supprimé les taches enfant, je supprime la tache concernée
    DELETE Tache WHERE id IN (SELECT id FROM Deleted)
END
GO

1) Est-ce que mon instruction (a) va rappeler récursivement mon trigger?
2) Est-ce que mon instruction (c) va rappeler récursivement mon trigger?
3) Si j'ai un trigger DELETE sur ma table "Tache_Intervenant" va-t-il se déclencher?

Merci pour vos réponses

[SQLpro]

Il faut que vous fassiez un DELETE avec une CTE récursive. A lire : http://sqlpro.developpez.com/cours/s...te-recursives/

A +

[aieeeuuuuu]

Bonjour,


pour la question 1/:
Je dirai que oui, si l'option NESTED TRIGGERS est activée (ce qui est le cas par defaut), et ce jusqu'a 32 niveaux de mémoire (à vérifier)
Par contre, un thread est en cours sur ce forum, ou il s'agit d'un trigger AFTER qui ne declenche pas de trigger instead of (alors qu'il devrait...)... donc le mieux pour toi est de tester pour avoir la réponse à ta question... et de nous tenir au courant
En fait, je pense que ton trigger INSTEAD OF ne sera pas appelé de façon récursive, mais je trouve que la doc manque de clarté à ce sujet...

pour les questions 2/ et 3/, idem... en théorie oui, en pratique ça semble parfois avoir un comportement... inatendu.

mais je pense qu'il y a une autre solution, qui évitera ce genre de questions, et qui sera peut-être mieux niveau perfs.

1/ pour la FK taches --> Tache_Intervenant, tu peux spécifier une suppression en cascade, ce qui t’évitera un trigger.

2/ pour la FK taches --> taches, il me semble que l'on ne peut pas spécifier de cascade sur les autoréferences, mais tu peux gérer la récursivité au sein d'un seul trigger instead of (si tu vérifies qu'ils ne sont pas appelés en cascade )

quelque chose comme :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
CREATE TRIGGER TRG_DEL_ARBO
ON Tache
INSTEAD OF DELETE
AS
BEGIN
	;WITH ARBO AS(
		SELECT id 
		FROM DELETED
		UNION ALL
		SELECT Tache.id
		FROM Tache
		INNER JOIN ARBO 
			ON ARBO.id = Tache.parent
	)
	DELETE FROM Tache
	FROM ARBO 
	WHERE ARBO.id = Tache.id
	OPTION (MAXRECURSION 0)
END

Par contre, il faut aussi prévoir que les données peuvent être... mises à jour

[SQLpro]

INSTEAD OF n'est pas récursif puisque rien n'est fait !

A +

[aieeeuuuuu]

rien n'est fait, certes, mais si on y fait des choses..;

Dans ce cas précis, un trigger INSTEAD OF DELETE sur une table, qui supprime des données dans cette même table, devrait se declencher de façon récursive.

la doc sur les trigger imbriqués parle bien de récursion (directe ou indirecte). Et pour ce qui est de la récursion directe :

Direct recursion

This recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again. For example, an application updates table T3; this causes trigger Trig3 to fire. Trig3 updates table T3 again; this causes trigger Trig3 to fire again.

In SQL Server 2008, direct recursion can also occur when the same trigger is called again, but after a trigger of a different type (AFTER or INSTEAD OF) is called. In other words, direct recursion of an INSTEAD OF trigger can occur when the same INSTEAD OF trigger is called for a second time, even if one or more AFTER triggers are called in between. Likewise, direct recursion of an AFTER trigger can occur when the same AFTER trigger is called for a second time, even if one or more INSTEAD OF triggers are called in between. For example, an application updates table T4. This update causes INSTEAD OF trigger Trig4 to fire. Trig4 updates table T5. This update causes AFTER trigger Trig5 to fire. Trig5 updates table T4, and this update causes INSTEAD OF trigger Trig4 to fire again. This chain of events is considered direct recursion for Trig4.

Cela laisse supposer qu'un trigger INSTED OF peut être appelé de façon récursive...

Mais après test, en effet (et pour répondre partiellement aux questions de sebRD) un trigger INSTEAD OF n'est pas appelé de façon récursive

[sebRD]

Merci pour vos réponses, je bosse sur un autre truc maintenant, je vous tiens au courant dès que j'y reviens

[sebRD]

Voila j'y suis revenu. Ce déclencheur marche merci à tous

Code :

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
 
CREATE TRIGGER Tache_DEL_TRIGGER ON Tache INSTEAD OF DELETE
AS   
    CREATE TABLE #Tache(
        id    uniqueidentifier
    )
 
    CREATE TABLE #Tache_Intervenant(
		id uniqueidentifier
	)
 
    INSERT INTO #Tache (id)
    SELECT  id
    FROM    deleted
 
    DECLARE @c INT
    SET @c = 0
 
    WHILE @c <> (SELECT COUNT(id) FROM #Tache) BEGIN
        SELECT @c = COUNT(id) FROM #Tache
 
        INSERT INTO #Tache (id)
        SELECT  Tache.id
        FROM    Tache
        LEFT OUTER JOIN #Tache ON Tache.id = #Tache.id
        WHERE   Tache.Parente IN (SELECT id FROM #Tache)
        AND     #Tache.id IS NULL
    END
 
	DELETE Tache_Intervenant WHERE Tache IN (SELECT id FROM #Tache)
 
    DELETE  Tache
    FROM    Tache
    INNER JOIN #Tache ON Tache.id = #Tache.id
 
GO

[elsuket]

Bonjour,

Vous n'avez aucunement besoin de table temporaire ici, ni de boucle.
Vous traitez les lignes une à une alors que SQL est un langage ensembliste, conçu pour traiter des ensembles de lignes.
Il est contre-performant au traitement ligne à ligne.

Quand vous codez dans une implémentation de SQL, (celle de SQL Server s'appelle T-SQL), oubliez la façon dont vous codez dans les autres langages fonctionnels.

J'ajoute que SQL est de plus un langage déclaratif : vous décrivez ce que vous voulez, pas la façon dont vous souhaitez l'obtenir !

Vous pouviez tout à fait partir de ce que vous a donné aieeeuuuuu :

Code :

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
ALTER TRIGGER Tache_DEL_TRIGGER 
	ON dbo.Tache
	INSTEAD OF DELETE
AS
BEGIN
	SET NOCOUNT ON
 
	;WITH
		CTE AS
		(
				SELECT	id
				FROM	deleted
			UNION ALL
				SELECT		T.id
				FROM		CTE AS C
				INNER JOIN	dbo.Tache AS T ON C.id = T.parent
		)
	DELETE		FROM dbo.Tache_intervenant
	FROM		dbo.Tache_intervenant AS TI
	INNER JOIN	CTE AS C
				ON TI.Tache = C.id
 
	;WITH
		CTE AS
		(
				SELECT	id
				FROM	deleted
			UNION ALL
				SELECT		T.id
				FROM		CTE AS C
				INNER JOIN	dbo.Tache AS T ON C.id = T.parent
		)
	DELETE		FROM dbo.Tache
	FROM		dbo.Tache AS T
	INNER JOIN	CTE AS C ON T.id = CTE.id
END

Ici deux instructions, et basta

@++