Triggers (déclencheurs)

5.1 Qu'est-ce qu'un trigger ?

Un trigger est un bloc de code SQL qui s'execute automatiquement quand un evenement precis se produit sur une table : insertion, modification ou suppression d'une ligne.

Analogie : une alarme de maison. On la configure une fois. Ensuite, a chaque fois que quelqu'un ouvre la porte (INSERT), l'alarme se declenche automatiquement, sans qu'on ait a intervenir. Le trigger fonctionne de la meme maniere : on le cree une fois, et il se declenche tout seul a chaque evenement.

Le declenchement est transparent : le programme qui fait l'INSERT ne sait pas qu'un trigger existe. Il fait son INSERT normalement, et le trigger s'execute en arriere-plan.

5.2 Les 6 moments possibles

Chaque trigger est associe a :

• Un moment : BEFORE (avant) ou AFTER (apres)
• Un evenement : INSERT, UPDATE ou DELETE

Ce qui donne 6 combinaisons :

Quand choisir BEFORE ?

• Pour valider ou modifier les donnees avant qu'elles soient ecrites.
• Exemple : verifier qu'un prix n'est pas negatif avant insertion. Corriger automatiquement une valeur avant ecriture.

Quand choisir AFTER ?

• Pour reagir a une modification deja effectuee.
• Exemple : mettre a jour un total, ecrire dans une table d'historique.

5.3 NEW et OLD

Dans le corps d'un trigger, on accede aux valeurs de la ligne concernee avec les mots-cles NEW et OLD.

Tableau recapitulatif complet :

Exemples d'acces :

• NEW.prix_ht : le nouveau prix HT (en INSERT ou UPDATE).
• OLD.prix_ht : l'ancien prix HT (en UPDATE ou DELETE).
• En BEFORE INSERT ou BEFORE UPDATE, on peut modifier les valeurs de NEW : SET NEW.prix_ht = 0;
• On ne peut jamais modifier OLD. OLD est en lecture seule.

5.4 CREATE TRIGGER : syntaxe complete

DELIMITER //

CREATE TRIGGER nom_trigger
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON nom_table
FOR EACH ROW
BEGIN
    -- corps du trigger
END //

DELIMITER ;

• nom_trigger : nom unique dans la base.
• FOR EACH ROW : obligatoire en MySQL. Le trigger s'execute pour chaque ligne affectee.
• Le corps est entre BEGIN et END (sauf si une seule instruction, mais il est recommande de toujours utiliser BEGIN/END).

5.5 Cas d'utilisation 1 : historique / audit

Besoin : a chaque modification du prix d'un produit, enregistrer l'ancien et le nouveau prix dans la table historique_produit.

Table d'historique : deja creee dans le schema de reference (voir debut du document).

Trigger :

DELIMITER //

CREATE TRIGGER trg_audit_prix_produit
AFTER UPDATE
ON produit
FOR EACH ROW
BEGIN
    IF OLD.prix_ht != NEW.prix_ht THEN
        INSERT INTO historique_produit
            (id_produit, ancien_prix, nouveau_prix, utilisateur)
        VALUES
            (NEW.id_produit, OLD.prix_ht, NEW.prix_ht, CURRENT_USER());
    END IF;
END //

DELIMITER ;

Explication :

• AFTER UPDATE : le trigger se declenche apres chaque UPDATE sur la table produit.
• IF OLD.prix_ht != NEW.prix_ht : on ne logue que si le prix a reellement change (un UPDATE peut modifier d'autres colonnes sans toucher au prix).
• OLD.prix_ht : l'ancien prix (avant modification).
• NEW.prix_ht : le nouveau prix (apres modification).
• CURRENT_USER() : l'utilisateur MySQL qui a fait la modification.

Test :

-- Inserer un produit
INSERT INTO produit (designation, prix_ht, stock) VALUES ('Casque audio', 50.00, 20);

-- Le modifier
UPDATE produit SET prix_ht = 55.00 WHERE designation = 'Casque audio';

-- Verifier l'historique
SELECT * FROM historique_produit;
-- On voit : ancien_prix = 50.00, nouveau_prix = 55.00

-- Modifier le stock (pas le prix)
UPDATE produit SET stock = 25 WHERE designation = 'Casque audio';

-- Verifier : pas de nouvelle ligne dans l'historique (le prix n'a pas change)
SELECT * FROM historique_produit;

5.6 Cas d'utilisation 2 : validation avec SIGNAL SQLSTATE

Besoin : empecher l'insertion d'un produit avec un prix negatif ou un stock negatif.

DELIMITER //

CREATE TRIGGER trg_valider_produit
BEFORE INSERT
ON produit
FOR EACH ROW
BEGIN
    IF NEW.prix_ht < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Le prix HT ne peut pas etre negatif';
    END IF;

    IF NEW.stock < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Le stock ne peut pas etre negatif';
    END IF;
END //

DELIMITER ;

Explication :

• BEFORE INSERT : le trigger s'execute avant l'insertion. Si on declenche une erreur, l'insertion est annulee.
• SIGNAL SQLSTATE '45000' : declenche une erreur utilisateur. Le code '45000' est le code standard pour les erreurs definies par l'utilisateur.
• MESSAGE_TEXT : le message d'erreur retourne au programme appelant.

Test :

-- Ceci doit reussir
INSERT INTO produit (designation, prix_ht, stock) VALUES ('Clef USB', 10.00, 50);

