Déclencheurs MySQL : du SQL exécuté à chaque écriture d'une table

À chaque INSERT, UPDATE ou DELETE sur une table, MySQL sait lancer automatiquement un bloc de SQL rattaché à cette table : voilà ce que sont les triggers de MySQL, ou déclencheurs. Ils exécutent leur code ligne par ligne, avant ou après l'écriture de la donnée, pour tenir un historique, corriger une valeur ou refuser une mise à jour incohérente dans la base.

Ce qu'un déclencheur fait dans une base de données MySQL

Un déclencheur MySQL est un objet de la base de données, nommé, qui associe un bloc d'instructions SQL à une table et à un type d'écriture. Le serveur l'exécute de lui-même : l'application qui envoie la requête n'a rien à appeler, et elle ignore souvent que ce code existe. Cette propriété fait tout l'intérêt de l'outil, et aussi sa principale difficulté, puisqu'une règle placée dans un trigger s'applique quel que soit le programme qui écrit dans la table : un script PHP, une console d'administration, un import CSV ou une tâche planifiée.

La définition d'un trigger repose sur trois choix. Le moment d'abord, BEFORE ou AFTER, qui place le code avant ou après l'écriture de chaque ligne. L'événement ensuite, INSERT, UPDATE ou DELETE. La table surveillée enfin, qui doit être une table permanente : le manuel de référence de MySQL 8.4 exclut les tables temporaires et les vues. À ces trois choix s'ajoute un nom, unique dans le schéma, qui sert à afficher, ordonner ou supprimer le déclencheur.

Les usages les plus fréquents tiennent en quelques familles : tenir un journal des modifications, horodater une ligne au moment de sa mise à jour, recopier une information dans une seconde table pour garder deux tables cohérentes, refuser une donnée hors bornes, ou remplir une colonne calculée à partir des autres. Les autres questions sur les bases relationnelles, de la conversion de schémas aux index, se trouvent dans la rubrique MySQL et MariaDB en pratique.

BEFORE ou AFTER, INSERT, UPDATE ou DELETE : les six combinaisons

Un trigger MySQL combine un moment et un événement, ce qui donne six types de déclencheurs par table. Depuis MySQL 5.7.2, une même table peut porter plusieurs déclencheurs pour la même combinaison, par exemple deux triggers BEFORE INSERT ; leur ordre d'exécution se fixe alors avec les clauses FOLLOWS ou PRECEDES. Le choix entre BEFORE et AFTER dépend de ce que le code doit faire : modifier la ligne en cours ou la refuser impose BEFORE, agir sur une autre table une fois l'écriture acquise relève plutôt de AFTER.

L'événement ne désigne pas une instruction SQL précise mais un type d'écriture. Les triggers INSERT réagissent aussi à LOAD DATA et à REPLACE ; une instruction REPLACE qui écrase une ligne existante lance en plus les déclencheurs DELETE. Pour un INSERT ... ON DUPLICATE KEY UPDATE, le manuel décrit l'enchaînement suivant : BEFORE INSERT pour chaque ligne, puis soit AFTER INSERT, soit BEFORE UPDATE et AFTER UPDATE quand la clé existe déjà.

OLD et NEW : lire et corriger la ligne en cours

Dans le corps d'un déclencheur MySQL, les pseudo-lignes OLD et NEW donnent accès aux colonnes de la ligne traitée. OLD.colonne désigne la valeur avant une modification ou une suppression ; NEW.colonne désigne la valeur insérée, ou la valeur après une modification. Un trigger INSERT n'a pas de OLD, un trigger DELETE n'a pas de NEW, comme le résume le tableau précédent.

OLD reste en lecture seule dans tous les cas. NEW se modifie avec SET, mais seulement dans un trigger BEFORE, et à condition de disposer du privilège UPDATE sur la colonne concernée. Cette possibilité permet de corriger une donnée avant qu'elle ne soit écrite : mettre un code postal en majuscules, compléter une date manquante, arrondir un montant. Dans un trigger AFTER, la ligne est déjà écrite et NEW n'est plus modifiable.

