Trigger MySQL : BEFORE, AFTER, NEW et OLD avec exemples

Un trigger (en français, déclencheur) est un programme stocké attaché à une table, que MySQL exécute automatiquement avant ou après chaque INSERT, UPDATE ou DELETE sur cette table, une fois pour chaque ligne concernée. Dans son code, NEW désigne la ligne telle qu'elle va être (ou a été) écrite, et OLD la ligne telle qu'elle était. Ce chapitre montre comment écrire un trigger de contrôle, de correction et de propagation sur la base parc, et quelles sont ses limites.

La syntaxe de CREATE TRIGGER

DELIMITER //
CREATE TRIGGER nom_trigger
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON nom_table
FOR EACH ROW
[ { FOLLOWS | PRECEDES } autre_trigger ]
BEGIN
  -- instructions ; NEW.colonne et OLD.colonne désignent la ligne en cours
END //
DELIMITER ;

• Le moment : BEFORE s'exécute avant l'écriture de la ligne, AFTER après. Avec trois événements, cela fait six combinaisons possibles par table.
• FOR EACH ROW est obligatoire en MySQL : le trigger s'exécute une fois par ligne. Un UPDATE qui modifie dix tickets déclenche dix fois le trigger.
• Le nom est unique dans la base. Une convention lisible : trg_<table>_<rôle>, comme trg_intervention_technicien.
• Créer un trigger demande le privilège TRIGGER sur la table.

NEW et OLD

OLD est toujours en lecture seule. NEW peut être modifié avec SET NEW.colonne = valeur, mais seulement dans un trigger BEFORE : dans un trigger AFTER, la ligne est déjà écrite, et MySQL refuse de créer le trigger (erreur 1362, Updating of NEW row is not allowed in after trigger).

Contrôler : refuser une modification avec SIGNAL

Seul un membre du service informatique peut être technicien d'une intervention. Une clé étrangère ne sait pas l'exprimer (elle vérifie que l'employé existe, pas son service), une contrainte CHECK non plus (elle ne lit pas d'autre table). Un trigger BEFORE INSERT le peut :

DELIMITER //
CREATE TRIGGER trg_intervention_technicien
BEFORE INSERT ON intervention
FOR EACH ROW
BEGIN
  IF NOT EXISTS (SELECT 1
                 FROM employe e
                 JOIN service s ON s.id_service = e.id_service
                 WHERE e.id_employe = NEW.id_technicien AND s.code = 'INFO') THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Seul un membre du service informatique peut intervenir';
  END IF;
END //
DELIMITER ;

INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min, compte_rendu)
VALUES (12, 9, '2026-10-22 10:00:00', 15, 'Clavier changé');
-- ERROR 1644 (45000): Seul un membre du service informatique peut intervenir

Inès Bonnet (9) travaille à la logistique : l'insertion est refusée, quelle que soit l'application qui la tente. Un second contrôle sur la même table et le même événement : une intervention ne peut pas précéder l'ouverture de son ticket.

DELIMITER //
CREATE TRIGGER trg_intervention_date
BEFORE INSERT ON intervention
FOR EACH ROW
FOLLOWS trg_intervention_technicien
BEGIN
  DECLARE v_ouverture DATETIME;

  SELECT date_ouverture INTO v_ouverture
  FROM ticket
  WHERE id_ticket = NEW.id_ticket;

  IF NEW.date_intervention < v_ouverture THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Intervention antérieure à l''ouverture du ticket';
  END IF;
END //
DELIMITER ;

INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min, compte_rendu)
VALUES (12, 11, '2026-10-20 10:00:00', 15, 'Clavier changé');
-- ERROR 1644 (45000): Intervention antérieure à l'ouverture du ticket

Le ticket 12 a été ouvert le 21 octobre. Plusieurs triggers peuvent exister pour le même moment et le même événement : ils s'exécutent dans leur ordre de création, ou dans l'ordre que fixent FOLLOWS et PRECEDES. Ici, le contrôle du technicien passe en premier.

Corriger : modifier NEW avant l'écriture

