Logo GNT
Actualités Tests & Guides Bons Plans
GTA 6 iPhone 17 Copilot Switch 2 Temu ChatGPT Tesla
OVH Cloud OVH Cloud
Entraide Windows Windows Serveur SQL Serveur [Complexe] Trigger en cascade sur table hiérarchique

[Complexe] Trigger en cascade sur table hiérarchique

5 réponses
Avatar
Laurent Jordi
Salut à tous :)

Petit problème du dimanche après midi :

Je travaille sur une table hiérarchique (qui point sur elle même). A chaque
enregistrement de la table peut correspondre un ou plusieurs enregistrements
d'une table de jointure.

Table1
Code
FK_T1

Jointure
Code
FK_T2
FK_T1

Lorsque je veux supprimer une enreg de T1, je dois d'une part supprimer les
enregistrements correspondant de la jointure (jusque là rien de violent) et
d'autre part tous les enregistrements fils de L'enregistrement de T1 en
cours de suppression et bien entendu tous les enregs de la jointure
correspondant.

Cela donne une sorte de trigger récursif

Je sais faire en procédure stockée mais je trouve que ça serait beaucoup
plus propre en trigger, ce qui m'amène à vous poser la question suivante :

Peut-on créer des triggers récursif ? Si oui, doit-on séparer la suppression
des fils de la suppression des enregistrements des jointures ?

D'avance merci

Bien à vous

Laurent
Signaler un problème avec ce contenu

5 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
Laurent Jordi
ALTER PROCEDURE SP_R
(
@Parent int
)
AS
/* Procédure récursive
Creation de #TMP_R Table temporaire de récursion contenant les codes de
la descendance
*/
declare @Code int
declare @lvl int

if @@NESTLEVEL=1
BEGIN
CREATE TABLE #TMP_R (
Code int,
Niv int
)
-- On enregistre le 1er code dans la table temporaire
INSERT INTO #TMP_R (Code,Niv) VALUES (@ParentMenu,0)
END

DECLARE C CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT Code
FROM T1
WHERE FK_T1_Code = @ParentMenu
OPEN C
FETCH NEXT FROM C INTO
@Code
WHILE @@FETCH_STATUS = 0
BEGIN
-- On enregistre les code des descendants dans la table temporaire
INSERT INTO #TMP_R
(Code,Niv)
VALUES
(@Code,@@NESTLEVEL)
exec SP_R @Code
FETCH NEXT FROM C INTO
@Code
END
CLOSE C
DEALLOCATE C


if @@NESTLEVEL=1
BEGIN
-- On est ici après être remonté de la récursion
-- Tres important je trie par niveau à l'envers pour ne pas obtenir
d'erreur de violation d'intégrité
DECLARE C CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT Code,Niv FROM #TMP_R ORDER BY Niv DESC
OPEN C
FETCH NEXT FROM C INTO
@Code,@lvl
WHILE @@FETCH_STATUS = 0
BEGIN
--Je supprime d'abord les éléments de la jointure
DELETE FROM J1 WHERE FK_T1_Code = @Code
--Je supprime ensuite le decendant qui à ce stade n'a plus de
décendant
DELETE FROM T1 WHERE Code = @Code
FETCH NEXT FROM C INTO
@Code,@lvl
END
CLOSE C
DEALLOCATE C
--Je détruis la table temporaire de récursion
DROP TABLE #TMP_R
END
RETURN
Avatar
Christian Robert
C'est possible même si çà n'est pas la meilleure idée, le nombre maximal de
niveau en appel récursif est de 32 (tout comme les appels imbriqué, et les
appels de proc stockées)... Faire une boucle serait mieux, si cela est
possible...

Il faut activer l'option de trigger récursif sur la base de données :
ALTER DATABASE pubs SET RECURSIVE_TRIGGERS ON

Chaque trigger peut a nouveau se déclencher s'il fait des modifications sur
la même table... Un trigger FOR DELETE, se redeclenchera si l'on fait un
DELETE sur cette même table...

Petit article MSDN :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_6nw3.asp

Cordialement

------------------------------
Christian Robert
Winwise
MCT - MCDBA - MCSD.Net


"Laurent Jordi" a écrit :

ALTER PROCEDURE SP_R
(
@Parent int
)
AS
/* Procédure récursive
Creation de #TMP_R Table temporaire de récursion contenant les codes de
la descendance
*/
declare @Code int
declare @lvl int

if @@NESTLEVEL=1
BEGIN
CREATE TABLE #TMP_R (
Code int,
Niv int
)
-- On enregistre le 1er code dans la table temporaire
INSERT INTO #TMP_R (Code,Niv) VALUES (@ParentMenu,0)
END

DECLARE C CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT Code
FROM T1
WHERE FK_T1_Code = @ParentMenu
OPEN C
FETCH NEXT FROM C INTO
@Code
WHILE @@FETCH_STATUS = 0
BEGIN
-- On enregistre les code des descendants dans la table temporaire
INSERT INTO #TMP_R
(Code,Niv)
VALUES
(@Code,@@NESTLEVEL)
exec SP_R @Code
FETCH NEXT FROM C INTO
@Code
END
CLOSE C
DEALLOCATE C


