Tables utilisées par la requête
Comme mentionné auparavant, il est impossible de modifier les données d'une table utilisée par la requête ayant déclenché le
trigger à l'intérieur de celui-ci.
Cette restriction est importante, et peut remettre en question l'utilisation de certains triggers.
Exemple : le trigger AFTER INSERT ON Adoption modifie les données de la table Animal. Si l'on exécute la requête
suivante, cela posera problème.
Code : SQL
INSERT INTO Adoption (animal_id, client_id, date_reservation, prix,
paye)
SELECT Animal.id, 4, NOW(), COALESCE(Race.prix, Espece.prix), FALSE
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
LEFT JOIN Race ON Race.id = Animal.race_id
WHERE Animal.nom = 'Boucan' AND Animal.espece_id = 2;
Code : Console
ERROR 1442 (HY000): Can't update table 'animal' in stored function/trigger because it is
Le trigger échoue puisque la table Animal est utilisée par la requête INSERT qui le déclenche. L'insertion elle-même est donc
finalement annulée.
Clés étrangères
Une suppression ou modification de données déclenchée par une clé étrangère ne provoquera pas l'exécution du trigger
correspondant.
Par exemple, la colonne Animal.race_id possède une clé étrangère, qui référence la colonne Race.id . Cette clé étrangère a été
définie avec l'option ON DELETE SET NULL. Donc en cas de suppression d'une race, tous les animaux de cette race seront
modifiés, et leur race_id changée en NULL. Il s'agit donc d'une modification de données. Mais comme cette modification a été
déclenchée par une contrainte de clé étrangère, les éventuels triggers BEFORE UPDATE et AFTER UPDATE de la table
Animal ne seront pas déclenchés.
En cas d'utilisation de triggers sur des tables présentant des clés étrangères avec ces options, il vaut donc mieux supprimer
celles-ci et déplacer ce comportement dans des triggers.
Une autre solution est de ne pas utiliser les triggers sur les tables concernées. Vous pouvez alors remplacer les triggers par
l'utilisation de procédures stockées et/ou de transactions.
Qu'avons-nous comme clés étrangères dans nos tables ?
Race : CONSTRAINT fk_race_espece_id FOREIGN KEY (espece_id) REFERENCES Espece (id)
ON DELETE CASCADE;
Animal : CONSTRAINT fk_race_id FOREIGN KEY (race_id) REFERENCES Race (id) ON DELETE
SET NULL;
Animal : CONSTRAINT fk_espece_id FOREIGN KEY (espece_id) REFERENCES Espece (id);
Animal : CONSTRAINT fk_mere_id FOREIGN KEY (mere_id) REFERENCES Animal (id) ON
DELETE SET NULL;
Animal : CONSTRAINT fk_pere_id FOREIGN KEY (pere_id) REFERENCES Animal (id) ON
DELETE SET NULL;
Quatre d'entre elles pourraient donc poser problème. Quatre, sur cinq ! Ce n'est donc pas anodin comme restriction !
On va donc modifier nos clés étrangères pour qu'elles reprennent leur comportement par défaut. Il faudra ensuite créer (ou
Partie 5 : Sécuriser et automatiser ses actions
325/414
www.openclassrooms.com
Comme mentionné auparavant, il est impossible de modifier les données d'une table utilisée par la requête ayant déclenché le
trigger à l'intérieur de celui-ci.
Cette restriction est importante, et peut remettre en question l'utilisation de certains triggers.
Exemple : le trigger AFTER INSERT ON Adoption modifie les données de la table Animal. Si l'on exécute la requête
suivante, cela posera problème.
Code : SQL
INSERT INTO Adoption (animal_id, client_id, date_reservation, prix,
paye)
SELECT Animal.id, 4, NOW(), COALESCE(Race.prix, Espece.prix), FALSE
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
LEFT JOIN Race ON Race.id = Animal.race_id
WHERE Animal.nom = 'Boucan' AND Animal.espece_id = 2;
Code : Console
ERROR 1442 (HY000): Can't update table 'animal' in stored function/trigger because it is
Le trigger échoue puisque la table Animal est utilisée par la requête INSERT qui le déclenche. L'insertion elle-même est donc
finalement annulée.
Clés étrangères
Une suppression ou modification de données déclenchée par une clé étrangère ne provoquera pas l'exécution du trigger
correspondant.
Par exemple, la colonne Animal.race_id possède une clé étrangère, qui référence la colonne Race.id . Cette clé étrangère a été
définie avec l'option ON DELETE SET NULL. Donc en cas de suppression d'une race, tous les animaux de cette race seront
modifiés, et leur race_id changée en NULL. Il s'agit donc d'une modification de données. Mais comme cette modification a été
déclenchée par une contrainte de clé étrangère, les éventuels triggers BEFORE UPDATE et AFTER UPDATE de la table
Animal ne seront pas déclenchés.
En cas d'utilisation de triggers sur des tables présentant des clés étrangères avec ces options, il vaut donc mieux supprimer
celles-ci et déplacer ce comportement dans des triggers.
Une autre solution est de ne pas utiliser les triggers sur les tables concernées. Vous pouvez alors remplacer les triggers par
l'utilisation de procédures stockées et/ou de transactions.
Qu'avons-nous comme clés étrangères dans nos tables ?
Race : CONSTRAINT fk_race_espece_id FOREIGN KEY (espece_id) REFERENCES Espece (id)
ON DELETE CASCADE;
Animal : CONSTRAINT fk_race_id FOREIGN KEY (race_id) REFERENCES Race (id) ON DELETE
SET NULL;
Animal : CONSTRAINT fk_espece_id FOREIGN KEY (espece_id) REFERENCES Espece (id);
Animal : CONSTRAINT fk_mere_id FOREIGN KEY (mere_id) REFERENCES Animal (id) ON
DELETE SET NULL;
Animal : CONSTRAINT fk_pere_id FOREIGN KEY (pere_id) REFERENCES Animal (id) ON
DELETE SET NULL;
Quatre d'entre elles pourraient donc poser problème. Quatre, sur cinq ! Ce n'est donc pas anodin comme restriction !
On va donc modifier nos clés étrangères pour qu'elles reprennent leur comportement par défaut. Il faudra ensuite créer (ou
Partie 5 : Sécuriser et automatiser ses actions
325/414
www.openclassrooms.com