Exécution d'un trigger sur CASCADE d'une clé étrangère

Bonjour,

J'ai le schéma suivant :
Candidat-diplome -0,1----prouver----0,1- Fichier

=> Un fichier peut prouver qu'un candidat a obtenu un diplôme et un candidat ayant obtenu un diplôme peut le prouver en joignant un fichier.


J'ai donc les tables suivantes :
te_candidat_diplome_cdp (cdp_id_candidat, cdp_id_diplome...) => table issue d'une "entité type associative
te_fichier_fic (fic_id...)
tj_fic_prouver_cdp_fpc (fpc_id_fichier, fpc_id_candidat, fpc_id_diplome)

J'ai défini les clés étrangères de la table tj_fic_prouver_cdp_fpc en CASCADE :
- Si je supprime un fichier, l'enregistrement correspondant sera supprimé dans la table associative ;
- Si je supprime une association candidat-diplome, l'enregistrement correspondant sera supprimé dans la table associative.

Mais dans ce dernier cas, l'existence du fichier n'a plus de sens et je voudrais que le fichier soit lui aussi supprimé "en cascade", ce qui ne peut pas se produire dans ce sens là.
J'ai donc l'idée de créer un trigger sur la table associative qui supprime fichier correspondant sur toute suppression de ligne dans la la table associative.
Il me semblait avoir lu il y a longtemps que le trigger ne se déclenchait pas sur les opérations de cascade mais uniquement lors de requêtes SQL explicites (DELETE, UPDATE, INSERT).
Est-ce le cas ou puis-je créer mon trigger sans crainte de tomber sur un cas où il ne s'exécuterait pas ?

C'est donc que ton entité fichier dépend des deux autres… Donc, tu devrais modifier ton modèle.
Moi je mettrais la dépendance entre diplôme et fichier.

A +

Mais le fichier peut être aussi une photo, un scan de pièce d'identité, une attestation... Il s'agit des fichiers joints à une candidature puis une inscription d'un étudiant ou d'un stagiaire chez nous. Donc j'ai plusieurs associations entre fichier et d'autres tables : celle des étudiants et celle des inscriptions.
La table des diplômes enregistre les diplômes existants : tous les bacs et, au fur et à mesure qu'on les collecte via les inscriptions, les diplômes supérieurs (ils sont trop nombreux pour qu'on les connaisse tous, surtout qu'on a beaucoup d'étrangers).
Donc j'enregistre dans la table te_candidat_diplome_cdp l'association entre les diplômes et les candidats et c'est à cette association que je dois associer l'éventuel fichier apportant la preuve de l'obtention du diplôme.
La table te_candidat_diplome_cdp était auparavant une table associative (issue d'une association du MCD). J'ai transformé l'association en "entité-type associative" dans le MCD pour pouvoir à nouveau associer cet ensemble aux fichiers et à d'autres informations (mention obtenue, ville d'obtention).

Donc ma question technique sur le trigger reste posée.

Mécanisme :
1) Je supprime un couple (candidat, diplôme) dans te_candidat_diplome_cdp.
2) Avec la clé étrangère y faisant référence et la contrainte ON DELETE CASCADE, la ligne correspondante est supprimée dans la table tj_fic_prouver_cdp_fpc.
3) Le trigger AFTER DELETE de la table te_candidat_diplome_cdp, qui doit supprimer la ligne correspondante dans la table des fichiers va t-il se déclencher alors qu'il n'y aura pas eu d'instruction DELETE explicite en SQL ?

Salut à tous.

Je comprends mieux la problématique après avoir fait des essais.

Citation:

---

Envoyé par SQLPRO

Moi je mettrais la dépendance entre diplôme et fichier.

---

J'aurai plutôt mis la dépendance entre le candidat et le fichier.
Après tout, la preuve du diplôme appartient au candidat, non ?

Je pense que la table diplôme est commune à tous les candidats et sert de descriptif général.
A priori, je pense que CinePhil ne va jamais supprimer quoi que ce soit dans le table diplôme.

Même si l'on crée la dépendance sur le candidat, CinePhil désire supprimer une ligne de la table association, et faire en cascade la suppression.
Ok, mais la bonne question est : vers quoi ?

Citation:

---

Envoyé par CinePhil

Il me semblait avoir lu il y a longtemps que le trigger ne se déclenchait pas sur les opérations de cascade mais uniquement lors de requêtes SQL explicites (DELETE, UPDATE, INSERT).

---

Je confirme que le déclencheur ne fonctionnera pas sur une suppression en cascade.
Cela ne peut se faire que sur un delete, explicitement donnée !

