Discussion :

[karimot]

Bonjour tout le monde

je voudrais faire une suppression en cascade après la suppression d'un département avec un trigger
voilà le diagramme de base de donnée :


voilà ce que j'ai fai :

Code :

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

1
2
3
4
5
6
7
Create TRIGGER DelCascadeTrig
  ON département
  for DELETE
AS
  DELETE 
    FROM Employe where Ndept in ( select 
   Ndept from deleted)

Le trigger a été crée pas de probleme mais quand j'ai effecué cette commande

Code :

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

1
2
delete from département 
   where  département.NDept=1

Voilà le message de l'erreur :

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK__Employe__NDept__0EA330E9". The conflict occurred in database "db", table "dbo.Employe", column 'NDept'.

The statement has been terminated.

j'ai essayé de faire cette solution pour éviter l'erreur precedente :

Code :

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

1
2
3
4
5
6
7
8
9
Create TRIGGER DelCascadeTrig
  ON département
  for DELETE
AS
  DELETE 
    FROM projet where CodeP  in ( select 
  participation.CdeP from Participation where 
  Participation.Matr in ( select Employe.Matr from Employe
  where Employe.Ndept in ( select Ndept from deleted)))

Mais malheureusement la même erreur

merci d'avance

[Lyche]

Une simple question d'étape d'exécution du code.

Passer le trigger en After Delete pourrait résoudre le soucis.

[karimot]

Re,

Meme si j'ai effectué cette modification le même problème :

Code :

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

1
2
3
4
5
6
7
8
9
alter TRIGGER DelCascadeTrig
  ON département
  after DELETE
AS
  DELETE 
    FROM projet WHERE CodeP  IN ( SELECT 
  participation.CdeP FROM Participation WHERE 
  Participation.Matr IN ( SELECT Employe.Matr FROM Employe
  WHERE Employe.Ndept IN ( SELECT Ndept FROM deleted)))

Après ça :

Code :

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

1
2
delete from département 
   where  département.NDept=2

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__Employe__NDept__0EA330E9". The conflict occurred in database "db", table "dbo.Employe", column 'NDept'.
The statement has been terminated.


Je m'excuse pour le dérangement

[Lyche]

Ok... c'est moi -_-

C'est un before... En gros, il faut que ton trigger nettoie la table fille avant que le delete de la table mère ne s'exécute... Et moi comme un boulet je te le fais faire après...

[karimot]

Avec un before voilà ce que ca donne :



Msg 195, Level 15, State 1, Procedure DelCascadeTrig, Line 3
'before' is not a recognized trigger.


Sql server ne le reconai pas

Code :

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

1
2
3
4
5
6
7
8
9
alter TRIGGER DelCascadeTrig
  ON département
  before DELETE
AS
  DELETE 
    FROM projet WHERE CodeP  IN ( SELECT 
  participation.CdeP FROM Participation WHERE 
  Participation.Matr IN ( SELECT Employe.Matr FROM Employe
  WHERE Employe.Ndept IN ( SELECT Ndept FROM deleted)))

[fsmrel]

Pourquoi un trigger ? Ceci devrait suffire (ON DELETE CASCADE) :

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE EMPLOYE
(
        Matr           CHAR(3)            NOT NULL
      , NDept          CHAR(3)            NOT NULL
    , CONSTRAINT EMPLOYE_PK PRIMARY KEY (Matr)
    , CONSTRAINT EMPLOYE_DEPARTEMENT_FK FOREIGN KEY (NDept)
          REFERENCES DEPARTEMENT ON DELETE CASCADE
) ;