-- Ceci doit echouer
INSERT INTO produit (designation, prix_ht, stock) VALUES ('Clef USB', -5.00, 50);
-- Erreur : Le prix HT ne peut pas etre negatif

-- Ceci doit echouer
INSERT INTO produit (designation, prix_ht, stock) VALUES ('Clef USB', 10.00, -3);
-- Erreur : Le stock ne peut pas etre negatif

Important : il faut aussi creer un trigger BEFORE UPDATE pour la meme validation, sinon un UPDATE pourrait mettre un prix negatif.

DELIMITER //

CREATE TRIGGER trg_valider_produit_update
BEFORE UPDATE
ON produit
FOR EACH ROW
BEGIN
    IF NEW.prix_ht < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Le prix HT ne peut pas etre negatif';
    END IF;

    IF NEW.stock < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Le stock ne peut pas etre negatif';
    END IF;
END //

DELIMITER ;

5.7 Cas d'utilisation 3 : mise a jour automatique du stock

Besoin : quand on ajoute une ligne de commande (INSERT dans ligne_commande), diminuer automatiquement le stock du produit concerne.

DELIMITER //

CREATE TRIGGER trg_diminuer_stock
AFTER INSERT
ON ligne_commande
FOR EACH ROW
BEGIN
    UPDATE produit
    SET stock = stock - NEW.quantite
    WHERE id_produit = NEW.id_produit;
END //

DELIMITER ;

Explication :

• AFTER INSERT ON ligne_commande : se declenche apres chaque insertion dans ligne_commande.
• NEW.quantite : la quantite commandee (valeur de la ligne inseree).
• NEW.id_produit : le produit concerne.
• Le stock du produit est diminue de la quantite commandee.

Test :

-- Verifier le stock initial
SELECT id_produit, designation, stock FROM produit WHERE id_produit = 1;
-- Supposons stock = 100

-- Inserer une ligne de commande
INSERT INTO ligne_commande (id_commande, id_produit, quantite, prix_unitaire)
VALUES (1, 1, 3, 25.00);

-- Verifier le stock
SELECT id_produit, designation, stock FROM produit WHERE id_produit = 1;
-- stock = 97

5.8 Cas d'utilisation 4 : mise a jour du total de la commande

Besoin : quand on ajoute une ligne de commande, mettre a jour automatiquement le montant_total dans la table commande.

DELIMITER //

CREATE TRIGGER trg_maj_total_commande
AFTER INSERT
ON ligne_commande
FOR EACH ROW
BEGIN
    UPDATE commande
    SET montant_total = montant_total + (NEW.quantite * NEW.prix_unitaire)
    WHERE id_commande = NEW.id_commande;
END //

DELIMITER ;

Test :

-- Creer une commande
INSERT INTO commande (id_client) VALUES (1);
-- montant_total = 0 par defaut

SET @id_cmd = LAST_INSERT_ID();

-- Ajouter des lignes
INSERT INTO ligne_commande (id_commande, id_produit, quantite, prix_unitaire)
VALUES (@id_cmd, 1, 2, 25.00);

INSERT INTO ligne_commande (id_commande, id_produit, quantite, prix_unitaire)
VALUES (@id_cmd, 2, 1, 15.00);

-- Verifier le total
SELECT id_commande, montant_total FROM commande WHERE id_commande = @id_cmd;
-- montant_total = 65.00 (2*25 + 1*15)

5.9 Les pieges des triggers

Piege 1 : boucle infinie entre triggers

Si un trigger sur la table A modifie la table B, et qu'un trigger sur la table B modifie la table A, on obtient une boucle infinie. MySQL detecte cette situation et leve une erreur (profondeur maximale atteinte).

Exemple dangereux :

Trigger sur table A → UPDATE table B
Trigger sur table B → UPDATE table A → re-declenche le trigger sur A → boucle

Solution : concevoir les triggers pour eviter les modifications circulaires. En cas de doute, dessiner un schema des dependances entre triggers.

Piege 2 : ordre d'execution avec plusieurs triggers

Depuis MySQL 5.7, on peut avoir plusieurs triggers pour le meme evenement sur la meme table. L'ordre est controle par FOLLOWS et PRECEDES :

CREATE TRIGGER trg_second
AFTER INSERT ON produit
FOR EACH ROW
FOLLOWS trg_premier
BEGIN
    -- s'execute apres trg_premier
END;

Si on ne precise rien, l'ordre depend de l'ordre de creation. En examen, eviter d'avoir plusieurs triggers pour le meme evenement sauf si explicitement demande.

Piege 3 : un trigger ne peut pas modifier sa propre table avec INSERT/UPDATE/DELETE

En MySQL, un trigger sur la table X ne peut pas faire INSERT INTO X, UPDATE X ou DELETE FROM X. Cela provoque une erreur. Le seul moyen de modifier la ligne en cours est d'utiliser SET NEW.colonne = valeur dans un trigger BEFORE.

Piege 4 : utiliser NEW dans DELETE ou OLD dans INSERT

• NEW n'existe pas dans un trigger DELETE (il n'y a pas de nouvelle ligne).
• OLD n'existe pas dans un trigger INSERT (il n'y avait pas d'ancienne ligne).

Utiliser l'un ou l'autre dans le mauvais contexte provoque une erreur.

5.10 DROP TRIGGER

DROP TRIGGER IF EXISTS trg_audit_prix_produit;

5.11 Voir les triggers existants

SHOW TRIGGERS;
SHOW TRIGGERS FROM nom_base;
SHOW CREATE TRIGGER nom_trigger;