if @@NESTLEVEL=1
BEGIN
-- On est ici après être remonté de la récursion
-- Tres important je trie par niveau à l'envers pour ne pas obtenir
d'erreur de violation d'intégrité
DECLARE C CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT Code,Niv FROM #TMP_R ORDER BY Niv DESC
OPEN C
FETCH NEXT FROM C INTO
@Code,@lvl
WHILE @@FETCH_STATUS = 0
BEGIN
--Je supprime d'abord les éléments de la jointure
DELETE FROM J1 WHERE FK_T1_Code = @Code
--Je supprime ensuite le decendant qui à ce stade n'a plus de
décendant
DELETE FROM T1 WHERE Code = @Code
FETCH NEXT FROM C INTO
@Code,@lvl
END
CLOSE C
DEALLOCATE C
--Je détruis la table temporaire de récursion
DROP TABLE #TMP_R
END
RETURN





Avatar
SQLpro [MVP]
Laurent Jordi a écrit :
Salut à tous :)

Petit problème du dimanche après midi :

Je travaille sur une table hiérarchique (qui point sur elle même). A chaque
enregistrement de la table peut correspondre un ou plusieurs enregistrements
d'une table de jointure.

Table1
Code
FK_T1

Jointure
Code
FK_T2
FK_T1

Lorsque je veux supprimer une enreg de T1, je dois d'une part supprimer les
enregistrements correspondant de la jointure (jusque là rien de violent) et
d'autre part tous les enregistrements fils de L'enregistrement de T1 en
cours de suppression et bien entendu tous les enregs de la jointure
correspondant.

Cela donne une sorte de trigger récursif

Je sais faire en procédure stockée mais je trouve que ça serait beaucoup
plus propre en trigger, ce qui m'amène à vous poser la question suivante :

Peut-on créer des triggers récursif ? Si oui, doit-on séparer la suppression
des fils de la suppression des enregistrements des jointures ?

D'avance merci

Bien à vous

Laurent





Il serait beaucoup plus astucieux de modéliser votre table sous forme
d'arbre intervallaire. Cela élimine TOUTE récursivité, y compris dans
les requêtes SELECT pour parcourir l'arbre.

Regardez les articles que j'ai écrit à ce sujet :
1) pour V 2000 : http://sqlpro.developpez.com/cours/arborescence/
2) pour v 2005 :
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp

A +


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Avatar
bruno reiter
tu as peut etre intérêt à créer ta FK avec ON DELETE CASCADE

br

"Laurent Jordi" <laurent.jordi@(xxx)wanadoo.fr> a écrit dans le message de
news: OkqrqA%
Salut à tous :)

Petit problème du dimanche après midi :

Je travaille sur une table hiérarchique (qui point sur elle même). A
chaque enregistrement de la table peut correspondre un ou plusieurs
enregistrements d'une table de jointure.

Table1
Code
FK_T1

Jointure
Code
FK_T2
FK_T1

Lorsque je veux supprimer une enreg de T1, je dois d'une part supprimer
les enregistrements correspondant de la jointure (jusque là rien de
violent) et d'autre part tous les enregistrements fils de L'enregistrement
de T1 en cours de suppression et bien entendu tous les enregs de la
jointure correspondant.

Cela donne une sorte de trigger récursif

Je sais faire en procédure stockée mais je trouve que ça serait beaucoup
plus propre en trigger, ce qui m'amène à vous poser la question suivante :

Peut-on créer des triggers récursif ? Si oui, doit-on séparer la
suppression des fils de la suppression des enregistrements des jointures ?

D'avance merci

Bien à vous

Laurent





Avatar
Laurent Jordi
Salut,

Merci de vos réponses, j'étais en déplacement, je n'ais pas eu le temps de
les consulter avant aujourd'hui. Je trouve l'approche de l'arbre
intervallaire très intéressante mais assez abstraite. Je vais toutefois
l'étudier car dernière la base de données il y a toute une série de
procédures et de composants que j'ai réalisé et qui devraient être adaptés
pour fonctionner avec ce modèle. Par contre, la possibilité de connaître
tous les descendants d'un noeud sans faire de requête récursive est très
intéressante.

Compte tenu du fait que la hiérarchie est assez limitée je suis loin
d'atteindre la limite des 32 Niveaux (5 ou 6) tout au plus. Pour l'heure
j'ai opté pour l'option Procédure stockée récursive.

Bien à vous

Laurent Jordi


"bruno reiter" a écrit dans le message de
news:
tu as peut etre intérêt à créer ta FK avec ON DELETE CASCADE

br

"Laurent Jordi" <laurent.jordi@(xxx)wanadoo.fr> a écrit dans le message de
news: OkqrqA%
Salut à tous :)

Petit problème du dimanche après midi :

Je travaille sur une table hiérarchique (qui point sur elle même). A
chaque enregistrement de la table peut correspondre un ou plusieurs
enregistrements d'une table de jointure.

Table1
Code
FK_T1

Jointure
Code
FK_T2
FK_T1

Lorsque je veux supprimer une enreg de T1, je dois d'une part supprimer
les enregistrements correspondant de la jointure (jusque là rien de
violent) et d'autre part tous les enregistrements fils de
L'enregistrement de T1 en cours de suppression et bien entendu tous les
enregs de la jointure correspondant.

Cela donne une sorte de trigger récursif

Je sais faire en procédure stockée mais je trouve que ça serait beaucoup
plus propre en trigger, ce qui m'amène à vous poser la question suivante
:

Peut-on créer des triggers récursif ? Si oui, doit-on séparer la
suppression des fils de la suppression des enregistrements des jointures
?

D'avance merci

Bien à vous

Laurent