Même chose pour les participations (à moins que l'on ne veuille pas retirer les employés des projets) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE PARTICIPATION
(
        Matr           CHAR(3)            NOT NULL
      , CodeP          CHAR(3)            NOT NULL
      , Heures         INT                NOT NULL
    , CONSTRAINT PARTICIPATION_PK PRIMARY KEY (Matr, CodeP)
    , CONSTRAINT PARTICIPATION_EMPLOYE_FK FOREIGN KEY (Matr)
          REFERENCES EMPLOYE ON DELETE CASCADE
    , CONSTRAINT PARTICIPATION_PROJET_FK FOREIGN KEY (CodeP)
          REFERENCES  PROJET ON DELETE NO ACTION
) ;

[Lyche]

Pourquoi un trigger ? Ceci devrait suffire (ON DELETE CASCADE) :

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE EMPLOYE
(
        Matr           CHAR(3)            NOT NULL
      , NDept          CHAR(3)            NOT NULL
    , CONSTRAINT EMPLOYE_PK PRIMARY KEY (Matr)
    , CONSTRAINT EMPLOYE_DEPARTEMENT_FK FOREIGN KEY (NDept)
          REFERENCES DEPARTEMENT ON DELETE CASCADE
) ;

Même chose pour les participations (à moins que l'on ne veuille pas retirer les employés des projets) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE PARTICIPATION
(
        Matr           CHAR(3)            NOT NULL
      , CodeP          CHAR(3)            NOT NULL
      , Heures         INT                NOT NULL
    , CONSTRAINT PARTICIPATION_PK PRIMARY KEY (Matr, CodeP)
    , CONSTRAINT PARTICIPATION_EMPLOYE_FK FOREIGN KEY (Matr)
          REFERENCES EMPLOYE ON DELETE CASCADE
    , CONSTRAINT PARTICIPATION_PROJET_FK FOREIGN KEY (CodeP)
          REFERENCES  PROJET ON DELETE NO ACTION
) ;

Ho, simple question de pouvoir garder la main sur ses mouvement de BDD... Je déteste les cascades je trouve ça anti-relationnel..

Sans blagues, on créer des systèmes pour s'assurer qu'il n'y aura pas de suppressions de données sans contrôle (fk) et derrière on créer des cascades qui font justement sauter les contraintes... Logique -> 0

Donc non, je n'aurais même pas évoqué le sujet.
Après, je peux comprendre qu'on aime bien cette fonctionnalité.

[darkelend]

Sans blagues, on créer des systèmes pour s'assurer qu'il n'y aura pas de suppressions de données sans contrôle (fk) et derrière on créer des cascades qui font justement sauter les contraintes... Logique -> 0

J'avoue que je pense à peu près la même chose. Je préfère autant une procédure stockée qui va dans la même transaction supprimer toutes les informations dans le bon ordre.
De plus, si quelqu'un tente un delete manuel, les règles d'intégrité lèveront une alerte qui disparait avec le mode cascade.

[Lyche]

J'avoue que je pense à peu près la même chose. Je préfère autant une procédure stockée qui va dans la même transaction supprimer toutes les informations dans le bon ordre.
De plus, si quelqu'un tente un delete manuel, les règles d'intégrité lèveront une alerte qui disparait avec le mode cascade.

merci, je pensais être le seul

[fsmrel]

La suppression fonctionne avec un trigger INSTEAD OF :

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TRIGGER DelCascadeTrig ON DEPARTEMENT INSTEAD OF DELETE
AS
  DELETE 
    FROM EMPLOYE WHERE NDept IN ( SELECT 
   Ndept FROM DELETED) ;
 
   DELETE FROM DEPARTEMENT WHERE NDept IN ( SELECT 
   Ndept FROM DELETED) ;

Mais la mise en oeuvre de ce trigger est équivalente (mais en plus compliqué) à ce que je vous ai proposé (ON DELETE CASCADE).

[karimot]

Bravo ... Merci beaucoup !!

[karimot]

et si on voudrais supprimer aussi dans la table participation et projet
avec le trigger que vous avez donner il se limite juste dans les deux tables
: EMPLOYER ET DEPARTEMENT

pour controler aussi les autres la seul solution c'est la première lors de la création des tableaux ?

j'ai pensé de faire cela :

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
alter TRIGGER DelCascadeTrig ON département instead of DELETE
AS
  DELETE 
    FROM EMPLOYE WHERE NDept IN ( SELECT 
   Ndept FROM DELETED) ;
  DELETE FROM département WHERE NDept IN ( SELECT 
 Ndept FROM DELETED) ;
     DELETE FROM Participation 
 
 
alter trigger Participationdecl
on participation instead of delete as delete from Projet where CodeP in ( select CdeP
from DELETED);
Delete from participation where CdeP in ( select CdeP
from DELETED);

mais ca marche pas toujours j'ai cette erreur :


Msg 547, Level 16, State 0, Procedure Participationdecl, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK__Participat__CdeP__300424B4". The conflict occurred in database "db", table "dbo.Participation", column 'CdeP'.
The statement has been terminated.

[fsmrel]

Cette fois-ci il y a du trigger à mettre en œuvre du fait que la suppression d’un enfant (PARTICIPATION) doit entraîner celle du parent (PROJET).

Exemple (à tester). On supprime au plus bas niveau, participation, puis on supprime en remontant dans la hiérarchie (employé puis département) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER DEPARTEMENT_DELETE ON DEPARTEMENT INSTEAD OF DELETE
AS
  DELETE FROM PARTICIPATION 
         WHERE Matr IN (SELECT Matr 
                        FROM   EMPLOYE 
                        WHERE  NDept IN (SELECT Ndept 
                                         FROM DELETED)) ;
  DELETE FROM EMPLOYE 
         WHERE NDept IN (SELECT Ndept 
                         FROM   DELETED) ;
  DELETE FROM DEPARTEMENT 
          WHERE NDept IN (SELECT Ndept 
                          FROM   DELETED) ;

Et comme il faut supprimer les projets, on intercepte par un trigger ad-hoc les suppressions des participations et à cette occasion on supprime les projets (on répète la suppression des participations, car elles ne sont pas encore effectives) :

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TRIGGER PARTICIPATION_DELETE ON PARTICIPATION INSTEAD OF DELETE
AS
   DELETE FROM PARTICIPATION 
          WHERE Matr IN (SELECT Matr 
                         FROM   DELETED) ;
   DELETE FROM PROJET 
          WHERE CodeP IN (SELECT CodeP 
                          FROM   DELETED) ;

A tester donc. Il y a sans doute des solutions plus élégantes, et les spécialistes de SQL Server pourront vous les communiquer.


N.B. Déclaration de la table PARTICIPATION concernant les clés étrangères :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE PARTICIPATION
(
        Matr           CHAR(3)            NOT NULL
      , CodeP          CHAR(3)            NOT NULL
      , Heures         INT                NOT NULL
    , CONSTRAINT PARTICIPATION_PK PRIMARY KEY (Matr, CodeP)
    , CONSTRAINT PARTICIPATION_EMPLOYE_FK FOREIGN KEY (Matr)
          REFERENCES EMPLOYE ON DELETE NO ACTION
    , CONSTRAINT PARTICIPATION_PROJET_FK FOREIGN KEY (CodeP)
          REFERENCES  PROJET ON DELETE NO ACTION
) ;

[fsmrel]

C'est un before... En gros, il faut que ton trigger nettoie la table fille avant que le delete de la table mère ne s'exécute...

Le problème est que MS SQL Server ne propose pas BEFORE. On peut en passer par INSTEAD OF, comme on l’a vu ça marche, mais quel serait le bénéfice pour karimot d’utiliser un trigger ? Comme je l’ai déjà dit, le résultat obtenu est le même qu’avec une action référentielle CASCADE, mais en bien plus lourd du point de vue du codage, moins bon quant aux performances et ça n’est pas forcément portable (vers d’autres SGBD).

Ho, simple question de pouvoir garder la main sur ses mouvement de BDD...

Dans l’exemple proposé par karimot, que voulez-vous garder comme main ? Le choix de karimot est que soient supprimés automatiquement les employés rattachés à un département qui fait l’objet d’une suppression, ni plus, ni moins. Mais ce choix de karimot peut être remis en question d’un point de vue fonctionnel et, dans l’intérêt des employés, on peut préférer promouvoir la règle suivante :

« On ne peut pas supprimer un département auquel est rattaché au moins un employé ».

Auquel cas c’est l’action référentielle ON DELETE NO ACTION qui sera pertinente et dans ces conditions, pour arriver à supprimer un département, il faudra par exemple d’abord affecter les employés concernés à d’autres départements.

Je déteste les cascades je trouve ça anti-relationnel.

Détester l'action référentielle CASCADE est votre droit le plus strict (confidence pour confidence, moi ce sont les poireaux...), mais vous êtes alors en opposition totale avec les théoriciens du Modèle Relationnel de Données. Je vous renvoie à l’ouvrage de référence de C.J. Date An Introduction to Database Systems (8th Edition) au chapitre 9 (« Integrity »). La théorie relationnelle ne fait des prescriptions que pour les fondements du Modèle Relationnel de Données, et parce qu’une contrainte référentielle n’est qu’un cas particulier de contrainte de base de données, la théorie ne la prescrit pas formellement (donc a fortiori l’action référentielle CASCADE), mais elle y est évidemment favorable et fait des recommandations en ce sens.

Par exemple, dans Database Explorations, Essays on The Third Manifesto and Related Topics, au chapitre 13, page 227 :

A relational database language like Tutorial D should allow <foreign key def>s of the following form to be specified as part of a base or virtual definition:

Code D :

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

1
2
3
4
5
<foreign key def>
    ::=  FOREIGN KEY {[all but] <attribute ref commalist>}
             REFERENCES <relation exp>
            [ON DELETE [NO] CASCADE]
            [ON UPDATE [NO] CASCADE]

Par ailleurs, au moins d’un point de vue sémantique, il n’est pas interdit d’utiliser un concept valide quand il répond à ce qu’on cherche. Pour illustrer : EMPLOYE est une entité-type forte, qui ne fait que désigner l’entité-type DEPARTEMENT ; un employé n’est pas la propriété du département auquel il est rattaché à l’instant T (il peut en changer), en vertu de quoi, pour la contrainte référentielle connectant les tables DEPARTEMENT et EMPLOYE, coder NO ACTION est justifié contrairement à CASCADE. En revanche, si vous avez une entité-type COMMANDE pour les commandes et une entité-type LIGNE_COMMANDE pour les lignes de commande, cette dernière est sémantiquement une entité-type faible (weak entity-type), une propriété de COMMANDE, c’est « à la vie à la mort », la suppression d’une commande entraîne celle de ses lignes (on a une relation de composition au sens UML), et au niveau SQL on n’aurait que des mauvaises raisons pour coder NO ACTION plutôt que CASCADE pour la contrainte référentielle connectant les tables COMMANDE et LIGNE_COMMANDE.

Sans blagues, on créer des systèmes pour s'assurer qu'il n'y aura pas de suppressions de données sans contrôle (fk) et derrière on créer des cascades qui font justement sauter les contraintes...

Pardon ? CASCADE garantit l’intégrité référentielle, heureusement !
Supposons qu’on ait un enchaînement de contraintes référentielles pour les tables T1, T2, T3, ..., Tm-1, Tm (T2 fait référence à T1, T3 à T2, etc.) :

T1 <- T2 <- T3 <- ... Tm-1 <- Tm

Et que l’action référentielle soit systématiquement CASCADE, sauf pour la contrainte connectant Tm à Tm-1.
Un DELETE portant sur un tuple de T1 déclenche un stimulus propagé en cascade jusqu’à Tm. Si aucun tuple de Tm n’est touché, la suppression est effective pour toutes les autres tables de la chaîne. Par contre, si au moins un tuple de Tm est touché, il y a une réaction négative et la tentative de suppression est rejetée en bloc, aucune table n’est mise à jour (l’opération est atomique, c’est tout ou rien) et en contrepartie, comme pour karimot, on a droit à un message d’erreur justifiant le rejet de l’opération.
Ça fonctionne ainsi en relationnel, et dès 1969, Ted Codd (père du Modèle Relationnel de Données) l’avait à l’esprit :

« Some deletions may be triggered by others, if deletion dependencies between specified relations are declared... » (E.F. Codd « Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks », IBM Research Report RJ599, August 19th, 1969).

Incidemment, j’aimerais savoir ce que vous entendez par faire « sauter les contraintes »...

Je préfère autant une procédure stockée qui va dans la même transaction supprimer toutes les informations dans le bon ordre.

Ou l’art de se compliquer la vie. Un SGBD relationnel comme MS SQL Server fait les choses dans le bon ordre et de façon parfaitement transparente, sinon ça se saurait. Par ailleurs, au vu de votre approche, n’oubliez pas que le jour où vous aurez à faire évoluer vos structures de bases de données, vous aurez à vérifier qu’aucun « bon ordre » n’aura été oublié et ne sera donc pris en défaut...

De plus, si quelqu'un tente un delete manuel, les règles d'intégrité lèveront une alerte qui disparait avec le mode cascade.

Qu’appelez vous un « delete manuel » ? Quoi qu’il en soit, je répète : l’action référentielle CASCADE garantit évidemment l’intégrité référentielle. Et pour ma part, ça fait 25 ans que je l’observe (avec DB2, l’intégrité référentielle a été mise à notre disposition en 1988, et bien entendu, nombre de ceux qui la réclamaient à cor et à cris l’on aussitôt rejetée, au nom de la performance, en toute méconnaissance de cause, sans même avoir bâti de prototypes de performances)...

[Lyche]

Le problème est que MS SQL Server ne propose pas BEFORE. On peut en passer par INSTEAD OF, comme on l’a vu ça marche, mais quel serait le bénéfice pour karimot d’utiliser un trigger ? Comme je l’ai déjà dit, le résultat obtenu est le même qu’avec une action référentielle CASCADE, mais en bien plus lourd du point de vue du codage, moins bon quant aux performances et ça n’est pas forcément portable (vers d’autres SGBD).

Certes, j'avais oublié que le before n'existe pas et que le instead of nécéssite de reproduire l'action demandée.
Je m'explique en dessous pour la cascade et pourquoi je ne l'aime pas.

Dans l’exemple proposé par karimot, que voulez-vous garder comme main ? Le choix de karimot est que soient supprimés automatiquement les employés rattachés à un département qui fait l’objet d’une suppression, ni plus, ni moins. Mais ce choix de karimot peut être remis en question d’un point de vue fonctionnel et, dans l’intérêt des employés, on peut préférer promouvoir la règle suivante :

« On ne peut pas supprimer un département auquel est rattaché au moins un employé ».

Auquel cas c’est l’action référentielle ON DELETE NO ACTION qui sera pertinente et dans ces conditions, pour arriver à supprimer un département, il faudra par exemple d’abord affecter les employés concernés à d’autres départements.

Dans son soucis ce n'est pas un problème particulier en effet. Mais je vous rappel que le principe de l'intégrité référentielle est de protéger toute donnée liée à une autre dans les tables filles. Par expérience, j'ai déjà rencontré des entreprises qui permettaient à des MOA ou même des commerciaux (si si je vous jure -_-) de faire des manipulations de bases de données. J'ai vu des updates et des delete se faire exécuter par des gens qui ne connaissaient rien en BDD. On leur avait appris la commande... Bonjour les catastrophe en base prod.
Il avait été demandé par un client à un de nos commerciaux de nettoyer certaines informations de la base. Il a supprimé des données et les cascades déclenchées nous ont fait sauter des informations très importantes sur des questionnaires que ces mêmes personnes avaient remplies...

Sans cascade ça ne se serait pas déroulé ainsi.

Détester l'action référentielle CASCADE est votre droit le plus strict (confidence pour confidence, moi ce sont les poireaux...), mais vous êtes alors en opposition totale avec les théoriciens du Modèle Relationnel de Données. Je vous renvoie à l’ouvrage de référence de C.J. Date An Introduction to Database Systems (8th Edition) au chapitre 9 (« Integrity »). La théorie relationnelle ne fait des prescriptions que pour les fondements du Modèle Relationnel de Données, et parce qu’une contrainte référentielle n’est qu’un cas particulier de contrainte de base de données, la théorie ne la prescrit pas formellement (donc a fortiori l’action référentielle CASCADE), mais elle y est évidemment favorable et fait des recommandations en ce sens.

Les théories et la pratique des entreprises sont deux choses bien distinctes. En France il y a encore un cruel manque de connaissances sur l'importance d'une bonne gestion de ses données. Et surtout sur les bonnes pratiques de maintenances de celles-ci. Cf au dessus

C'est une vision personnelle, et je n'essaye pas de l'imposer. Mais j'ai pris l'habitude de ne pas les utiliser à cause des différentes politiques d'entreprises que j'ai pu croiser et qui m'ont réellement fait prendre conscience qu'il faut garder le contrôle sur tout, même dans les endroits les plus inattendus.

Je comprends tout à fait votre vision, la simplicité de la gestion entrainée par les cascades permettent de se décharger de beaucoup de contrôles...
Mais elles permettent de passer outre des restrictions nécessaires à la sécurité des données.

Et puis.. on est pas toujours au même poste, il suffit qu'une personne reprenne le projet et oublie qu'il y ai une cascade pour faire une erreur.

Cordialement,
Lyche