Discussion :

[CinePhil]

Bonjour,

J'aimerais avoir l'avis des experts en SQL sur le comportement de MySQL qui ignore les éventuels triggers sur les tables touchées par une contrainte CASCADE de clé étrangère.

J'ai manifesté mon étonnement dans cette discussion mais ericd69, qui semble très bien connaître MySQL, donne une explication de la chose, en la trouvant quand même un tantinet piégeuse !

Qu'en est-il dans les autres SGBD ?
Que dit la norme SQL à ce sujet ?

[SQLpro]

Dans les SGBDR les déclencheurs BEFORE produisent leurs effets avant les contraintes (ce qui permet de donner une valeur à une clef primaire par exemple) et les déclencheurs AFTER produisent leurs effets après les contraintes, sauf contrainte déferrée

Les contraintes déferrable sont vérifiées en fin de transaction si elles sont effectivement déferrées et s'il y a circularité de référence, la pose de contrainte est empêchée si la contrainte n'est pas INITIALLY DEFFERRED.

Dans certains SGBDR, les cycles sont interdits. Exemple : SQL Server qui n'accepte pas les contraintes déferrables et interdit les cycles de références.

Visiblement le très mauvais MySQL ne sait pas vérifier correctement les cycles de références.

A +

[CinePhil]

Dans les SGBDR les déclencheurs BEFORE produisent leurs effets avant les contraintes (ce qui permet de donner une valeur à une clef primaire par exemple)

Tu veux dire que dans un TRIGGER BEFORE INSERT je peux utiliser la valeur de l'identifiant auto-incrémenté qui est créé par l'insertion ? Je pensais qu'il fallait que l'insertion ait eu lieu (même non commitée) donc utilisable seulement dans un TRIGGER AFTER INSERT !

OK pour le reste mais donc, pour en revenir à ma question, si un UPDATE tableA entraîne, par cascade, un UPDATE tableB parce qu'elle référence tableA avec une contrainte ON UPDATE CASCADE, un TRIGGER ON tableB BEFORE UPDATE (ou AFTER UPDATE) doit-il normalement se déclencher ?

Visiblement le très mauvais MySQL ne sait pas vérifier correctement les cycles de références.

Un défaut de plus, c'est bien ce qu'il me semblait, même si ce n'est pas un bug puisque apparemment c'est volontaire.

[SQLpro]

Tu veux dire que dans un TRIGGER BEFORE INSERT je peux utiliser la valeur de l'identifiant auto-incrémenté qui est créé par l'insertion ? Je pensais qu'il fallait que l'insertion ait eu lieu (même non commitée) donc utilisable seulement dans un TRIGGER AFTER INSERT !

Dans un trigger BEFORE INSERT, l'auto incrément n'a pas encore été effectué ou affecté. Par exemple dans Oracle, si tu veux auto incrémenter avec une séquence, c'est le moment d'affecter la valeur du séquenceur (NEXT_VALUE....) pour qu'ensuite la contrainte de clef primaire vérifie l'unicité...

OK pour le reste mais donc, pour en revenir à ma question, si un UPDATE tableA entraîne, par cascade, un UPDATE tableB parce qu'elle référence tableA avec une contrainte ON UPDATE CASCADE, un TRIGGER ON tableB BEFORE UPDATE (ou AFTER UPDATE) doit-il normalement se déclencher ?

L'ensemble des cascade doit se faire de la même façon. Si nous sommes dans un trigger BEFORE, la cascade n'a pas encore été propagée dans les tables filles. Et dans les triggers after, la propagation est effective... Il y a donc un empilement des appels des déclencheurs "imbriqués".

Note d'ailleurs que MySQL ne permet pas non plus des triggers récursifs, par exemple de modifier (UPDATE) la table cible d'un déclencheur UPDATE (ni d'ailleurs Oracle), alors que MS SQL Server sait très bien le faire... Idem pour des triggers "ping-pong" : Un trigger sur table A met à jour la table B et vice versa....

Un défaut de plus, c'est bien ce qu'il me semblait, même si ce n'est pas un bug puisque apparemment c'est volontaire.

Oui, je dirais plus un bug volontaire par absence de prévision de ce que dois faire un SGBDR !

A +