Une particularité surprend souvent : dans un trigger BEFORE INSERT, la valeur NEW d'une colonne AUTO_INCREMENT vaut 0, et non l'identifiant que MySQL attribuera à la nouvelle ligne. L'identifiant réel n'est connu qu'en AFTER INSERT. Un historique qui doit noter l'identifiant d'une ligne inséree se place donc après l'écriture, pas avant.

Créer un trigger avec CREATE TRIGGER

La syntaxe de CREATE TRIGGER suit l'ordre des trois choix décrits plus haut : le nom, le moment, l'événement, la table, puis FOR EACH ROW et le corps. Depuis MySQL 8.0.29, la clause IF NOT EXISTS évite une erreur quand un déclencheur du même nom existe déjà. La forme générale s'écrit ainsi :

CREATE [DEFINER = compte] TRIGGER [IF NOT EXISTS] nom_du_trigger
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
  ON nom_de_la_table FOR EACH ROW
  [{FOLLOWS | PRECEDES} autre_trigger]
  corps_du_trigger;

DELIMITER, BEGIN et END : écrire un corps sur plusieurs instructions

Un corps de trigger qui contient plusieurs instructions s'entoure de BEGIN et END, et chaque instruction se termine par un point-virgule. Le client en ligne de commande mysql couperait alors la requête au premier point-virgule : la commande DELIMITER change provisoirement le séparateur, par exemple en //, le temps de créer le déclencheur. DELIMITER ne fait pas partie du SQL envoyé au serveur ; elle ne sert qu'au client. Dans phpMyAdmin, l'onglet SQL propose un champ Délimiteur qui joue le même rôle, et MySQL Workbench gère le séparateur de lui-même dans son éditeur.

Un exemple concret sur une table animal

L'exemple suivant crée une table animal avec un prénom, un nom de famille, une date de naissance et une date de modification, puis un trigger BEFORE UPDATE qui tient cette date à jour automatiquement à chaque modification de la ligne :

CREATE TABLE animal (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50),
  date_naissance DATE,
  date_modification DATETIME
);

DELIMITER //
CREATE TRIGGER animal_bu BEFORE UPDATE ON animal
FOR EACH ROW
BEGIN
  SET NEW.date_modification = NOW();
END//
DELIMITER ;

Une requête UPDATE animal SET last_name = 'Durand' WHERE id = 3 met désormais la colonne date_modification à jour sans que l'application ait à la renseigner. Le même résultat s'obtient pour les colonnes TIMESTAMP avec ON UPDATE CURRENT_TIMESTAMP, plus simple quand la règle se limite à l'horodatage ; le trigger garde l'avantage dès que la règle porte une condition. Pour remplir la date lors de l'insertion d'une nouvelle ligne, un second déclencheur BEFORE INSERT sur la même table fait l'affaire.

Garder un historique des modifications et des suppressions

L'historique est l'utilisation la plus courante d'un trigger MySQL : chaque changement d'une table se recopie dans une table de journal, avec l'ancienne valeur, la nouvelle et la date. Voici un déclencheur AFTER UPDATE qui note chaque changement de prix d'un produit, et un AFTER DELETE qui archive la ligne supprimée :

CREATE TABLE prix_historique (
  produit_id INT NOT NULL,
  ancien DECIMAL(10,2),
  nouveau DECIMAL(10,2),
  change_le DATETIME NOT NULL
);

DELIMITER //
CREATE TRIGGER produits_prix_au AFTER UPDATE ON produits
FOR EACH ROW
BEGIN
  IF NOT (OLD.prix <=> NEW.prix) THEN
    INSERT INTO prix_historique VALUES (OLD.id, OLD.prix, NEW.prix, NOW());
  END IF;
END//

