Les triggers de base de données MySQL

Les triggers de base de données MySQL

  1. Objectifs

    • Connaitre les triggers MySQL
  2. Définition

    • Les triggers de base de données MySQL

    • Dans MySQL, un trigger ou trigger est un programme stocké invoqué automatiquement en réponse à un événement tel que insert , update ou delete qui se produit dans la table associée.
    • Les triggers sont des procédures stockées qui s’exécutent automatiquement quant un événement se produit.
    • En général cet événement représente une opération DML (Data Manipulation Language ) sur une table.
    • Les instructions DML doivent inclure INSERT, UPDATE ou DELETE Ils permettent entre autre de contrôler les accès à la base de données.
    • Un Trigger, également appelé déclencheur, permet d’exécuter un ensemble d’instruction SQL juste après un événement. Cela permet de faciliter et d’automatiser des actions au sein d’un Système de Gestion de Base de Données (SGBD).
    • “Un trigger est un objet de base de données nommé, qui est associé à une table et qui s’active lorsqu’un événement particulier survient dans une table”
    • Un Trigger est un bloc d’instructions SQL :
      • Il est rattaché à une seule table
      • Il réagit à 3 événements : INSERT, UPDATE, DELETE
      • Il s’exécute soit avant soit après un des ces événements
      • L’accession aux données modifiées via les événements est possible avec les mots-clés New.nom_colonne ou OLD.nom_colonne
    • Une table peut avoir 6 triggers Maximum



  3. Utiliser les triggers MySQL

    • Cas d’utilisation
      • Log des utilisateurs/changements
      • Exécuter des règles métier
      • Renforcer les contraintes d’intégrité
      • Dupliquer des données
      • Améliorer les performances et les changements
    • Fonctions des triggers MySQL
      • Chaque trigger est associé à une table et a un nom et une fonction uniques basés sur deux facteurs :
        1. Temps . BEFORE ou AFTER un événement de ligne spécifique.
        2. Événement . INSERT, UPDATE ou DELETE.

        Les triggers de base de données MySQL

  4. Syntaxe simplifiée

    • La création d’un trigger en SQL peut être effectué via la syntaxe suivante :
    •          CREATE [OR REPALCE] TRIGGER nomtrigger
                {BEFORE [ AFTER] INSERT OR UPDATE OR DELETE}
                ON Nomdetable [FOR EACH ROW] [WHEN condition]
               BLOC PL/SQL 
      
    • Le nom du trigger doit être unique dans la base de données.
    • L’option BEFORE /AFTER indique le moment du déclenchement du trigger.
    • Les instructions SQL INSERT OR UPDATE OR DELETE peuvent être toutes présentes comme on peut en avoir juste une.
    • Pour un UPDATE, on peut spécifier une liste de colonnes. Dans ce cas, le trigger ne se déclenchera que s’il porte sur l’une des colonnes précisées dans la liste.
    • La syntaxe générale utilisée pour créer un trigger MySQL est illustrée dans l’exemple suivant :
    • DELIMITER //
      CREATE TRIGGER [TRIGGER_NAME]
      [TRIGGER TIME] [TRIGGER EVENT]
      ON [TABLE]
      FOR EACH ROW
      [TRIGGER BODY]//
      DELIMITER ;

    • La structure du trigger comprend :
      • DELIMITER // : le délimiteur MySQL par défaut est ; — il est nécessaire de le changer en autre chose afin que MySQL traite les lignes suivantes comme une seule commande jusqu’à ce qu’il atteigne votre délimiteur personnalisé. Dans cet exemple, le délimiteur est changé en // et ensuite le délimiteur ; est redéfini à la fin.
      • [TRIGGER_NAME] : Un trigger doit avoir un nom et c’est là que vous incluez la valeur.
      • [TRIGGER TIME] : Un trigger peut être invoqué à différents moments. MySQL vous permet de définir si le trigger démarrera avant ou après une opération de base de données.
      • [TRIGGER EVENT]: Les triggers ne sont appelés que par les opérations INSERT, UPDATE et DELETE. Vous pouvez utiliser n’importe quelle valeur ici en fonction de ce que vous voulez réaliser.
      • [TABLE]: Tout trigger que vous créez sur votre base de données MySQL doit être associé à une table.
      • FOR EACH ROW : Cette instruction ordonne à MySQL d’exécuter le code du trigger pour chaque ligne que ce dernier affecte.
      • [TRIGGER BODY] : le code qui est exécuté lorsque le trigger est invoqué est appelé_ trigger body​​_​. Ce peut être une seule instruction SQL ou plusieurs commandes. Notez que si vous exécutez plusieurs instructions SQL sur le trigger body, vous devez les inclure dans un bloc BEGIN… END.
  5. Créer ou remplacer un déclencheur

    • Le CREATE OR REPLACE n’est pas supporté par MySQL

    • MySQL n’autorise pas cette syntaxe, vous devez plutôt utiliser les éléments suivants:
    • DELIMITER $$
      DROP TRIGGER IF EXISTS myTrigger;
      $$
      CREATE TRIGGER myTrigger
      -- ...
      
      $$
      DELIMITER ;
      
    • Attention, ce n’est pas une transaction atomique :
      • vous perdrez l’ancien déclencheur si le CREATE échoue
      • sur une charge lourde, d’autres opérations peuvent se LOCK TABLES myTable WRITE; entre le DROP et le CREATE , utilisez un LOCK TABLES myTable WRITE; tout d’abord pour éviter les incohérences de données et UNLOCK TABLES; après le CREATE pour libérer la table
  6. OLD et NEW triggers MySQL

    • Dans le corps du trigger, MySQL met à disposition deux mots-clés : OLD et NEW.
      • OLD : représente les valeurs des colonnes de la ligne traitée avant qu’elle ne soit modifiée par l’événement déclencheur. Ces valeurs peuvent être lues, mais pas modifiées.
      • NEW : représente les valeurs des colonnes de la ligne traitée après qu’elle a été modifiée par l’événement déclencheur. Ces valeurs peuvent être lues et modifiées.
    • Il n’y a que dans le cas d’un trigger UPDATE que OLD et NEW coexistent. Lors d’une insertion, OLD n’existe pas, puisque la ligne n’existe pas avant l’événement déclencheur ; dans le cas d’une suppression, c’est NEW qui n’existe pas, puisque la ligne n’existera plus après l’événement déclencheur.
    • Premier exemple :
      • Pour inserer une ligne. Exécuter la commande suivante :
        • INSERT INTO Adoption (client_id, animal_id, date_reservation, prix, paye) VALUES (12, 15, NOW(), 200.00, FALSE);
      • Pendant le traitement de cette ligne par le trigger correspondant,
        • NEW.client_id vaudra 12 ;
          NEW.animal_id vaudra 15 ;
          NEW.date_reservation vaudra NOW() ;
          NEW.date_adoption vaudra NULL ;
          NEW.prix vaudra 200.00 ;
          NEW.paye vaudra FALSE (0).

      • Les valeurs de OLD ne seront pas définies. Dans le cas d’une suppression, on aura exactement l’inverse.
    • Second exemple
    • La modification d’une ligne. On modifie la ligne que l’on vient d’insérer en exécutant la commande suivante :
      • UPDATE Adoption SET paye = TRUE WHERE client_id = 12 AND animal_id = 15;
    • Pendant le traitement de cette ligne par le trigger correspondant,
      • — NEW.paye vaudra TRUE, tandis que OLD.paye vaudra FALSE.
        — Par contre les valeurs respectives de NEW.animal_id, NEW.client_id, NEW.date_reservation, NEW.date_adoption et NEW.prix seront les mêmes que OLD.animal_id, OLD.client_id, OLD.date_reservation, OLD.date_adoption et OLD.prix, puisque ces colonnes ne sont pas modifiées par la requête.

  7. Types de triggers

    • La norme SQL définit deux types de triggers : les triggers au niveau des lignes et les triggers au niveau des instructions.
      • Un trigger au niveau de la ligne est activé pour chaque ligne insérée, mise à jour ou supprimée. Par exemple, si une table a 100 lignes insérées, mises à jour ou supprimées, le trigger est automatiquement invoqué 100 fois pour les 100 lignes affectées.
      • Un trigger au niveau de l’instruction est exécuté une fois pour chaque transaction, quel que soit le nombre de lignes insérées, mises à jour ou supprimées.
    • MySQL ne prend en charge que les triggers au niveau des lignes. Il ne prend pas en charge les triggers au niveau de l’instruction.
    • Un trigger se compose donc de trois partie :
      1. l’événement déclencheur (after/before insert/update/delete)
      2. la condition supplémentaire (when)
      3. l’action à mener (le bloc begin/end)

    • Pour plus d’information consultez notre article : les types de triggers MySQL
  8. Stockage des triggers MySQL

    • Les triggers MySQL sont stockés dans le répertoire /data/classicmodels avec le nom tablename.TRGettriggername.TRN :
      • Le fichier tablename.TRG associe le trigger à la table correspondante
      • Le fichier triggername.TRN contient la définition du trigger qui sera exécuté une fois qu’un événement se produit
    • Nous pouvons sauvegarder les triggers MySQL en copiant les fichiers de triggers dans le dossier de sauvegarde ou à tout autre endroit que nous souhaitons.
    • Nous pouvons également traiter le dossier de déclenchement comme un référentiel VCS et effectuer sa sauvegarde chaque fois que quelque chose change.
  9. Avantages et Inconvénients des triggers MySQL

    • Avantages des triggers MySQL
      • Les triggers offrent un autre moyen de vérifier l’intégrité des données.
      • Les triggers gèrent les erreurs de la couche de base de données.
      • Les triggers offrent un autre moyen d’ exécuter des tâches planifiées . En utilisant des triggers, vous n’avez pas à attendre que les événements planifiés s’exécutent car les triggers sont appelés automatiquement avant ou après qu’une modification est apportée aux données d’une table.
      • Les triggers peuvent être utiles pour auditer les modifications de données dans les tables.
    • Inconvénients des triggers MySQL
      • Les triggers ne peuvent fournir que des validations étendues, pas toutes les validations. Pour des validations simples, vous pouvez utiliser les contraintes NOT NULL, UNIQUE, CHECK et FOREIGN KEY.
      • Les triggers peuvent être difficiles à dépanner car ils s’exécutent automatiquement dans la base de données, ce qui peut ne pas être invisible pour les applications clientes.
      • Les triggers peuvent augmenter la surcharge du serveur MySQL.



Abonnez vous à notre chaîne YouTube gratuitement