Discussion :

[CinePhil]

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 ?

[SQLpro]

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 +

[CinePhil]

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 ?

[Artemus24]

Salut à tous.

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

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 ?

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 :

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

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.


@+

[SQLpro]

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 +

[CinePhil]

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.

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.

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

[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 ?

[SQLpro]

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 +

[Artemus24]

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.

@+

[Michel.Priori]

@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.