CREATE TRIGGER produits_ad AFTER DELETE ON produits
FOR EACH ROW
BEGIN
  INSERT INTO produits_supprimes (id, nom, prix, supprime_le)
  VALUES (OLD.id, OLD.nom, OLD.prix, NOW());
END//
DELIMITER ;

L'opérateur <=> compare deux valeurs en tenant compte des NULL : un simple OLD.prix <> NEW.prix ne détecterait pas le passage d'un prix vide à un prix renseigné, car toute comparaison avec NULL rend NULL. La condition IF évite aussi d'écrire une ligne d'historique quand une requête UPDATE touche d'autres colonnes sans changer le prix.

Le choix de AFTER plutôt que BEFORE a une raison précise : l'historique ne doit noter que les écritures réussies. Le manuel de MySQL indique qu'un trigger AFTER ne s'exécute que si les déclencheurs BEFORE et l'écriture de la ligne ont abouti. Avec un moteur transactionnel, une erreur dans le trigger annule en outre l'instruction entière, écriture d'origine comprise, ce qui garde la table et son journal cohérents.

Refuser une donnée incohérente et gérer les erreurs

Un trigger BEFORE peut refuser une écriture en levant une erreur avec l'instruction SIGNAL : la ligne n'est alors pas écrite, et l'application reçoit le message comme une erreur SQL ordinaire. La valeur SQLSTATE '45000' sert par convention aux erreurs définies par l'utilisateur. L'exemple ci-dessous refuse l'insertion d'un stock négatif :

CREATE TRIGGER produits_stock_bi BEFORE INSERT ON produits
FOR EACH ROW
  IF NEW.stock < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Stock négatif refusé';
  END IF;

Ce corps ne contient qu'une instruction IF, ce qui dispense de BEGIN et END ; dès qu'une deuxième instruction s'ajoute, le bloc redevient nécessaire. La même logique protège une ligne contre la suppression : un BEFORE DELETE qui lève une erreur quand OLD.statut vaut 'verrouille' empêche toute requête DELETE de la retirer, y compris depuis un outil d'administration.

La gestion des erreurs dépend du moteur de stockage de la table. Sur une table InnoDB, une erreur levée dans un déclencheur BEFORE ou AFTER annule toute l'instruction : aucune ligne n'est écrite, même pour une requête qui en visait plusieurs. Sur une table MyISAM, non transactionnelle, les lignes déjà écrites avant l'erreur restent en place. Les écarts entre les moteurs se lisent en détail dans le comparatif InnoDB, MyISAM ou MEMORY ; pour des triggers fiables, InnoDB s'impose.

Le diagnostic d'une erreur levée par un trigger passe par SHOW ERRORS ou SHOW WARNINGS dans la même session, et par le journal d'erreurs du serveur quand l'hébergeur en donne l'accès.

Créer un déclencheur depuis phpMyAdmin ou MySQL Workbench

Un déclencheur MySQL se crée aussi sans écrire la requête complète, grâce aux outils graphiques. Dans phpMyAdmin, la vue d'une table propose un onglet Déclencheurs (Triggers dans l'interface anglaise) : le formulaire demande le nom, la table, le moment, l'événement, la définition et le compte DEFINER, puis génère l'instruction CREATE TRIGGER. Le même onglet liste les déclencheurs existants et permet de les modifier ou de les exporter.

Dans MySQL Workbench, l'éditeur de table (clic droit sur la table, puis Alter Table) contient un onglet Triggers qui affiche les six combinaisons sous forme de liste ; un clic sur le signe plus ajoute un trigger avec un squelette de code BEGIN ... END à compléter. L'outil produit ensuite un script de modification à relire avant de l'appliquer.

Ces interfaces ne changent rien à ce que fait le serveur : l'instruction envoyée reste un CREATE TRIGGER classique, soumise aux mêmes privilèges. Sur un hébergement mutualisé, un formulaire phpMyAdmin qui échoue renvoie donc le même message d'erreur que la console, et la cause se trouve en général dans les droits du compte, traités plus bas.