La seule solution possible est, de rattacher le fichier, non pas au candidat, non pas au diplôme, mais à la table association.
Ce qui implique qu'un fichier ne doit contenir qu'un et un seul diplôme.
La bonne modélisation est :

candidat (id_candidat, ...)
diplome (id_diplome, ...)
association (id_association, fk_candidat, fk_diplome, ...)
fichier (id_fichier, fk_association, ...)

La preuve du diplôme se fera par la présence de la clef étrangère entre la table association et la table fichier.

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 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225

-------------- START TRANSACTION --------------   -------------- DROP DATABASE IF EXISTS `base` --------------   -------------- CREATE DATABASE IF NOT EXISTS `base` DEFAULT CHARACTER SET `latin1` DEFAULT COLLATE `latin1_general_ci` --------------   -------------- DROP TABLE IF EXISTS `candidat` --------------   -------------- create table `candidat` ( `id` integer unsigned not null auto_increment primary key, `patronyme` varchar(255) not null ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- insert into `candidat` (`patronyme`) values ('rouge'),('jaune'),('vert') --------------   -------------- select * from `candidat` --------------   +----+-----------+ | id | patronyme | +----+-----------+ | 1 | rouge | | 2 | jaune | | 3 | vert | +----+-----------+ -------------- DROP TABLE IF EXISTS `diplome` --------------   -------------- create table `diplome` ( `id` integer unsigned not null auto_increment primary key, `lib` varchar(255) not null ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- insert into `diplome` (`lib`) values ('BEPC'),('CAP'),('BACCALAUREAT') --------------   -------------- select * from `diplome` --------------   +----+--------------+ | id | lib | +----+--------------+ | 1 | BEPC | | 2 | CAP | | 3 | BACCALAUREAT | +----+--------------+ -------------- DROP TABLE IF EXISTS `association` --------------   -------------- create table `association` ( `id` integer unsigned not null auto_increment primary key, `fk_candidat` integer unsigned not null, `fk_diplome` integer unsigned not null, CONSTRAINT `FK_01` FOREIGN KEY (`fk_candidat`) REFERENCES `candidat` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_02` FOREIGN KEY (`fk_diplome`) REFERENCES `diplome` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- insert into `association` (`fk_candidat`,`fk_diplome`) values (1,1),(1,3), (2,1),(2,3), (3,1) --------------   -------------- select * from `association` --------------   +----+-------------+------------+ | id | fk_candidat | fk_diplome | +----+-------------+------------+ | 1 | 1 | 1 | | 2 | 1 | 3 | | 3 | 2 | 1 | | 4 | 2 | 3 | | 5 | 3 | 1 | +----+-------------+------------+ -------------- DROP TABLE IF EXISTS `fichier` --------------   -------------- create table `fichier` ( `id` integer unsigned not null auto_increment primary key, `nom_fichier` varchar(255) not null, `fk_association` integer unsigned not null, CONSTRAINT `FK_03` FOREIGN KEY (`fk_association`) REFERENCES `association` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- insert into `fichier` (`nom_fichier`,`fk_association`) values ('ma preuve du bepc', 1), ('ma preuve du bac', 2), ('ma preuve du bepc', 3), ('ma preuve du bepc', 5) --------------   -------------- select * from `fichier` --------------   +----+-------------------+----------------+ | id | nom_fichier | fk_association | +----+-------------------+----------------+ | 1 | ma preuve du bepc | 1 | | 2 | ma preuve du bac | 2 | | 3 | ma preuve du bepc | 3 | | 4 | ma preuve du bepc | 5 | +----+-------------------+----------------+ -------------- delete from `association` where id=3 --------------   -------------- select * from `candidat` --------------   +----+-----------+ | id | patronyme | +----+-----------+ | 1 | rouge | | 2 | jaune | | 3 | vert | +----+-----------+ -------------- select * from `diplome` --------------   +----+--------------+ | id | lib | +----+--------------+ | 1 | BEPC | | 2 | CAP | | 3 | BACCALAUREAT | +----+--------------+ -------------- select * from `association` --------------   +----+-------------+------------+ | id | fk_candidat | fk_diplome | +----+-------------+------------+ | 1 | 1 | 1 | | 2 | 1 | 3 | | 4 | 2 | 3 | | 5 | 3 | 1 | +----+-------------+------------+ -------------- select * from `fichier` --------------   +----+-------------------+----------------+ | id | nom_fichier | fk_association | +----+-------------------+----------------+ | 1 | ma preuve du bepc | 1 | | 2 | ma preuve du bac | 2 | | 4 | ma preuve du bepc | 5 | +----+-------------------+----------------+ -------------- select t1.id, t1.fk_candidat, t2.patronyme, t1.fk_diplome, t3.lib, t4.nom_fichier   from `association` as t1   inner join `candidat` as t2 on t2.id = t1.fk_candidat   inner join `diplome` as t3 on t3.id = t1.fk_diplome   left outer join `fichier` as t4 on t4.id = t1.id --------------   +----+-------------+-----------+------------+--------------+-------------------+ | id | fk_candidat | patronyme | fk_diplome | lib | nom_fichier | +----+-------------+-----------+------------+--------------+-------------------+ | 1 | 1 | rouge | 1 | BEPC | ma preuve du bepc | | 2 | 1 | rouge | 3 | BACCALAUREAT | ma preuve du bac | | 4 | 2 | jaune | 3 | BACCALAUREAT | ma preuve du bepc | | 5 | 3 | vert | 1 | BEPC | NULL | +----+-------------+-----------+------------+--------------+-------------------+ -------------- COMMIT --------------   Appuyez sur une touche pour continuer...

---

Dans mon exemple, le candidat vert n'a pas donné sa preuve de l'obtention de son BEPC --> NULL dans la colonne nom_fichier.


@+

Dans ce cas j'ajoute une double clef étrangère composées :

• de l'id de la ligne de table référencée (diplôme ou autre)
• de l'id de la table (il faut une table des tables dans ton modèle).

Et le tout géré par un déclencheur (Intégrité Référentielle Procédurale et non par contrainte : Intégrité Référentielle Déclarative…)

A +

Citation:

---

Envoyé par Artemus24

Je confirme que le déclencheur ne fonctionnera pas sur une suppression en cascade.
Cela ne peut se faire que sur un delete, explicitement donnée !

---

Merci, c'était la précision technique que je demandais.

Citation:

---

La seule solution possible est, de rattacher le fichier, non pas au candidat, non pas au diplôme, mais à la table association.

---

C'est justement ce que je fais mais comme :
1) Le fichier peut être la preuve d'autre chose qu'un diplôme ;
2) La preuve du diplôme peut ne pas exister donc pas de ligne correspondante dans la table des fichiers,

J'ai une table associative entre celle des fichiers et celle des couples (candidat, diplôme).

Ce qui justifiait ma question à laquelle j'ai maintenant une réponse.

Citation:

---

Dans ce cas j'ajoute une double clef étrangère composées :

de l'id de la ligne de table référencée (diplôme ou autre)
de l'id de la table (il faut une table des tables dans ton modèle).



Et le tout géré par un déclencheur (Intégrité Référentielle Procédurale et non par contrainte : Intégrité Référentielle Déclarative…)

---

Mouais... j'aime bien les clés étrangères et la solution que j'envisage (trigger AFTER DELETE cette fois sur la table des couples {candidat, diplôme}) a ma préférence.
Mais bon... ce n'est pas urgent car nous ne sommes pas prêts de supprimer de ces couples, à mon avis.

Merci à vous deux ; je considère le sujet :resolu:

Encore une question, cependant, à destination plus particulièrement de SQLPro :
Est-ce que ce non de déclenchement de trigger si la suppression n'est pas lancée explicitement par un DELETE en SQL est conforme à la norme SQL ou est-ce une déficience de MySQMerde ? ;)

Salut CinePhil.

Comme vous n'avez pas été assez précis dans votre demande, je me pose aussi la question du nombre de fichiers qu'un candidat peut donner en pièce jointe, ainsi que le lieu où sont stockés ces fichiers.

Si vous avez fait le choix de stocker ces fichiers dans une table dédié à cela, aucune suppression physique dans un répertoire n'est à envisager.
Si par contre, vous indiquez un lien, il faudra envisager deux choses :

• Ne pas faire de suppression physique mais une suppression logique afin de ne pas perdre le lien vers le fichier que vous désirez supprimer.
• Faire le soir, dans un batch, un traitement qui va supprimer physiquement les fichiers dans les répertoires, ainsi que la suppression physique des lignes marqués.

Dans la table "fichier", vous devez mettre une clef étrangère pointant vers la table candidat.
Au choix le lien vers le fichier, ou bien stocker son contenu dans un colonne à cet effet.
Marquer la ligne si celle-ci doit être en suppression logique ou pas.

Je maintiens que le fichier doit être associé au candidat et non à autre chose.
Il me semble logique que si on supprime le candidat, on doit aussi supprimer toutes les pièces jointes.

Vouloir supprimer les fichiers quand on supprime une ligne, soit de la table "candidat-diplôme", ou soit de la table "prouver" n'a plus aucune raison d'être.
On peut choisir l'option de marquer la ligne dans la table fichier, si l'on désire supprimer ce dit fichier.

De même, la table "prouver" n'a plus aucune raison d'être et peut-être remplacé par un simple caractères de confirmation.
L'important est la confirmation par un responsable, et non par le candidat, comme quoi celui-ci a bien fournit le duplicata de son diplôme.

Je crois que nous avions eu, il y a déjà fort longtemps, une discussion au sujet de la suppression physique (en cascade) et logique (no action) des lignes dans les tables.
La suppression en cascade, suite à une erreur de manipulation, peut-être une source de problème.
Le problème ne se pose pas si c'est une suppression logique.

Je pense que vouloir trop automatiser certaines manipulations sont sources d'erreurs.

@+

Citation:

---

Envoyé par CinePhil

Encore une question, cependant, à destination plus particulièrement de SQLPro :
Est-ce que ce non de déclenchement de trigger si la suppression n'est pas lancée explicitement par un DELETE en SQL est conforme à la norme SQL ou est-ce une déficience de MySQMerde ? ;)

---

Tout événements SQL de type DELETE qu'il soit directs (DELETE) ou indirects (MERGE) doivent conduire à l'exécution du déclencheur.
Le cas du TRUNCATE est particulier : soit le TRUNCATE exécute le déclencheur, soit le TRUNCATE ne doit pas s'exécuter s'il y a un déclencheur (et donc retourner une erreur).
Le but étant de préserver l'intégrité des données.

A +

Citation:

---

Envoyé par SQLpro

Tout événements SQL de type DELETE qu'il soit directs (DELETE) ou indirects (MERGE) doivent conduire à l'exécution du déclencheur.

---

J'avoue ne pas connaitre la réponse du point de vue de la norme et j'attendais la réponse :)

Si je comprends bien la réponse fournie, un DELETE qu'il soit ou pas encapsulé dans un trigger, devrait obligatoirement lui même déclencher le trigger suivant (s'il existe).

Ce qui pose le problème des déclencheurs récursifs.

Si on s'en tient au fait que le SQL soit la conséquence de l'algèbre relationnelle, alors, il ne devrait pas être possible d'entrer en boucle infinie.
Ou alors il y a des trous dans la raquette ;)

Est-ce qu'on peut avoir un lien, ou une référence à la norme, qui parle de ce point particulier à l'image des précisions sur l'ordre TRUNCATE ?

@CinePhil :
En espérant que la version soit > 8.0.
Il ne reste plus qu'a faire une jolie procédure récursive à propos de INFORMATION_SCHEMA.KEY_COLUMN_USAGE pour trouver toutes les tables enfantes eu delà de la première génération pour gérer les suppressions en cascade.

Citation:

---

Envoyé par Michel.Priori

J'avoue ne pas connaitre la réponse du point de vue de la norme et j'attendais la réponse :)

Si je comprends bien la réponse fournie, un DELETE qu'il soit ou pas encapsulé dans un trigger, devrait obligatoirement lui même déclencher le trigger suivant (s'il existe).

Ce qui pose le problème des déclencheurs récursifs.

---

Oui et parfaitement géré dans SQL Server via :
1) ALTER DATABASE CURRENT SET RECURSIVE_TRIGGER {ON | OFF }
2) EXEC sp_configure 'nested triggers' { 0 |1 }

Citation:

---

Si on s'en tient au fait que le SQL soit la conséquence de l'algèbre relationnelle, alors, il ne devrait pas être possible d'entrer en boucle infinie.
Ou alors il y a des trous dans la raquette ;)

---

Et pourquoi donc ? L'algèbre en général n'interdit pas l'exécution récursive des opérations. Soit l'algèbre est ouverte (donc possibilité de passer dans un autre "univers" algébrique), soit fermé. Et dans le cas de la fermeture, l'implémentation de la fermeture transitive conduit à la récursivité !

Citation:

---

Est-ce qu'on peut avoir un lien, ou une référence à la norme, qui parle de ce point particulier à l'image des précisions sur l'ordre TRUNCATE ?

---

Je n'ai rien vu à ce sujet…. Mais
1) ce qui n'est pas interdit est permis
2) la notion de déclencheur est apparue récemment, car avant c'était la contrainte de type ASSERTION.

A +