Un trigger BEFORE peut compléter ou normaliser la ligne. À l'ouverture d'un ticket, on retire les espaces en trop du titre, on refuse un titre vide, et un ticket qui parle de rançongiciel passe d'office en priorité critique :

DELIMITER //
CREATE TRIGGER trg_ticket_avant_ajout
BEFORE INSERT ON ticket
FOR EACH ROW
BEGIN
  SET NEW.titre = TRIM(NEW.titre);
  IF NEW.titre = '' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Le titre du ticket est obligatoire';
  END IF;
  IF NEW.titre LIKE '%rançongiciel%' OR NEW.titre LIKE '%ransomware%' THEN
    SET NEW.priorite = 'critique';
  END IF;
END //
DELIMITER ;

INSERT INTO ticket (titre, id_demandeur) VALUES ('   Rançongiciel sur le poste de la compta  ', 2);
SELECT id_ticket, titre, priorite, statut FROM ticket WHERE id_ticket = 15;

La priorité n'a pas été fournie : la valeur par défaut 'normale' est déjà dans NEW quand le trigger s'exécute, et il la remplace. Le statut, lui, garde sa valeur par défaut. L'interclassement par défaut ignore la casse et les accents dans les comparaisons : LIKE '%rançongiciel%' reconnaît aussi « Rançongiciel ».

Un deuxième exemple, que tu as peut-être croisé dans le chapitre aperçu du cours SQL : la date de clôture gérée par la base, à partir du statut.

DELIMITER //
CREATE TRIGGER trg_ticket_cloture
BEFORE UPDATE ON ticket
FOR EACH ROW
BEGIN
  IF NEW.statut = 'resolu' AND OLD.statut <> 'resolu' THEN
    SET NEW.date_cloture = NOW();          -- le ticket vient d'être résolu
  ELSEIF NEW.statut <> 'resolu' THEN
    SET NEW.date_cloture = NULL;           -- un ticket non résolu n'a pas de date de clôture
  END IF;
END //
DELIMITER ;

UPDATE ticket SET statut = 'en_cours' WHERE id_ticket = 9;
SELECT statut, date_cloture FROM ticket WHERE id_ticket = 9;   -- en_cours, NULL

Propager : modifier une autre table

Un trigger AFTER peut écrire dans d'autres tables. Quand un technicien enregistre la première intervention sur un ticket ouvert, le ticket passe automatiquement « en cours » :

DELIMITER //
CREATE TRIGGER trg_intervention_prise_en_charge
AFTER INSERT ON intervention
FOR EACH ROW
BEGIN
  UPDATE ticket
  SET statut = 'en_cours'
  WHERE id_ticket = NEW.id_ticket AND statut = 'ouvert';
END //
DELIMITER ;

INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min, compte_rendu)
VALUES (12, 11, '2026-10-22 10:00:00', 15, 'Clavier changé');

SELECT statut FROM ticket WHERE id_ticket = 12;   -- en_cours (il était ouvert)

L'UPDATE de ticket déclenche à son tour trg_ticket_cloture (un BEFORE UPDATE sur ticket) : les triggers s'enchaînent. C'est puissant et dangereux à la fois, car l'effet d'un simple INSERT devient difficile à prévoir. Documente chaque trigger, et garde-les courts.

Pourquoi AFTER et pas BEFORE ? Un trigger AFTER ne s'exécute que si la ligne a été écrite et a passé toutes les contraintes : la conséquence ne concerne qu'une intervention réellement enregistrée. Avec InnoDB, un BEFORE qui modifierait le ticket serait de toute façon annulé avec l'instruction en cas d'erreur, mais la convention est claire et vaut d'être suivie : BEFORE pour la ligne elle-même, AFTER pour les conséquences ailleurs.

Les limites des triggers

Un trigger ne peut pas modifier sa propre table. Un trigger sur ticket qui exécute UPDATE ticket (ou INSERT, ou DELETE sur ticket) échoue à l'exécution :

ERROR 1442 (HY000): Can't update table 'ticket' in stored function/trigger because it is
already used by statement which invoked this stored function/trigger.