Ce qui ne déclenche rien, et ce qu'un trigger ne peut pas faire

Un trigger MySQL ne réagit qu'aux écritures faites par des requêtes SQL sur sa table, et le manuel en exclut plusieurs. Les actions en cascade d'une clé étrangère (FOREIGN KEY ... ON DELETE CASCADE ou ON UPDATE CASCADE) n'activent pas les triggers de la table enfant. TRUNCATE TABLE vide une table sans lancer ses déclencheurs DELETE, et DROP TABLE supprime la table avec ses déclencheurs. Un historique alimenté par trigger peut donc rater des suppressions faites de cette façon.

Le corps d'un déclencheur obéit aussi à des restrictions que le manuel détaille dans sa page sur les programmes stockés :

• il ne peut pas modifier la table qui l'a déclenché par une requête INSERT, UPDATE ou DELETE (erreur 1442) ; pour changer la ligne en cours, SET NEW dans un trigger BEFORE suffit ;
• il ne peut contenir aucune instruction qui valide ou annule une transaction, ni START TRANSACTION, COMMIT ou ROLLBACK, ni une instruction qui provoque une validation implicite comme ALTER TABLE ;
• il ne peut pas renvoyer de résultat au client : un SELECT doit écrire dans des variables avec INTO ;
• il n'accepte pas le SQL dynamique (PREPARE et EXECUTE).

Le coût en performance reste à mesurer sur chaque cas. Comme le corps s'exécute pour chaque ligne, une importation massive de plusieurs millions de lignes paie le trigger autant de fois ; un trigger qui fait une requête lourde ou une recherche sans index ralentit chaque écriture de la table.

Privilèges, DEFINER et hébergement mutualisé

Créer un trigger exige le privilège TRIGGER sur la table concernée. Lorsque la journalisation binaire est active, ce qui est fréquent sur les serveurs répliqués, MySQL peut réclamer en plus un privilège d'administration que les offres mutualisées n'accordent pas : l'erreur parle alors de SUPER ou de log_bin_trust_function_creators, et seul l'hébergeur peut lever le blocage. Avant de bâtir une application sur des déclencheurs, un test de création sur la base de production évite une mauvaise surprise.

Un déclencheur s'exécute avec les droits de son créateur, le compte DEFINER, et non avec ceux du compte qui écrit dans la table. Si ce compte disparaît, le trigger échoue et bloque toutes les écritures de sa table. Le cas se présente surtout après la migration d'une base vers un autre serveur ou un autre hébergeur, où le nom d'utilisateur change : le dump contient DEFINER=`ancien_compte`@`localhost`, et l'import échoue ou crée des déclencheurs inutilisables. La correction consiste à remplacer ce compte dans le fichier d'export avant l'import, ou à recréer les triggers avec le nouveau compte.

La même vigilance vaut pour les sauvegardes. mysqldump inclut les déclencheurs par défaut, mais certains outils d'export simplifiés ne les reprennent pas ; une restauration sans eux laisse une application qui tourne, sans historique ni contrôle, et rien ne le signale. Après une conversion de schéma, comme lors du passage de tables SQLite vers MySQL, les triggers se réécrivent à la main : la syntaxe de SQLite diffère.

Trigger, procédure stockée ou code PHP : où placer la règle

Un trigger MySQL convient aux règles qui doivent s'appliquer quel que soit le programme qui écrit dans la table : historique, horodatage, cohérence entre deux tables, refus d'une valeur impossible. Il convient mal à une logique métier complexe : il reste invisible depuis le code de l'application, complique le débogage et surprend le développeur qui découvre la base sans connaître son existence.

La procédure stockée (stored procedure) répond à un autre besoin : elle s'appelle explicitement par CALL, accepte des paramètres et peut renvoyer des résultats. Un déclencheur peut d'ailleurs appeler une procédure stockée, à condition qu'elle respecte les mêmes restrictions, sans résultat renvoyé au client ni validation de transaction. Ce découpage garde le corps du trigger court et place la logique réutilisable dans les stored procedures.

