Triggers MySQL : automatiser la cohérence des données en production

Table des matières :

1. Ce que fait réellement un trigger MySQL (et ce qu’il ne fera jamais)
2. Automatiser la cohérence des données en production : cas d’usage pragmatiques (e-commerce / PrestaShop)
3. Écrire un trigger robuste : patterns, anti-patterns et exemples concrets
4. Exploitation en production : déploiement, réplication, sauvegardes et stratégie de rollback
5. Mesurer et maintenir : performance, observabilité et check-list avant activation

Ce que fait réellement un trigger MySQL (et ce qu’il ne fera jamais)

Un trigger MySQL est un objet de schéma attaché à une table, exécuté automatiquement lors d’un événement INSERT / UPDATE / DELETE.

Dit autrement : si votre application, un import CSV, un batch ERP ou un module PrestaShop écrit dans la table, le trigger s’exécute sans passer par votre code. C’est précisément ce qui en fait un outil intéressant pour la cohérence en production… et précisément ce qui en fait un outil dangereux si on y met de la logique métier complexe.

Quelques points structurants à garder en tête (souvent mal compris) :

• MySQL ne propose que des triggers FOR EACH ROW : un UPDATE ... WHERE ... sur 50 000 lignes déclenche 50 000 exécutions (donc un coût multiplicatif sur imports et scripts de maintenance).
• Vous n’avez que deux timings : BEFORE et AFTER.
• BEFORE est le bon endroit pour normaliser et valider des valeurs via NEW.col (sans requêtes).
• AFTER est utile pour propager vers d’autres tables (audit, table d’agrégats), mais il faut gérer l’impact verrous/perf.
• Depuis MySQL 5.7.2, vous pouvez chaîner plusieurs triggers sur le même couple (timing, event) avec FOLLOWS / PRECEDES. Ne comptez pas sur un ordre “naturel” tant que vous ne l’imposez pas explicitement (et côté MariaDB, la syntaxe existe aussi, avec des nuances selon versions).

Il faut aussi être clair sur les limites structurelles :

1) Un trigger s’exécute dans la transaction de la requête appelante : il peut faire échouer l’instruction, mais il ne peut pas “sauver” un état intermédiaire en jouant avec des COMMIT/ROLLBACK internes. En pratique, cela signifie que toute latence ajoutée par le trigger (requête interne, verrou, attente disque) se répercute directement sur l’opération de l’application.

2) MySQL interdit certains effets de bord qui surprennent en production. La restriction suivante est particulièrement importante : modifier une table qui est déjà utilisée (en lecture ou écriture) par l’instruction qui a déclenché le programme n’est pas autorisé dans certains cas.

Concrètement : si votre trigger AFTER UPDATE essaie de réécrire dans la même table, vous allez au-devant d’erreurs (ou de contournements via tables tampons). Et même quand “ça passe” en modifiant une autre table, vous pouvez créer des chaînes implicites (table A → trigger → table B → trigger → table C) qui deviennent pénibles à déboguer.

3) Un trigger n’est pas une alternative moderne à toutes les contraintes. Avant de “tout mettre en trigger”, posez-vous la question des outils natifs, souvent plus simples à maintenir et plus transparents :

Besoin	Meilleur outil (souvent)	Pourquoi	Quand le trigger reste pertinent
Empêcher NULL, garantir une clé unique	contraintes NOT NULL, UNIQUE	optimisé, explicite, standard	rare (logique conditionnelle)
Valeur calculée simple	colonne générée (GENERATED)	reproductible, indexable selon cas	calcul dépendant d’autres tables
Validation logique simple	CHECK (MySQL 8.0.16+)	lisible, côté schéma	message d’erreur/contrôle avancé
Traçabilité des changements	mécanisme d’audit applicatif ou logs	plus riche (utilisateur, contexte)	si plusieurs writers SQL contournent l’app
Mise à jour automatique d’un cache/agrégat	job + recalcul incrémental	isolable et observable	besoin temps réel strict au write

Le trigger reste un excellent outil quand vous avez plusieurs points d’entrée (app, imports, connecteurs) et que vous voulez imposer une invariant “au niveau base”.

Enfin, si vous travaillez aussi avec MariaDB, gardez un lien de référence (utile en migration ou en environnement hybride) : Documentation MariaDB — Triggers

Automatiser la cohérence des données en production : cas d’usage pragmatiques (e-commerce / PrestaShop)

Le cas “propre” pour des triggers MySQL, c’est la normalisation et validation au point d’écriture, indépendamment de l’application. Exemple trivial mais efficace : garantir qu’un email est stocké en minuscules et sans espaces, ou qu’une colonne date_upd est mise à jour même si un script externe court-circuite l’ObjectModel PrestaShop.