Pour modifier la ligne en cours, utilise SET NEW.colonne = … dans un trigger BEFORE. Pour modifier d'autres lignes de la même table, il faut une procédure appelée par l'application.

Les actions des clés étrangères ne déclenchent pas les triggers. Supprimer le ticket 13 supprime en cascade ses interventions (ON DELETE CASCADE), mais un trigger AFTER DELETE ON intervention ne s'exécute pas pour elles. Un audit des suppressions d'interventions doit donc aussi surveiller les suppressions de tickets.

Ce qui déclenche, et ce qui ne déclenche pas.

Les autres interdits sont ceux des fonctions (chapitre 4) : pas de jeu de résultats (un SELECT sans INTO), pas de COMMIT ni de ROLLBACK, pas de SQL dynamique, pas de RETURN (pour sortir, LEAVE sur un bloc étiqueté). Un trigger peut appeler une procédure avec CALL, à condition qu'elle respecte les mêmes interdits.

Le coût. Un trigger s'exécute pour chaque ligne : un UPDATE de 100 000 lignes exécute 100 000 fois son code. Un trigger lent ralentit toutes les écritures de la table, pour tout le monde.

Lister, afficher et supprimer

SHOW TRIGGERS FROM parc;                     -- tous les triggers de la base
SHOW TRIGGERS LIKE 'intervention';           -- ceux d'une table (LIKE porte sur le nom de la table)
SHOW CREATE TRIGGER trg_ticket_cloture;      -- le code complet

SELECT trigger_name, event_manipulation AS evenement, event_object_table AS table_cible,
       action_timing AS moment, action_order AS ordre
FROM information_schema.triggers
WHERE trigger_schema = 'parc'
ORDER BY table_cible, evenement, moment, ordre;

Pour supprimer : DROP TRIGGER IF EXISTS trg_ticket_cloture;. Supprimer une table supprime ses triggers. Comme pour les procédures, il n'y a pas de ALTER TRIGGER : on supprime et on recrée. CREATE TRIGGER IF NOT EXISTS existe depuis MySQL 8.0.29 ; MariaDB ajoute CREATE OR REPLACE TRIGGER. MySQL n'a pas de commande pour désactiver un trigger : il faut le supprimer.

Exercices

ALTER TABLE intervention
  ADD CONSTRAINT ck_intervention_duree_max CHECK (duree_min <= 480);

INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min)
VALUES (14, 11, '2026-10-28 09:00:00', 600);
-- ERROR 3819 (HY000): Check constraint 'ck_intervention_duree_max' is violated.

DELIMITER //
CREATE TRIGGER trg_poste_inventaire_ajout
BEFORE INSERT ON poste
FOR EACH ROW
BEGIN
  SET NEW.num_inventaire = UPPER(TRIM(NEW.num_inventaire));
END //

CREATE TRIGGER trg_poste_inventaire_modif
BEFORE UPDATE ON poste
FOR EACH ROW
BEGIN
  SET NEW.num_inventaire = UPPER(TRIM(NEW.num_inventaire));
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER trg_employe_suppression
BEFORE DELETE ON employe
FOR EACH ROW
BEGIN
  IF EXISTS (SELECT 1 FROM ticket WHERE id_demandeur = OLD.id_employe AND statut <> 'resolu') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Employé avec des tickets en attente';
  END IF;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER trg_intervention_compteur
AFTER INSERT ON intervention
FOR EACH ROW
BEGIN
  SET NEW.duree_min = NEW.duree_min;
  UPDATE ticket SET nb_interventions = nb_interventions + 1
  WHERE id_ticket = OLD.id_ticket;
END //
DELIMITER ;

ALTER TABLE ticket ADD COLUMN nb_interventions INT NOT NULL DEFAULT 0;
UPDATE ticket t
SET nb_interventions = (SELECT COUNT(*) FROM intervention i WHERE i.id_ticket = t.id_ticket);

CREATE TRIGGER trg_intervention_compteur
AFTER INSERT ON intervention
FOR EACH ROW
  UPDATE ticket SET nb_interventions = nb_interventions + 1 WHERE id_ticket = NEW.id_ticket;