Le code PHP, enfin, reste le bon endroit pour tout ce qui dépend du contexte de l'utilisateur : droits, envoi d'un courriel, appel à un service externe. Pour un document stocké en colonne JSON, un trigger peut tenir à jour une colonne extraite, mais les colonnes générées décrites pour le stockage du JSON dans MySQL font le même travail de façon plus lisible.

Débuter avec les triggers : méthode de travail et documentation

Pour un débutant, la documentation officielle de MySQL reste la référence la plus fiable : le chapitre consacré aux programmes stockés décrit la syntaxe, les limitations et le comportement de chaque opération, version par version. Les tutoriels disponibles sur le web, souvent écrits pour MySQL 5.x, omettent les nouveautés de la série 8 comme IF NOT EXISTS ; vérifier la version du serveur avec SELECT VERSION() avant de copier un exemple évite une erreur de syntaxe.

Une méthode simple permet de maîtriser l'outil sans risque pour les données de production :

1. créer une copie de la table sur une base de développement et y exécuter le trigger ;
2. effectuer une opération de chaque type sur quelques enregistrements, puis comparer le résultat attendu et le résultat obtenu ;
3. tester les cas limites : valeur NULL, insertion multiple, requête qui échoue ;
4. consigner le rôle de chaque déclencheur dans la documentation interne du projet, pour l'équipe de développement qui reprendra la base après vous.

Pour relire une base dont on hérite, la vue INFORMATION_SCHEMA.TRIGGERS donne pour chaque déclencheur sa table (EVENT_OBJECT_TABLE), son événement (EVENT_MANIPULATION), son moment (ACTION_TIMING) et le code exécuté, dans la colonne ACTION_STATEMENT. Une requête sur ces colonnes, filtrée par schéma, dresse l'inventaire en une fois ; elle sert aussi à contrôler, après un import, que tous les triggers sont revenus.

Côté WordPress et des CMS courants, l'installation de base n'utilise aucun déclencheur : la logique métier vit dans le code PHP et dans les extensions. Un trigger ajouté par un développeur à une table de WordPress reste donc une exception à documenter, car une migration de l'hébergement peut le faire disparaître sans avertissement. Pour automatiser une tâche périodique plutôt qu'une réaction à une écriture, une fonction stockée lancée par cron ou par le planificateur d'événements de MySQL répond mieux au besoin ; le niveau d'expérience requis reste comparable, et chaque outil garde son usage spécifique.

Lister, ordonner, désactiver : les triggers MySQL au quotidien

Comment voir les triggers existants dans une base ?

SHOW TRIGGERS liste les déclencheurs de la base courante, avec leur table, leur moment et leur événement ; SHOW TRIGGERS LIKE 'produits' filtre par nom de table. SHOW CREATE TRIGGER nom affiche le code complet, et la table INFORMATION_SCHEMA.TRIGGERS permet des requêtes plus fines.

Comment modifier ou supprimer un trigger MySQL ?

MySQL ne propose pas d'ALTER TRIGGER : un déclencheur se modifie en le supprimant avec DROP TRIGGER IF EXISTS nom, puis en le recréant. Entre les deux instructions, les écritures de la table passent sans contrôle, d'où l'intérêt de faire l'opération hors des heures d'activité.

Peut-on désactiver un trigger temporairement ?

MySQL n'a pas de commande pour désactiver un trigger sans le supprimer. La méthode courante consiste à tester une variable de session au début du corps, par exemple IF @sans_trigger IS NULL THEN ... END IF, puis à positionner cette variable dans la session qui lance un import.

Sources : manuel de référence de MySQL 8.4, pages CREATE TRIGGER, utilisation des triggers, restrictions des programmes stockés et instruction SIGNAL ; documentation de phpMyAdmin et de MySQL Workbench.