Dans la vraie vie d’une boutique, vous avez rarement un seul writer : back-office, webservice, import PIM/ERP, script de migration, module tiers, correctif “one-shot”… Le trigger devient alors un “garde-fou” côté SQL, qui évite les divergences silencieuses. Mini-scénario fréquent :

• Un import catalogue met à jour des clients B2B via INSERT ... ON DUPLICATE KEY UPDATE.
• Le module d’import n’appelle pas les classes PrestaShop (donc pas de normalisation email, pas de hook, parfois pas de mise à jour de date_upd).
• Résultat sans trigger : doublons d’email (Jean.Dupont@... vs jean.dupont@...), recherche client instable, bugs de login intermittents.
• Résultat avec trigger BEFORE INSERT/UPDATE : stockage normalisé, cohérence conservée quel que soit l’outil d’écriture.

Le deuxième cas d’usage, fréquent sur des boutiques à gros volume, concerne la dénormalisation contrôlée et les agrégats. PrestaShop, comme beaucoup de moteurs, finit par introduire des tables “cache” (ou des colonnes calculées) pour éviter des COUNT(*) ou des jointures coûteuses à chaque page. Un trigger peut maintenir une table d’agrégation (compteurs par statut de commande, derniers achats par client, etc.) au fil de l’eau, plutôt que de recalculer en batch.

Deux recommandations pour éviter l’effet “bombe à retardement” :

• Définissez le contrat fonctionnel de l’agrégat (qu’est-ce qui est garanti, et quand). Par exemple : “compteur à jour en temps réel pour le back-office” n’implique pas forcément “compteur strictement exact à la milliseconde en cas de rollback”.
• Assurez-vous que les requêtes internes sont indexées. Un UPDATE sur table d’agrégat sans index sur la clé (ex. id_customer) peut créer une dégradation progressive invisible jusqu’au pic de charge (soldes, Black Friday, campagne emailing).

Le troisième cas d’usage est la traçabilité (audit trail). Sur un e-commerce, des modifications “silencieuses” sur ps_orders, ps_customer, ps_cart ou des tables métiers custom arrivent vite (hotfix, prestataire, script de migration, outil d’import). Un trigger AFTER UPDATE peut écrire dans une table d’audit minimaliste (qui, quand, quoi), ce qui donne un filet de sécurité en incident.

Pour que l’audit reste exploitable (et raisonnable en volume), visez un format minimal :

• clé de l’objet (id_customer, id_order…)
• type d’événement (INSERT/UPDATE/DELETE)
• timestamp
• utilisateur SQL (USER()), et éventuellement application (CURRENT_USER() reflète souvent le DEFINER / compte de sécurité)
• quelques champs critiques (ex. current_state, active, deleted) plutôt que toute la ligne

Et côté conformité (France/UE), attention à une conséquence très concrète : si vous logguez des données personnelles (email, nom, adresse) dans une table d’audit, vous créez une nouvelle surface de données à gérer (rétention, purge, accès). En pratique, beaucoup d’équipes auditent des identifiants et des champs métier non sensibles, puis recoupent si nécessaire.

Attention : ça ne remplace pas une vraie stratégie de sécurité applicative (WAF, CI/CD, SBOM, etc.), mais ça peut réduire le temps de diagnostic ; si vous avez déjà une démarche sécurité structurée, l’article PrestaShop : sécuriser APIs et modules avec SBOM, WAF et CI/CD est un bon complément .

Dans l’écosystème PrestaShop, il faut aussi rappeler une limite non négociable : le cœur (et beaucoup de modules) s’appuie sur des invariants gérés côté PHP (ObjectModel, hooks, services Symfony en PrestaShop 9). Un trigger qui “corrige” la base peut masquer un bug applicatif, et pire, introduire des divergences avec ce que le code croit avoir écrit (ex. “je sauvegarde X” mais le trigger transforme X). Si votre objectif est d’étendre le comportement sans toucher au cœur, commencez par regarder si un hook natif couvre le besoin (et évitez d’écrire dans la DB en direct quand une API métier existe) : Comprendre les hooks dynamiques PrestaShop

Écrire un trigger robuste : patterns, anti-patterns et exemples concrets

Pré-requis de lecture : exemples validés sur MySQL 8.0.x et MariaDB 10.11.x, avec des boutiques PrestaShop 8.1/8.2 et PrestaShop 9 (PHP 8.2). En production, la première règle est simple : un trigger doit rester court, déterministe et index-friendly. Évitez tout ce qui ressemble à un mini-moteur métier : boucles, requêtes lourdes, parcours de tables volumineuses. Parce qu’un trigger s’exécute par ligne, le moindre SELECT non indexé devient une bombe pendant un import.

