Trigger SQL avant suppression
minimatt
-
minimatt -
minimatt -
Bonjour,
Je travaille actuellement sur un projet utilisant une base de données MySQL 5.0.41, et à cause de son rôle plutôt critique et du nombre de tables liées entre elles, je souhaiterais ajouter des contraintes d'intégrité référentielles. Malheureusement, c'est la première fois que je touche à ces bidules...
Disons que j'ai une table A contenant un ID et un nom, et une table B faisant référence à la table A pour désigner la personne concernée.
Maintenant, je voudrais qu'un enregistrement de la table A ne puisse être supprimé s'il en existe des références dans la table A, histoire de ne pas me retrouver avec un enregistrement en B faisant référence à quelqu'un qui n'existe plus.... Après plusieurs heures de recherches et d'essais, je me retrouve là :
Malheureusement, ma syntaxe n'est pas bonne dans le.... if ?!
Un coup de main, s'y'ouplait ? C'est vraiment important ='(
Je travaille actuellement sur un projet utilisant une base de données MySQL 5.0.41, et à cause de son rôle plutôt critique et du nombre de tables liées entre elles, je souhaiterais ajouter des contraintes d'intégrité référentielles. Malheureusement, c'est la première fois que je touche à ces bidules...
Disons que j'ai une table A contenant un ID et un nom, et une table B faisant référence à la table A pour désigner la personne concernée.
+----------- A -----------+ +----------- B -----------+
| id smallint(3) | | ... ............ |
| name varchar(255) | | who smallint(3) |
+-------------------------+ | ... ............ |
+-------------------------+
Maintenant, je voudrais qu'un enregistrement de la table A ne puisse être supprimé s'il en existe des références dans la table A, histoire de ne pas me retrouver avec un enregistrement en B faisant référence à quelqu'un qui n'existe plus.... Après plusieurs heures de recherches et d'essais, je me retrouve là :
CREATE TRIGGER a_before_delete BEFORE DELETE ON A FOR EACH ROW
BEGIN
IF EXISTS (SELECT * FROM B WHERE who = old.id)
BEGIN
RAISERROR ('Impossible de supprimer une personne encore référencée');
ROLLBACK TRANSACTION;
END;
END;
Malheureusement, ma syntaxe n'est pas bonne dans le.... if ?!
Un coup de main, s'y'ouplait ? C'est vraiment important ='(
A voir également:
- Trigger SQL avant suppression
- Forcer suppression fichier - Guide
- Avant browser - Télécharger - Navigateurs
- Suppression compte gmail - Guide
- Comment remettre ma page d'accueil comme avant - Guide
- Comment revenir à ma page d'accueil "google.fr" ? ✓ - Forum Google Chrome
4 réponses
Salut,
en principe, une fois que l'on a mis une contrainte d'intégrité référentielle, pas besoin de trigger, c'est le SGBD lui-même qui va te dire qu'il reste des records dans B et que tu ne peux supprimer A.
en principe, une fois que l'on a mis une contrainte d'intégrité référentielle, pas besoin de trigger, c'est le SGBD lui-même qui va te dire qu'il reste des records dans B et que tu ne peux supprimer A.
Salut
je ne suis pas très versé dans MySQL, je viens juste te filer une ou deux pistes à explorer.
1. la gestion de la transaction : ce n'est pas à un trigger de valider ou d'invalider une transaction. ton trigger doit se contenter de lever une exception, rien de plus
2. au lieu de tester l'existence de x lignes de y colonnes dans le if, je conseillerais plutôt
a. affecter select count(1) from B where who = old.id à une variable
b. vérifier si la variable est différente de 0 => exception
en règle générale, il faut éviter de lire des données dont on n'a pas besoin. ici, tu lis toutes les colonnes des x lignes de B en correspondance avec ton id supprimé alors qu'en fait seul le nombre de lignes t'intéresse :-(
3. (ça c'est bonus) certes, tu log tes erreurs, mais le système sera toujours infiniment plus rapide que le plus optimisé de tes codes => sers-toi donc de l'intégrité référentielle comme te l'a conseillé blux
je ne suis pas très versé dans MySQL, je viens juste te filer une ou deux pistes à explorer.
1. la gestion de la transaction : ce n'est pas à un trigger de valider ou d'invalider une transaction. ton trigger doit se contenter de lever une exception, rien de plus
2. au lieu de tester l'existence de x lignes de y colonnes dans le if, je conseillerais plutôt
a. affecter select count(1) from B where who = old.id à une variable
b. vérifier si la variable est différente de 0 => exception
en règle générale, il faut éviter de lire des données dont on n'a pas besoin. ici, tu lis toutes les colonnes des x lignes de B en correspondance avec ton id supprimé alors qu'en fait seul le nombre de lignes t'intéresse :-(
3. (ça c'est bonus) certes, tu log tes erreurs, mais le système sera toujours infiniment plus rapide que le plus optimisé de tes codes => sers-toi donc de l'intégrité référentielle comme te l'a conseillé blux
Effectivement, je suis pas du tout au point niveau optimisation =')
Malheureusement, même avec une contrainte d'intégrité référentielle, je n'y arrive pas ! J'arrive bien à ajouter la contrainte sur la table B, mais je peux tout de même ajouter des enregistrements dans ladite table qui ne correspondent à aucun ID de la table A, ou même supprimer un enregistrement de la table A auquel sont reliés des enregistrements de B...... La requête ajoutant la contrainte est pourtant bien exécutée :
Malheureusement, même avec une contrainte d'intégrité référentielle, je n'y arrive pas ! J'arrive bien à ajouter la contrainte sur la table B, mais je peux tout de même ajouter des enregistrements dans ladite table qui ne correspondent à aucun ID de la table A, ou même supprimer un enregistrement de la table A auquel sont reliés des enregistrements de B...... La requête ajoutant la contrainte est pourtant bien exécutée :
ALTER TABLE B ADD CONSTRAINT who_a_id FOREIGN KEY (who) REFERENCES A(id);Qu'est-ce que j'oublie ?
Salut,
J'ai retrouvé un vieux MySQL (Ver 14.12 Distrib 5.0.19 for Win32) où j'ai pu tester quelques bricoles... il en ressort que (à l'instar d'autres SGBD) la contrainte ne s'applique pas pour une valeur NULL ; par contre, une valeur non trouvée dans la colonne référencée sera rejetée. même sanction pour la suppression de la ligne référencée.
donc, essaie d'ajouter une contrainte NOT NULL sur la colonne who pour voir si tu n'atteindrais pas enfin le fonctionnement désiré.
J'ai retrouvé un vieux MySQL (Ver 14.12 Distrib 5.0.19 for Win32) où j'ai pu tester quelques bricoles... il en ressort que (à l'instar d'autres SGBD) la contrainte ne s'applique pas pour une valeur NULL ; par contre, une valeur non trouvée dans la colonne référencée sera rejetée. même sanction pour la suppression de la ligne référencée.
donc, essaie d'ajouter une contrainte NOT NULL sur la colonne who pour voir si tu n'atteindrais pas enfin le fonctionnement désiré.
Dans ce cas, disons que je préfère les triggers aux contraintes, histoire de pouvoir insérer dans une table log les éventuelles erreurs (ça, j'y arrive déjà ='P). Quelle est l'erreur de syntaxe dans mon bout de code ?
Aucune idée...