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

Version imprimable

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

23/03/2011, 17h08
SQLpro

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

A +

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:

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 ;)

23/03/2011, 17h35
SQLpro

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

A +
23/03/2011, 18h08
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 :

Citation:

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
28/03/2011, 14h58
sebRD

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

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

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 ;)

@++ ;)