Pattern utile : normaliser/valider dans un BEFORE (zéro requête, uniquement des opérations sur NEW). Exemple : forcer une valeur normalisée et refuser une entrée invalide via SIGNAL.

DELIMITER $$

CREATE TRIGGER trg_customer_email_normalize
BEFORE INSERT ON ps_customer
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(TRIM(NEW.email));

  IF NEW.email IS NULL OR NEW.email = '' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'email obligatoire';
  END IF;
END$$

DELIMITER ;

Deux variantes réalistes (sans complexifier) que vous pouvez appliquer au même principe :

• Sur BEFORE UPDATE, refuser une mise à jour incohérente (ex. passage d’un flag active à 1 alors que deleted=1).
• Sur BEFORE INSERT, forcer une valeur par défaut fonctionnelle (pas seulement DEFAULT SQL) quand la donnée dépend du contexte table (ex. tronquer une chaîne trop longue pour éviter des erreurs sur imports hétérogènes — à faire avec prudence et idéalement en remontant l’erreur plutôt qu’en “corrigeant en silence”).

Anti-pattern classique : vouloir “réparer” après coup en AFTER en réécrivant dans la même table, ou en lançant une cascade de mises à jour non maîtrisées. MySQL vous bloquera parfois (restriction sur la modification d’une table déjà utilisée par la requête), et même quand ça passe, vous augmentez le risque de deadlocks (InnoDB) et de contention.

Si vous devez propager un état vers une autre table, faites-le de manière ciblée, indexée, et avec un contrat clair. Exemple réaliste en e-commerce : garder ps_orders.current_state cohérent lorsqu’une ligne est ajoutée dans ps_order_history (table différente, donc autorisé).

DELIMITER $$

CREATE TRIGGER trg_order_history_sync_current_state
AFTER INSERT ON ps_order_history
FOR EACH ROW
BEGIN
  UPDATE ps_orders
  SET current_state = NEW.id_order_state,
      date_upd = NOW()
  WHERE id_order = NEW.id_order;
END$$

DELIMITER ;

Pour que ce type de trigger reste “safe” en charge, vérifiez deux détails concrets :

• ps_orders.id_order doit évidemment être indexée (c’est le cas en standard), sinon vous transformez chaque insertion d’historique en recherche coûteuse.
• Votre application doit être prête à gérer un échec “global” : si l’UPDATE ps_orders se bloque (verrou, timeout), l’INSERT dans l’historique échoue aussi, puisqu’on est dans la même transaction.

Un pattern très utile mais à documenter explicitement : désactivation contrôlée sur certaines opérations de masse via variable de session. C’est pertinent quand vous avez un import ERP sous contrôle (runbook, fenêtre de maintenance, supervision) et que vous voulez éviter la pénalité “par ligne”.

Exemple minimaliste (à adapter) :

-- Dans le job d'import, avant les écritures :
SET @DISABLE_TRIGGERS = 1;

-- Dans le trigger :
IF COALESCE(@DISABLE_TRIGGERS, 0) = 1 THEN
  LEAVE BEGIN;
END IF;

Ce compromis a un coût organisationnel : si quelqu’un oublie de réactiver, la cohérence disparaît. Si vous choisissez cette voie, imposez des garde-fous (script qui reset la variable, contrôle post-import, check automatisé).

Enfin, n’oubliez pas un point de maintenance très concret : les triggers sont du code “invisible” côté application. Pour aider le support et réduire le temps d’investigation, beaucoup d’équipes ajoutent un commentaire standard dans le corps SQL (qui décrit invariant + owner + lien ticket) et stockent aussi le SHOW CREATE TRIGGER dans Git.

Exploitation en production : déploiement, réplication, sauvegardes et stratégie de rollback

Ne déployez jamais des triggers directement sur la prod “à la main”. Travaillez avec un environnement de staging fidèle (mêmes versions MySQL/MariaDB, mêmes options InnoDB, mêmes volumes) ; sinon vous testez dans le vide. Si vous n’avez pas encore un staging reproductible, construisez-le proprement : Créer un environnement de staging Docker fidèle à la production Le point critique : un trigger peut fonctionner en dev et exploser en prod uniquement à cause du volume, des verrous ou d’un plan d’exécution différent.

Côté “delivery”, versionnez vos triggers comme du code. En pratique : un dépôt Git avec des fichiers SQL et une migration idempotente (DROP TRIGGER IF EXISTS ...; CREATE TRIGGER ...;). Gardez en tête que CREATE TRIGGER / DROP TRIGGER sont des opérations de type DDL : elles s’exécutent avec autocommit et ne se “rollbackent” pas comme une mise à jour de données. D’où l’importance d’un script de retour arrière prêt à l’emploi.

