Module 1Maîtriser le langage de programmation procédurale sous MySQL
Chapitre 9 sur 12
Les déclencheurs BEFORE et AFTER sur INSERT, UPDATE et DELETE, OLD et NEW, les exemples du résumé, les avantages et les restrictions.
Objectifs
Avec ce chapitre, tu sauras…
BEFORE ou AFTER sur INSERT, UPDATE ou DELETE ;OLD et NEW ;Leçon 1
Un déclencheur sert à valider les données, à enregistrer les anciennes et nouvelles valeurs dans une table d'audit (journal, log), ou à faire respecter les règles métier. Par exemple, il peut s'exécuter quand une ligne est insérée dans une table, ou quand certaines colonnes sont modifiées.
Vidéo · À regarder
FROptimisation base de données #08 : Les déclencheurs ou les triggers
coursaline - Rachid Saad · YouTube
Leçon 2
| Élément | Signification |
|---|---|
BEFORE | AFTER | quand le déclencheur s'exécute : avant ou après l'instruction |
INSERT | UPDATE | DELETE | l'opération LMD qui le déclenche |
ON nom_table | la table associée |
FOR EACH ROW | déclencheur au niveau ligne : il s'exécute pour chaque ligne touchée (obligatoire en MySQL) |
| corps |
Leçon 3
Leçon 4
| Déclencheur | OLD | NEW |
|---|---|---|
| INSERT | non (pas d'ancienne ligne) | oui |
| UPDATE | oui | oui |
| DELETE | oui | non (pas de nouvelle ligne) |
Vidéo · À regarder
FRLeçon 5
Vidéo · À regarder
FRLeçon 6
Leçon 7
Leçon 8
1Un déclencheur s'exécute…
2Dans un déclencheur INSERT, on peut utiliser…
3Dans un déclencheur DELETE, on peut utiliser…
4Pour refuser une insertion invalide, on utilise un déclencheur…
5FOR EACH ROW signifie…
6Pour modifier un déclencheur dans MySQL, on…
7Que deviennent les déclencheurs d'une table supprimée ?
8Combien de combinaisons moment/événement existe-t-il ?
L'essentiel du chapitre
INSERT, UPDATE ou DELETE d'une table ; sert à valider, auditer, faire respecter les règles métier.CREATE TRIGGER nom BEFORE|AFTER INSERT|UPDATE|DELETE ON table FOR EACH ROW corps; (BEGIN … END + DELIMITER si plusieurs instructions).SIGNAL, SET NEW.col = …) ; AFTER : journaliser / mettre à jour d'autres tables.NEW : INSERT et UPDATE ; OLD : UPDATE et DELETE.SHOW TRIGGERS;, DROP TRIGGER IF EXISTS nom; ; modifier = supprimer + recréer (avec LOCK TABLES) ; supprimer la table supprime ses déclencheurs.les opérations à faire : une instruction, ou un bloc BEGIN … END (avec DELIMITER) |
Vidéo · À regarder
FROptimisation base de données #08 : Les déclencheurs ou les triggers
coursaline - Rachid Saad · YouTube
Optimisation base de données #08 : Les déclencheurs ou les triggers
coursaline - Rachid Saad · YouTube
Optimisation base de données #08 : Les déclencheurs ou les triggers
coursaline - Rachid Saad · YouTube
Ce que tu vas voir (5 min) : les chapitres « De quoi s'agit-il ? », « Qu'est-ce qu'un trigger ? », « Exemple simple d'un trigger », « À quoi servent les triggers ? ». Le formateur explique ce qu'est un trigger, montre un exemple simple et à quoi servent les triggers.
CREATE TRIGGER [nom_declencheur]
[before | after]
{insert | update | delete}
ON [nom_table]
[for each row]
[corps_declencheur]BEFORE exécutent leur action avant l'instruction (l'événement) : ils servent à contrôler ou corriger les données (on peut encore modifier NEW ou refuser l'opération avec SIGNAL).AFTER exécutent leur action après l'instruction : ils servent à enregistrer ou mettre à jour d'autres tables (audit, totaux).BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, AFTER DELETE.OLD donne accès aux données de la ligne avant la modification (celles qui sont remplacées ou supprimées) ;NEW donne accès aux données entrantes, qui remplaceront l'ancienne ligne en cas de succès.On écrit OLD.colonne et NEW.colonne : NEW.date_naissance, OLD.quantity…
BEFORE INSERT : refuser un client mineur
DELIMITER //
CREATE TRIGGER tr_contrainte_age BEFORE INSERT
ON clients
FOR EACH ROW
BEGIN
DECLARE MinAge INT;
SET MinAge := 18;
IF NEW.date_naissance > (SELECT DATE_SUB(curdate(), interval MinAge year)) THEN
SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'La personne doit etre agee de plus de 18 ans.';
END IF;
END //
DELIMITER ;
-- tester le déclencheur par une opération INSERT sur la table clients
INSERT INTO clients (nom, prenom, date_naissance, adresse)
VALUES ("Slami", "Saida", "2010-5-22", "casa");
-- Error Code: 1644. La personne doit etre agee de plus de 18 ans.DATE_SUB(curdate(), interval 18 year) donne la date d'il y a 18 ans. Si la personne est née après cette date, elle a moins de 18 ans : SIGNAL refuse l'insertion (le client n'est pas ajouté).
Le code de la diapositive écrit FOR clients au lieu de ON clients FOR EACH ROW (la capture d'écran qui suit, elle, est correcte). Sans ON ni FOR EACH ROW, MySQL refuse la création.
AFTER INSERT : mettre à jour l'âge moyen
-- Initialisation de l'âge moyen
CREATE TABLE moyenne_age (moyenne double);
INSERT INTO moyenne_age
SELECT AVG(TIMESTAMPDIFF(YEAR, date_naissance, CURDATE())) FROM clients;
SET SQL_SAFE_UPDATES = 0;
-- Création du trigger
DROP TRIGGER IF EXISTS mise_ajour_moyenne_age;
CREATE TRIGGER mise_ajour_moyenne_age AFTER INSERT
ON clients
FOR EACH ROW
UPDATE moyenne_age
SET moyenne = (SELECT AVG(TIMESTAMPDIFF(YEAR, date_naissance, CURDATE())) FROM clients);
-- déclencher le trigger
INSERT INTO clients (nom, prenom, date_naissance, adresse)
VALUES ("Dadi", "Hamza", "2000-5-22", "casa");
-- afficher la nouvelle valeur de l'âge moyen
SELECT * FROM moyenne_age;TIMESTAMPDIFF(YEAR, date_naissance, CURDATE()) calcule l'âge ;SET SQL_SAFE_UPDATES = 0 désactive la protection de Workbench contre les UPDATE sans WHERE (chapitre 1) ;BEGIN … END ni de DELIMITER.Le corps du déclencheur est écrit SET moyenne = age SELECT (AVG(…)) FROM clients : ce n'est pas du SQL valide. Il faut une sous-requête entre parenthèses : SET moyenne = (SELECT AVG(…) FROM clients). La même erreur de parenthèse est dans l'INSERT … SELECT (AVG(…) d'initialisation.
BEFORE UPDATE : contrôler la modification de l'âge
DELIMITER //
CREATE TRIGGER tr_modification_age_controle BEFORE UPDATE
ON clients
FOR EACH ROW
BEGIN
DECLARE MinAge INT;
SET MinAge := 18;
IF NEW.date_naissance > (SELECT DATE_SUB(curdate(), interval MinAge year)) THEN
SIGNAL SQLSTATE '50002' SET MESSAGE_TEXT = 'La personne doit etre agee de plus de 18 ans.';
END IF;
END //
DELIMITER ;
-- tester le trigger
UPDATE clients SET date_naissance = '2010-9-23' WHERE id = 6; -- refusé (moins de 18 ans)
UPDATE clients SET date_naissance = '1999-9-23' WHERE id = 7; -- acceptéDeux fautes de frappe : NEW.date_naissace → NEW.date_naissance et SGLSTATE → SQLSTATE. Les id 6 et 7 dépendent des clients que tu as ajoutés : choisis des id qui existent dans ta table.
AFTER UPDATE : journaliser les changements de quantité
Ce déclencheur after_sales_update s'exécute après chaque mise à jour d'une ligne de la table des ventes (sales). Si la quantité change, il insère une ligne dans la table SalesChanges pour garder l'historique.
DELIMITER //
CREATE TRIGGER after_sales_update AFTER UPDATE
ON sales
FOR EACH ROW
BEGIN
IF OLD.quantity <> NEW.quantity THEN
INSERT INTO SalesChanges (salesId, beforeQuantity, afterQuantity)
VALUES (OLD.id, OLD.quantity, NEW.quantity);
END IF;
END //
DELIMITER ;Le texte dit que ce déclencheur est exécuté « avant » la mise à jour : c'est un AFTER UPDATE, il s'exécute après. Le code ouvre avec DELIMITER // mais se termine par END $$ : il faut END //.
BEFORE DELETE : archiver avant de supprimer
Avant de supprimer un salaire, ce déclencheur le sauvegarde dans la table d'archivage SalaryArchives :
DELIMITER //
CREATE TRIGGER before_salaries_delete BEFORE DELETE
ON salaries
FOR EACH ROW
BEGIN
INSERT INTO SalaryArchives (employeeNumber, validFrom, amount)
VALUES (OLD.employeeNumber, OLD.validFrom, OLD.amount);
END //
DELIMITER ;AFTER DELETE : mettre à jour le budget
Après la suppression d'un salaire, ce déclencheur met à jour le budget des salaires stocké dans la table SalaryBudgets :
CREATE TRIGGER after_salaries_delete AFTER DELETE
ON salaries
FOR EACH ROW
UPDATE SalaryBudgets
SET total = total - OLD.salary;Les tables sales, SalesChanges, salaries, SalaryArchives et SalaryBudgets ne sont pas créées dans le résumé. Le script complet pour tester les quatre derniers exemples est dans le corrigé 9.2.
Les déclencheurs contribuent :
BEFORE UPDATE sur une table donnée, mais il peut contenir plusieurs instructions ;CALL (dans le résumé) et ne peuvent pas créer de tables ou de vues temporaires ;FOLLOWS ou PRECEDES : CREATE TRIGGER t2 BEFORE UPDATE ON clients FOR EACH ROW FOLLOWS t1 ….CALL, à condition que la procédure ne renvoie pas de résultat au client et ne fasse ni COMMIT ni ROLLBACK.UPDATE clients dans un trigger sur clients → erreur 1442). Pour changer la ligne en cours, on modifie NEW dans un BEFORE : SET NEW.nom = UPPER(NEW.nom);.SHOW TRIGGERS; -- lister les déclencheurs de la base
DROP TRIGGER [IF EXISTS] [nom_bd.]nom_declencheur; -- supprimerRemarque : si on supprime une table, MySQL supprime automatiquement tous ses déclencheurs.
Il n'existe pas d'instruction pour modifier un déclencheur : on le supprime et on le recrée. Pour qu'aucune autre session ne modifie la table pendant ce court moment (sans déclencheur), on la verrouille avec LOCK TABLES (chapitre 3) :
LOCK TABLES t1 WRITE;
DROP TRIGGER t1_bi;
DELIMITER $$
CREATE TRIGGER t1_bi BEFORE INSERT
ON t1
FOR EACH ROW
BEGIN
…
END $$
DELIMITER ;
UNLOCK TABLES;NB : MariaDB, depuis la version 10.1.4, accepte CREATE OR REPLACE TRIGGER, qui fait les deux en une instruction. MySQL ne la connaît pas.
Dans eshop_app_db : (1) crée la table historique_prix (id, id_produit, ancien_prix, nouveau_prix, date_modif) et le déclencheur tr_historique_prix (AFTER UPDATE sur produit) qui y enregistre chaque changement de prix ; (2) crée le déclencheur tr_stock_positif (BEFORE UPDATE sur produit) qui refuse un stock négatif avec le message « Stock négatif interdit ». Teste les deux.
Crée les tables nécessaires et teste les quatre déclencheurs after_sales_update, before_salaries_delete et after_salaries_delete : modifie une quantité dans sales, supprime un salaire, et vérifie le contenu de SalesChanges, SalaryArchives et SalaryBudgets.
Écris le déclencheur tr_nom_majuscule (BEFORE INSERT sur clients) qui met automatiquement le nom en majuscules et, si l'adresse est vide ou NULL, la remplace par 'Agadir'. Teste avec INSERT INTO clients (nom, prenom, date_naissance) VALUES ('benali', 'Sara', '1998-04-10');.
Ce que tu vas voir (3 min) : les chapitres « Syntaxe d'un trigger ». Il détaille la syntaxe de CREATE TRIGGER : le moment, l'événement, la table et FOR EACH ROW.
Ce que tu vas voir (jusqu'à la fin) : les chapitres « Quand utiliser NEW », « Quand utiliser OLD ». Il montre quand utiliser NEW (INSERT, UPDATE) et quand utiliser OLD (UPDATE, DELETE), avec des exemples.
Ce que tu vas voir (4 min) : les chapitres « Trigger qui met à jour d'autres tables ». Il écrit un trigger qui met à jour une autre table, comme les exemples AFTER INSERT et AFTER DELETE de cette section.