Les triggers

Un trigger (ou déclencheur) est une sorte de procédure stockée, une série d'instruction enregistrée, qui est relié à une table de la base de données. Les instructions seront exécuté automatiquement lors d'une insertion, d'une mise à jour ou d'une suppression dans la table à laquelle le trigger est attaché et ça pour chacune des lignes affectées par la requête. On doit aussi spécifier à quel moment il sera déclenché, avant l'instruction SQL ou après. On peut donc définir jusqu'à 6 triggers par tables : avant ou après une insertion, avant ou après une mise à jour, avant ou après une suppression.

On peut depuis un trigger modifier et insérer des données dans n'importe quel table de la base de données à l'exception des tables utilisées dans la requête qui l'a déclenché. Si on veut travailler sur la table à laquelle le trigger est attaché, on n'a accès qu'à la ligne qu'on est en train de traiter.

Pourquoi utiliser des triggers ?

S'assurer de la validité des données avant leur insertion ou leur modification.
Pour pouvoir utiliser des traitements plus complexe lors d'opération qui affectent plusieurs tables. Par exemple si on veut supprimer des données qui sont reliées à d'autres tables par des clés étrangères et que les options offerte ne nous suffise pas.
Pour garder une trace des opérations effectuées dans la base de données.
Mettre à jour des informations qu'on utilise régulièrement pour pouvoir les retrouver facilement, comme par exemple le solde actif d'un compte client.

Syntaxe

On utilise la commande CREATE TRIGGER pour la création d'un trigger. Le nom du trigger doit être unique dans la base de données. Il est d'usage de nommer le trigger avec le nom de la table associée, du moment de son déclenchement et de l'opération effectuée, ex je veux créer un trigger sur la table usager qui se déclenchera avant un insertion, je vais le nommer usager_before_insert.

CREATE TRIGGER nomTrigger 
    momentDuDeclenchement operationSQL ON nomTable
    FOR EACH ROW
    corpsDuTrigger;

Le moment du déclenchement est BEFORE ou AFTER
L'opération SQL est soit INSERT, UPDATE ou DELETE
Si le corps du trigger contient plus d'une requête et/ou opération, on doit les englober par un BEGIN END.

NEW et OLD

On peut utiliser les mots clés dans le corps du trigger pour accéder aux données avant ou après leur modification.

OLD représente les données avant qu'elles ne soient modifié par le trigger. Ces données ne peuvent pas être modifiées. Old est disponible pour les UPDATE et DELETE
NEW représente les données après qu'elles aient été modifié. C'est données peuvent être lu et modifiés. New est disponible pour les INSERT et UPDATE.

Requête	OLD	NEW
INSERT	-	OUI
UPDATE	OUI	OUI
DELETE	OUI	-

Erreur dans un trigger

Si un trigger exécuté avant (BEFORE) une requête cause une erreur, la requête ne sera pas exécutée. Les triggers exécutés après (AFTER) une requête qui génère une erreur feront échouer celle-ci. Il est donc important de bien concevoir les triggers et d'inclure une gestion des erreurs dans notre traitement.

Supprimer un trigger

Pour supprimer un trigger, on utilise la commande DROP TRIGGER suivie du nom du trigger. Si on supprimer une table, tous les triggers qui lui sont associée seront aussi supprimés.

DROP TRIGGER before_insert_usager;

Gestion d'erreurs dans un trigger

MySQL gère les erreurs dans les triggers de la façon suivante :

Si un trigger de type BEFORE échoue, l'opération sur la ligne correspondante ne sera pas effectuée.
Un trigger BEFORE est activé par l'essaie d'insérer ou de modifier la ligne, même s'il y a échec de l'opération.
Un trigger AFTER est exécuté seulement après qu'un trigger BEFORE et l'opération sur la ligne ont réussi.
Une erreur dans un trigger BEFORE et AFTER cause l'échec de toute l'opération.

On peut aussi gérer les erreurs, les exceptions et même les contraintes à l'aide de HANDLER ou de la commande SIGNAL. Voir les notes sur la gestions des erreurs

Voici un court exemple de gestion avec SIGNAL. On a une table compte avec un champ solde qui ne peut être initialisé à une valeur inférieur à 0.

Exemple gestion d'exceptions

DELIMITER $$

CREATE TRIGGER compte_before_insert
    BEFORE INSERT
    ON compte FOR EACH ROW
    BEGIN

        IF NEW.solde < 0 THEN 
            SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Le solde du nouveau compte ne peut être négatif';
        END IF;

    END $$

DELIMITER ;

Restrictions

On en peut pas utiliser de transaction dans un trigger, START TRANSACTION, COMMIT ou ROLLBACK
On peut utiliser une procédure stockée mais avec les restrictions suivantes :
- La procédure ne peut pas retourner d'informations, à part en utilisant un paramètre OUT ou IN/OUT.
- La procédure ne doit pas utiliser de transaction.
On ne peut pas modifier les données d'une table qui est utilisée dans la requête qui déclenche le trigger. Ça compte pour toutes les tables de la requête et non seulement la table associée au trigger.
Une suppression ou modification de données déclenchée par une clé étrangère ne provoquera pas l'exécution du trigger associé à cette table.

Exemple de triggers

J'ai une table joueur, un table guilde et une table joueur_guilde_log. Dans la table joueur il y a un champ guilde_id qui fait référence à la clé primaire de la table guilde. Quand on modifie la guilde d'un joueur, je voudrais sauvegarder les informations dans une autre table (le id du joueur, l'id de son acienne guile, l'id de la nouvelle et la date de la modification). Je vais créer un trigger AFTER sur la table joueur. Je vais aussi valider que la colonne guilde_id a été modifié avant d'ajouter les informations dans la table joueur_guilde_log.

DELIMITER $$

CREATE TRIGGER joueur_after_update
    AFTER UPDATE
    ON joueur FOR EACH ROW
    BEGIN

        IF OLD.guilde_id <> NEW.guilde_id THEN 
            INSERT INTO joueur_guild_log (joueur_id, 
                                          old_guild_id, 
                                          new_guild_id, 
                                          mod_date)
            VALUES (old.id, 
                    old.guilde_id, 
                    new.guilde_id, 
                    NOW());
        END IF;

    END $$

DELIMITER ;