Si vous shippez via module PrestaShop, placez la création dans install()/upgrade avec gestion d’erreur stricte, mais gardez en tête que l’installateur est rarement un outil de migration robuste (timeouts, droits SQL limités en mutualisé, etc.). Pour l’inventaire et l’audit, appuyez-vous sur INFORMATION_SCHEMA.TRIGGERS et SHOW TRIGGERS. Exemple de requête utile en audit (notamment pour vérifier le DEFINER et éviter root) :

SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE,
       ACTION_TIMING, DEFINER
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION, TRIGGER_NAME;

Sur la sauvegarde/restauration, vérifiez systématiquement que votre chaîne inclut les triggers. Avec mysqldump, les triggers sont généralement inclus par défaut, mais il est fréquent de les exclure sans le vouloir via des options ou des pratiques “no-data”. En production, le seul test qui compte est opérationnel : restaurer un dump complet sur staging et vérifier la présence des triggers (et leur exécution sur un jeu de tests).

Pour aller plus loin sur l’état de votre serveur SQL (collation, InnoDB, compat MariaDB), gardez sous la main une checklist de prérequis : Checklist prérequis système et migration MySQL → MariaDB

Enfin, ne sous-estimez pas l’impact réplication. Un trigger est du code qui modifie des données ; selon votre binlog_format (ROW/STATEMENT/MIXED) et votre topologie, les effets peuvent être journalisés différemment. Les docs MySQL sur le binaire log et la réplication sont les références avant tout déploiement sur un cluster.

Point opérationnel (très concret en HA) :

• en STATEMENT, on réplique la requête d’origine ; les triggers s’exécutent “logiquement” sur chaque nœud qui rejoue la requête.
• en ROW, on réplique l’état final des lignes ; on réplique donc surtout le résultat des triggers (ce qui peut masquer une divergence de définition si tous les nœuds n’ont pas le même schéma).
• en MIXED, vous pouvez changer de comportement selon la requête, ce qui complique le diagnostic.

Conclusion pratique : imposez une configuration stable (format de binlog, isolation, sql_mode) et testez un failover. Sinon, vous découvrirez en incident que le trigger n’est pas “un simple bout de SQL”, mais un composant critique de votre pipeline d’écriture.

Mesurer et maintenir : performance, observabilité et check-list avant activation

La performance d’un trigger MySQL ne se devine pas, elle se mesure. Commencez par instrumenter : performance_schema (statements, waits, lock time) et le slow query log. Sur une boutique PrestaShop, c’est d’autant plus important que des triggers ajoutent du coût à des opérations déjà lourdes (imports catalogue, recalculs, mises à jour de stock). Pour un workflow concret d’analyse et d’indexation, référez-vous à : Analyser le slow query log et optimiser les index

Une approche simple (et très efficace) en phase de validation :

1) Reproduire une opération représentative (ex. import de 10 000 clients / 50 000 déclinaisons). 2) Mesurer la durée sans trigger puis avec trigger, à dataset identique. 3) Regarder les verrous et temps d’attente (InnoDB) si le delta est élevé. 4) Si un trigger fait des UPDATE sur une autre table, vérifier que ces UPDATE passent bien par un index.

Sur l’observabilité “humaine”, documentez l’observable : quels triggers existent, sur quelles tables, et pour quel invariant. C’est basique, mais en pratique, beaucoup d’équipes “oublient” les triggers après quelques mois, jusqu’au jour où une migration casse tout. Ajoutez au minimum :

1) la liste des triggers (export SHOW CREATE TRIGGER) 2) un test SQL reproductible (jeu d’entrées + résultat attendu) 3) un runbook de rollback (DROP/CREATE) 4) l’owner technique et la raison métier (“pourquoi ce trigger existe”) 5) un point de contrôle après déploiement (ex. opération test + vérification table d’audit)

Enfin, gardez une check-list d’activation en production, volontairement stricte :

• Vérifier les droits SQL (CREATE TRIGGER, TRIGGER) et le DEFINER (éviter root, éviter un compte humain).
• Valider l’impact sur les opérations de masse (import, UPDATE multi-lignes) sur staging avec volumes représentatifs.
• Confirmer les index sur toutes les colonnes touchées par les requêtes internes des triggers.
• Tester la compatibilité avec réplication/HA (binlog, failover) et restauration (dump/restore).
• Définir un plan de sortie (rollback immédiat) et une fenêtre de déploiement.

Si vous cochez ces points, les triggers MySQL deviennent un outil utile pour automatiser la cohérence des données en production, surtout quand votre stack a plusieurs writers (modules, scripts, intégrations ERP/PIM). Si vous ne les cochez pas, vous venez juste d’introduire du code “caché” dans la base, difficile à debugger, difficile à faire évoluer, et qui finira par vous coûter plus qu’il ne vous rapporte.