Les différents types de triggers MySQL

Les différents types de triggers MySQL

  1. Objectifs

    • Connaitre les types de triggers dans une base de données MySQL
    • Apprendre à créer des triggers dans votre base de données MySQL
    • Dans ce tutoriel, vous allez créer, utiliser et supprimer différents types de triggers dans votre base de données MySQL.
  2. Présentation

    • Les triggers de base de données MySQL

    • Un Trigger MySQL est un programme stocké qui est exécuté automatiquement pour répondre à un événement spécifique tel qu’une insertion, une mise à jour ou une suppression se produisant dans une table.
      • Il existe 6 types de Triggers différents dans MySQL :
      • MySQL Before Insert Trigger
      • MySQL After Insert Trigger
      • MySQL Before Update Trigger
      • MySQL Update Update Trigger
      • MySQL Before Delete Trigger
      • MySQL Delete Delete Trigger
    • La principale différence entre le trigger Before et After dans MySQL est que le trigger Before effectue une action avant qu’une certaine opération ne s’exécute sur la table, tandis que le trigger After effectue une action après l’exécution d’une certaine opération sur la table.



  3. MySQL BEFORE INSERT trigger

    1. Description
      • comme son nom l’indique, il s’agit d’un déclencheur qui s’exécute avant qu’une mise à jour ne soit invoquée.
    2. Restrictions
      • Vous ne pouvez pas créer de Trigger BEFORE INSERT sur une vue.
      • Vous ne pouvez pas mettre à jour les NOUVELLES valeurs d’un Trigger BEFORE INSERT.
      • Vous ne pouvez pas mettre à jour les anciennes valeurs d’un Trigger BEFORE INSERT.
    3. Syntaxe
      • Voici la syntaxe pour créer un déclencheur BEFORE INSERT dans MySQL:
        CREATE TRIGGER trigger_name
        BEFORE INSERT
        ON table_name FOR EACH ROW
        Trigger_body ;
    4. Paramètres
      • Le paramètre de syntaxe du trigger BEFORE INSERT peut être expliqué comme suit :
        1. Spécifier le nom du Trigger que vous voulez créer. Il doit être unique dans le schéma.
        2. Spécifier le temps d’action du Trigger, qui doit être la clause BEFORE INSERT pour appeler le déclencheur.
        3. Écrire le nom d’une table à laquelle le Trigger est associé. Il doit être écrit après le mot-clé ON.
        4. Spécifier l’instruction à exécuter lorsque le déclencheur est activé.
  4. MySQL AFTER INSERT trigger

    1. Description
      • MySQL déclenche un trigger AFTER INSERT après l’exécution de l’opération INSERT.
    2. Restrictions
      • Vous ne pouvez pas créer de Trigger AFTER INSERT sur une vue.
      • Vous ne pouvez pas mettre à jour les NOUVELLES valeurs d’un Trigger AFTER INSERT.
      • Vous ne pouvez pas mettre à jour les anciennes valeurs d’un Trigger AFTER INSERT.
    3. Syntaxe
      • Voici la syntaxe pour créer un déclencheur BEFORE INSERT dans MySQL:
        CREATE TRIGGER trigger_name
        AFTER INSERT
        ON table_name FOR EACH ROW
        Trigger_body ;
    4. Paramètres
      • Le paramètre de syntaxe du trigger AFTER INSERT peut être expliqué comme suit :
        1. Spécifier le nom du Trigger que vous voulez créer. Il doit être unique dans le schéma.
        2. Spécifier le temps d’action du Trigger, qui doit être la clause AFTER INSERT pour appeler le déclencheur.
        3. Écrire le nom d’une table à laquelle le Trigger est associé. Il doit être écrit après le mot-clé ON.
        4. Spécifier l’instruction à exécuter lorsque le déclencheur est activé.
    5. Exemple
  5. MySQL AFTER UPDATE trigger

    1. Description
      • Un trigger AFTER UPDATE signifie que MySQL déclenchera ce trigger avant que l’opération UPDATE ne soit exécutée.
      • Un trigger AFTER UPDATE est invoqué une fois qu’un enregistrement de base de données est mis à jour avec succès. Ce comportement rend le trigger adapté à l’enregistrement des audits. Dans un environnement multi-utilisateurs, l’administrateur peut vouloir consulter un historique des modifications apportées par les utilisateurs dans une table particulière, à des fins d’audit.
    2. Restrictions
      • Vous ne pouvez pas créer de Trigger AFTER UPDATE sur une vue.
      • Vous ne pouvez pas mettre à jour les NOUVELLES valeurs d’un Trigger AFTER UPDATE.
      • Vous ne pouvez pas mettre à jour les anciennes valeurs d’un Trigger AFTER UPDATE.
    3. Syntaxe
      • Voici la syntaxe pour créer un déclencheur BEFORE UPDATE dans MySQL:
        CREATE TRIGGER trigger_name
        AFTER UPDATE
        ON table_name FOR EACH ROW
        Trigger_body ;
    4. Paramètres
      • Le paramètre de syntaxe du trigger AFTER UPDATE peut être expliqué comme suit :
        1. Spécifier le nom du Trigger que vous voulez créer. Il doit être unique dans le schéma.
        2. Spécifier le temps d’action du Trigger, qui doit être la clause AFTER INSERT pour appeler le déclencheur.
        3. Écrire le nom d’une table à laquelle le Trigger est associé. Il doit être écrit après le mot-clé ON.
        4. Spécifier l’instruction à exécuter lorsque le déclencheur est activé.



    5. Exemple
    6. MySQL Before Delete trigger

      1. Description
        • Les triggers BEFORE DELETE sont invoqués avant qu’une instruction DELETE ne s’exécute sur une table. Ces types de triggers sont généralement utilisés pour faire respecter l’intégrité référentielle dans différentes tables apparentées.
        • Le trigger BEFORE DELETE peut empêcher la suppression accidentelle d’informations connexes dans une base de données.
        • Toutefois, dans certaines situations, vous pouvez vouloir supprimer tous les enregistrements associés à un enregistrement particulier dans les différentes tables connexes. Dans cette situation, vous utiliserez le trigger AFTER DELETE, que vous allez tester à l’étape suivante.
    7. MySQL After Delete trigger

      1. Description
        • Les triggers AFTER DELETE sont activés une fois qu’un enregistrement a été supprimé avec succès.
        • Vous pouvez par exemple utiliser un trigger AFTER DELETE dans une situation où le niveau de remise qu’un client particulier reçoit est déterminé par le nombre de ventes effectuées pendant une période définie. Si certains des enregistrements du client sont supprimés de la table sales, le niveau de réduction du client devrait être réduit.
        • Une autre utilisation du trigger AFTER DELETE consiste à supprimer des informations connexes d’une autre table une fois qu’un enregistrement de table de base est supprimé.
    8. Application

      1. Étape 1 — Création d’un exemple de base de données
        • Dans cette étape, vous allez créer un exemple de base de données client avec plusieurs tables, afin de démontrer le fonctionnement des triggers MySQL.
        • Pour en savoir plus sur les requêtes MySQL, consultez notre Introduction aux requêtes dans MySQL.
        • Create database test_db;
        • commencerez par créer une table de clients “customers“.
          • Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;
        • Ajoutez quelques enregistrements à la table “customers“. Pour ce faire, exécutez les commandes suivantes une par une :
          • Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
            Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
            Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');
        • Créez une deuxième table de clients “customer_status“.
          • Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;
        • Ensuite, vous allez créer une table de ventes “sales“. Cette table contiendra les données de vente (sales) relatives aux différents clients via la colonne customer_id :
          • Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;
        • Ensuite, créez une table audit_log pour enregistrer les mises à jour apportées à la table des ventes sales lorsque vous implémenterez le trigger AFTER UPDATE à l’étape 5 :
          • Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, 
            updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;
      2. Étape 2 — Création d’un trigger Before Insert
        • Créez un trigger dont la fonction consiste à vérifier si le sales_amount inséré dans la table des ventes est supérieur à 10000 et à signaler une erreur si cette valeur est évaluée comme vraie (true).
          • Ce trigger sera associé à la table des ventes sales et il sera invoqué avant l’insertion d’un enregistrement (Before Insert) pour valider le sales_amount.
          • DELIMITER //
            CREATE TRIGGER validate_sales_amount
            BEFORE INSERT
            ON sales
            FOR EACH ROW
            IF NEW.sales_amount>10000 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'La vente a dépassé le montant autorisé de 10 000.';
            END IF//
            DELIMITER ;
            
          • Vous utilisez l’instruction IF... THEN... END IF pour évaluer si la quantité fournie lors de l’instruction INSERT est comprise dans votre plage.
          • Le trigger est capable d’extraire la nouvelle valeur sales_amount fournie en utilisant le mot-clé NEW.
          • Les différents types de triggers MySQL

      3. Étape 3 — Création d’un trigger After Insert
        • Demandez à MySQL d’enregistrer un autre enregistrement dans la table customer_status une fois qu’un nouvel enregistrement client est inséré dans la table de clients customers.
          • Les triggers AFTER INSERT sont exécutés lorsque les enregistrements sont insérés avec succès dans une table. Cette fonctionnalité peut être utilisée pour exécuter automatiquement d’autres logiques commerciales.
          • Par exemple, dans une application bancaire, un déclencheur AFTER INSERT peut fermer un compte de prêt lorsqu’un client a fini de rembourser le prêt.
          • Le trigger peut surveiller tous les paiements insérés dans une table de transactions et fermer le prêt automatiquement une fois que le solde du prêt est à zéro.
          • Dans cette étape, vous travaillerez avec votre table “customer_status” en utilisant un trigger AFTER INSERT pour entrer des enregistrements de clients apparentés.
          • Pour créer le trigger AFTER INSERT, entrez les commandes suivantes :
          • DELIMITER //
            CREATE TRIGGER customer_status_records
            AFTER INSERT
            ON customers
            FOR EACH ROW
            Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
            DELIMITER ;
            
          • insérez un nouvel enregistrement dans la table customers pour confirmer que votre code de trigger sera invoqué :Insert into customers (customer_id, customer_name, level )values(‘4′,’DAVID DOE’,’VIP’);
          • Les différents types de triggers MySQL

          • La sortie confirme que le trigger a bien été exécuté.
      4. Étape 4 — Création d’un trigger Before Update
        • On suppose que vous disposez de deux niveaux pour vos clients dans la base de données. Dans cet exemple, une fois qu’un compte client est mis à jour au niveau VIP, le compte ne peut pas être rétrogradé au niveau BASIC. Pour faire appliquer une telle règle, vous allez créer un trigger (BEFORE UPDATE) qui s’exécutera avant l’instruction UPDATE, comme indiqué ci-dessous. Si un utilisateur de la base de données tente de rétrograder un client du niveau VIP au niveau BASIC, une exception définie par l’utilisateur sera déclenchée.
          • DELIMITER //
            CREATE TRIGGER validate_customer_level
            BEFORE UPDATE
            ON customers
            FOR EACH ROW
            IF OLD.level='VIP' THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Un client VIP ne peut pas être déclassé.';
            END IF //
            DELIMITER ;
            
          • Vous utilisez le mot-clé OLD pour capturer le niveau fourni par l’utilisateur lors de l’exécution de la commande UPDATE.
          • Encore une fois, vous utilisez l’instruction IF... THEN... END IF pour signaler à l’utilisateur une instruction d’erreur générique.
          • Exécutez la commande SQL suivante qui tente de rétrograder un compte client associé au customer_id 3
          • Update customers set level=’BASIC’ where customer_id=’3′;

          • Vous verrez la sortie suivante.
          • Les différents types de triggers MySQL

      5. Étape 5 — Création d’un trigger After Update
        • Créez un trigger qui enregistre l’activité de mise à jour de la table sales. Notre table audit_log contiendra des informations sur les utilisateurs MySQL qui mettent à jour la table sales, la date de la mise à jour, et les valeurs new et old de sales_amount.
          • DELIMITER //
            CREATE TRIGGER log_sales_updates
            AFTER UPDATE
            ON sales
            FOR EACH ROW
            Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
            DELIMITER ;
            
          • Vous insérez un nouvel enregistrement dans la table audit_log. Vous utilisez le mot-clé NEW pour récupérer la valeur du sales_id et du nouveau sales_amount En outre, vous utilisez le mot-clé OLD pour récupérer le sales_amount précédent puisque vous voulez enregistrer les deux montants à des fins d’audit.
          • La commande SELECT User() récupère l’utilisateur actuel de l’opération et l’instruction NOW() extrait la valeur de la date et de l’heure actuelle du serveur MySQL
          • Maintenant, si un utilisateur essaie de mettre à jour la valeur d’un enregistrement dans la table sales, le trigger log_sales_updates va insérer un nouvel enregistrement dans la table audit_log.
          • Créons un nouvel enregistrement de ventes avec un sales_id aléatoire de 5 et essayons de le mettre à jour. En premier lieu, insérez l’enregistrement des ventes avec :
            Insert into sales(sales_id, customer_id, sales_amount) values(‘5’, ‘2’,’8000′);
          • Ensuite, mettez à jour l’enregistrement :
            Update sales set sales_amount=’9000′ where sales_id=’5′;
          • Le trigger a enregistré la mise à jour. Votre sortie indique l’ancien et le nouveau sales_amount enregistrés avec l’utilisateur qui a mis à jour les enregistrements :
          • Les différents types de triggers MySQL

      6. Étape 6 — Création d’un trigger Before Delete
        • Par exemple, chaque enregistrement de la table “sales” concerne un customer_id de la table “customers“.
        • Si un utilisateur de la base de données a supprimé un enregistrement de la table “customers” qui a un enregistrement lié dans la table “sales“, vous n’auriez aucun moyen de connaître le client associé à cet enregistrement.
        • Créez un trigger BEFORE DELETE pour faire appliquer votre logique.
        • DELIMITER //
          CREATE TRIGGER validate_related_records
          BEFORE DELETE
          ON customers
          FOR EACH ROW
          IF OLD.customer_id in (select customer_id from sales) THEN
          SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'Le client a un enregistrement de vente connexe.';
          END IF//
          DELIMITER ;
        • Essayez de supprimer un client qui a un enregistrement de vente connexe : Delete from customers where customer_id=’2′;
        • Vous recevrez alors la sortie suivante :
        • Les différents types de triggers MySQL

      7. Étape 7 — Création d’un trigger After Delete
        • Définissez un trigger qui supprime l’enregistrement du client si les enregistrements de ventes avec le customer_id correspondant sont supprimés de la table sales.
        • DELIMITER //
          CREATE TRIGGER delete_related_info
          AFTER DELETE
          ON sales
          FOR EACH ROW
          Delete from customers where customer_id=OLD.customer_id;//
          DELIMITER ;
          



Abonnez vous à notre chaîne YouTube gratuitement