Comment utiliser les déclencheurs dans MySQL

L'auteur a choisi le Fonds pour la Diversité dans la Technologie pour recevoir un don dans le cadre du programme Écrire pour des Dons.

Introduction

Lorsqu'on travaille avec des bases de données relationnelles et le langage de requête structuré (SQL), la plupart des opérations sur les données sont effectuées à la suite de requêtes exécutées de manière explicite, telles que SELECT, INSERT ou UPDATE.

Cependant, les bases de données SQL peuvent également être programmées pour exécuter automatiquement des actions prédéfinies chaque fois qu'un événement spécifique se produit grâce aux déclencheurs. Par exemple, vous pouvez utiliser des déclencheurs pour conserver le journal de suivi de toutes les instructions DELETE ou mettre automatiquement à jour les résumés statistiques agrégés chaque fois que des lignes sont mises à jour ou ajoutées à la table.

Dans ce tutoriel, vous utiliserez différents déclencheurs SQL pour effectuer automatiquement des actions lorsque des lignes sont insérées, mises à jour ou supprimées.

Prérequis

Pour suivre ce guide, vous aurez besoin d'un ordinateur exécutant un système de gestion de base de données relationnelle (SGBDR) basé sur SQL. Les instructions et exemples de ce guide ont été validés en utilisant l'environnement suivant :

• Un serveur exécutant Ubuntu 20.04, avec un utilisateur non root disposant de privilèges administratifs et un pare-feu configuré avec UFW, comme décrit dans notre guide de configuration initiale du serveur pour Ubuntu 20.04.
• MySQL installé et sécurisé sur le serveur, comme indiqué dans Comment installer MySQL sur Ubuntu 20.04. Ce guide a été vérifié avec un utilisateur MySQL non root, créé en utilisant le processus décrit à l'étape 3.
• Une connaissance de base de l'exécution des requêtes SELECT, INSERT, UPDATE et DELETE pour manipuler les données dans la base de données comme décrit dans nos guides Comment SELECT des lignes depuis les tables en SQL, Comment insérer des données en SQL, Comment mettre à jour des données en SQL et Comment supprimer des données en SQL.
• Familiarité de base avec l'utilisation des requêtes imbriquées comme décrit dans notre guide Comment utiliser les requêtes imbriquées en SQL.
• Familiarité de base avec l'utilisation des fonctions mathématiques agrégées telles que décrites dans notre guide Comment utiliser les expressions mathématiques et les fonctions agrégées en SQL.

Vous aurez également besoin d'une base de données avec quelques tables remplies de données d'exemple afin de pouvoir pratiquer l'utilisation des fonctions. Nous vous encourageons à consulter la section suivante Connecter à MySQL et Configurer une Base de Données d'Exemple pour obtenir des détails sur la connexion à un serveur MySQL et la création de la base de test utilisée dans les exemples tout au long de ce guide.

Se connecter à MySQL et configurer une base de données exemple

Dans cette section, vous vous connecterez à un serveur MySQL et créerez une base de données d'exemple afin de pouvoir suivre les exemples des sections suivantes.

Pour ce guide, vous utiliserez une collection imaginaire d'objets de collection. Vous stockerez les détails des objets de collection actuellement possédés, garderez leur valeur totale facilement disponible, et vous assurerez que la suppression d'un objet de collection laisse toujours une trace.

Si votre système de base de données SQL fonctionne sur un serveur distant, connectez-vous à votre serveur en SSH depuis votre machine locale :

ssh sammy@your_server_ip

Ensuite, ouvrez l'invite du serveur MySQL, en remplaçant sammy par le nom de votre compte utilisateur MySQL :

mysql -u sammy -p

Créez une base de données nommée collectibles :

CREATE DATABASE collectibles;

Si la base de données a été créée avec succès, vous recevrez une sortie comme celle-ci :

Query OK, 1 row affected (0.01 sec)

Pour sélectionner la base de données collectibles, exécutez l'instruction USE suivante :

USE collectibles;

Vous recevrez la sortie suivante :

Database changed

Après avoir sélectionné la base de données, vous pouvez créer des tables d'exemple à l'intérieur. La table collectibles contiendra des données simplifiées sur les objets de collection dans la base de données. Elle comportera les colonnes suivantes :

• name : Cette colonne contient le nom de chaque objet de collection, exprimé en utilisant le type de données varchar avec un maximum de 50 caractères.
• value : Cette colonne stocke la valeur marchande de l'objet de collection en utilisant le type de données decimal avec un maximum de 5 chiffres avant le point décimal et 2 chiffres après celui-ci.

Créez le tableau d'exemple avec la commande suivante :

CREATE TABLE collectibles (

    name varchar(50),

    value decimal(5, 2)

);

Si la sortie suivante s'affiche, la table a été créée :

Query OK, 0 rows affected (0.00 sec)

La table suivante sera appelée collectibles_stats et sera utilisée pour suivre la valeur accumulée de tous les objets de collection dans la collection. Elle contiendra une seule ligne de données avec les colonnes suivantes :

• count : Cette colonne contient le nombre d'objets de collection possédés, exprimé à l'aide du type de données int.
• value : Cette colonne stocke la valeur accumulée de tous les objets de collection en utilisant le type de donnée decimal avec un maximum de 5 chiffres avant le point décimal et 2 chiffres après celui-ci.

Créez le tableau d'exemple avec la commande suivante :

CREATE TABLE collectibles_stats (

    count int,

    value decimal(5, 2)

);

Si la sortie suivante s'affiche, la table a été créée :

Query OK, 0 rows affected (0.00 sec)

La troisième et dernière table sera appelée collectibles_archive, qui suivra tous les objets de collection qui ont été retirés de la collection afin de s'assurer qu'ils ne disparaissent jamais. Elle contiendra des données similaires à la table collectibles, augmentées de la date de retrait. Elle utilise les colonnes suivantes :

• name : Cette colonne contient le nom de chaque objet de collection retiré, exprimé en utilisant le type de données varchar avec un maximum de 50 caractères.
• value : Cette colonne stocke la valeur marchande de l'objet de collection au moment de sa suppression en utilisant le type de données decimal avec un maximum de 5 valeurs avant la virgule décimale et 2 valeurs après celle-ci.
• removed_on : Cette colonne stocke la date et l'heure de suppression de chaque objet de collection archivé en utilisant le type de données timestamp avec la valeur par défaut NOW(), ce qui signifie la date actuelle chaque fois qu'une nouvelle ligne est insérée dans cette table.

Créez le tableau d'exemple avec la commande suivante :

CREATE TABLE collectibles_archive (

    name varchar(50),

    value decimal(5, 2),

    removed_on timestamp DEFAULT CURRENT_TIMESTAMP

);

Si la sortie suivante s'affiche, la table a été créée :

Query OK, 0 rows affected (0.00 sec)

Ensuite, chargez la table collectibles_stats avec l'état initial pour la collection de collectibles vide en exécutant l'opération INSERT INTO suivante :

INSERT INTO collectibles_stats SELECT COUNT(name), SUM(value) FROM collectibles;

L'opération INSERT INTO ajoutera une seule ligne à collectibles_stats avec les valeurs calculées en utilisant les fonctions d'agrégation pour compter toutes les lignes dans la table collectibles et pour sommer la valeur de tous les objets de collection en utilisant la colonne value et la fonction SUM. La sortie suivante indique que la ligne a été ajoutée :

Query OK, 1 row affected (0.002 sec)
Records: 1  Duplicates: 0  Warnings: 0

Vous pouvez vérifier cela en exécutant une instruction SELECT sur la table :

SELECT * FROM collectibles_stats;

Comme il n'y a pas encore d'objets de collection dans la base de données, le nombre initial d'articles est 0 et la valeur accumulée indique NULL :

+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

Avec cela, vous êtes prêt à suivre le reste du guide et à commencer à utiliser des déclencheurs dans MySQL.

Comprendre les déclencheurs

Les déclencheurs sont des instructions définies pour une table particulière qui s'exécutent automatiquement par la base de données chaque fois qu'un événement spécifié se produit dans cette table. Les déclencheurs peuvent être utilisés pour garantir que certaines actions seront effectuées de manière cohérente chaque fois qu'une instruction spécifique est exécutée sur une table, plutôt que de nécessiter que les utilisateurs de la base de données se souviennent de les exécuter manuellement.

Chaque déclencheur associé à une table est identifié par un nom défini par l'utilisateur et une paire de conditions qui indiquent au moteur de base de données quand exécuter le déclencheur. Ceux-ci peuvent être regroupés en deux classes distinctes :

• Événement de base de données : Le déclencheur peut être exécuté lorsque des instructions INSERT, UPDATE ou DELETE sont exécutées sur une table.
• Heure de l'événement : De plus, les déclencheurs peuvent être exécutés AVANT ou APRÈS l'instruction en question.

Combiner les deux groupes de conditions donne un total de six possibilités de déclenchement distinctes qui sont exécutées automatiquement chaque fois que la condition conjointe est remplie. Les déclencheurs qui se produisent avant l'exécution de l'instruction satisfaisant la condition sont BEFORE INSERT, BEFORE UPDATE et BEFORE DELETE. Ceux-ci peuvent être utilisés pour manipuler et valider les données avant qu'elles ne soient insérées ou mises à jour dans la table, ou pour sauvegarder les détails de la ligne supprimée à des fins d'audit ou d'archivage.

Les déclencheurs qui se produisent après l'exécution de l'instruction répondant à la condition sont AFTER INSERT, AFTER UPDATE et AFTER DELETE. Ceux-ci peuvent être utilisés pour mettre à jour des valeurs résumées dans une table distincte en fonction de l'état final de la base de données après l'instruction.

Pour effectuer des actions telles que valider et manipuler les données d'entrée ou archiver la ligne supprimée, la base de données permet d'accéder aux valeurs des données depuis l'intérieur des déclencheurs. Pour les déclencheurs INSERT, seules les données nouvellement insérées peuvent être utilisées. Pour les déclencheurs UPDATE, à la fois les données originales et mises à jour peuvent être accessibles. Enfin, avec les déclencheurs DELETE, seules les données de la ligne originale sont disponibles pour utilisation (puisqu'il n'y a pas de nouvelles données auxquelles se référer).

Les données à utiliser dans le corps du déclencheur sont exposées sous l'enregistrement OLD pour les données actuellement dans la base de données et l'enregistrement NEW pour les données que la requête va enregistrer. Vous pouvez vous référer à des colonnes individuelles en utilisant la syntaxe OLD.nom_colonne et NEW.nom_colonne.

L'exemple suivant montre la syntaxe générale d'une instruction SQL utilisée pour créer un nouveau déclencheur :

CREATE TRIGGER trigger_name trigger_condition

ON table_name

FOR EACH ROW

trigger_actions;

Décomposons la syntaxe en parties plus petites :

• CREATE TRIGGER est le nom de l'instruction SQL utilisée pour créer un nouveau déclencheur dans la base de données.
• trigger_name est le nom défini par l'utilisateur du déclencheur, utilisé pour décrire son rôle, de manière similaire à la façon dont les noms de tables et de colonnes sont utilisés pour décrire leur signification.
• ON table_name indique à la base de données que le déclencheur doit surveiller les événements se produisant sur la table table_name.
• trigger_condition est l'un des six choix possibles définissant quand le déclencheur doit s'exécuter, par exemple, BEFORE INSERT.
• FOR EACH ROW indique à la base de données que le déclencheur doit être exécuté pour chaque ligne affectée par l'événement déclencheur. Certaines bases de données prennent en charge des modèles d'exécution supplémentaires en dehors de FOR EACH ROW ; cependant, dans le cas de MySQL, l'exécution des instructions du corps du déclencheur pour chaque ligne affectée par l'instruction qui a provoqué l'exécution du déclencheur est la seule option.
• trigger_actions est le corps du déclencheur et définit ce qui se passe lorsque le déclencheur s’exécute. Il s’agit généralement d’une seule instruction SQL valide. Il est possible d’inclure plusieurs instructions dans le corps du déclencheur pour effectuer des opérations de données complexes en utilisant les mots-clés BEGIN et END pour entourer la liste des instructions dans un bloc. Cela est toutefois en dehors du cadre de ce tutoriel. Consultez la documentation officielle sur les déclencheurs pour en savoir plus sur la syntaxe utilisée pour définir les déclencheurs.

Dans la section suivante, vous allez créer des déclencheurs qui manipulent les données avant les opérations INSERT et UPDATE.

Manipulation des données avec les déclencheurs BEFORE INSERT et BEFORE UPDATE

Dans cette section, vous utiliserez des déclencheurs pour manipuler les données avant que les instructions INSERT et UPDATE ne soient exécutées.

Dans cet exemple, vous utiliserez des triggers pour vous assurer que tous les objets de collection dans la base de données utilisent des noms en majuscules pour garantir la cohérence. Sans utiliser de triggers, vous devriez vous rappeler d'utiliser des noms d'objets de collection en majuscules pour chaque instruction INSERT et UPDATE. Si vous oubliez, la base de données enregistrera les données telles quelles, ce qui peut entraîner des erreurs dans l'ensemble de données.

Vous commencerez par insérer un exemple d'objet de collection appelé spaceship model d'une valeur de $12,50. Le nom de l'objet sera écrit en minuscules pour illustrer le problème. Exécutez la commande suivante :

INSERT INTO collectibles VALUES ('spaceship model', 12.50);

Le message suivant confirme que l'article a été ajouté :

Query OK, 1 row affected (0.009 sec)

Vous pouvez vérifier que la ligne a été insérée en exécutant la requête SELECT :

SELECT * FROM collectibles;

La sortie suivante s'affichera à l'écran :

+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
+-----------------+-------+
1 row in set (0.000 sec)

L'objet de collection a été enregistré tel quel, avec le nom écrit uniquement en lettres minuscules.

Pour s'assurer que tous les futurs objets de collection seront toujours écrits en majuscules, vous allez créer un déclencheur BEFORE INSERT. Utiliser un déclencheur qui s'exécute avant que l'instruction déclenchante ne soit exécutée permet de manipuler les données qui seront transmises à la base de données avant que cela ne se produise.

Exécutez l'instruction suivante :

CREATE TRIGGER uppercase_before_insert BEFORE INSERT

ON collectibles

FOR EACH ROW

SET NEW.name = UPPER(NEW.name);

Cette commande crée un déclencheur nommé uppercase_before_insert qui sera exécuté AVANT toutes les instructions INSERT sur la table nommée collectibles.

L'instruction dans le trigger SET NEW.name=UPPER(NEW.name) sera exécutée pour chaque ligne insérée. La commande SQL SET attribue la valeur du côté droit au côté gauche. Dans ce cas, NEW.name représente la valeur de la colonne name que la requête d'insertion va enregistrer. En appliquant la fonction UPPER au nom à insérer et en l'assignant à nouveau à la valeur de la colonne, vous convertissez la casse des lettres de la valeur qui sera enregistrée dans la base de données.

SET GLOBAL log_bin_trust_function_creators = 1;

Le paramètre log_bin_trust_function_creators contrôle si les utilisateurs qui créent des déclencheurs et des fonctions stockées peuvent être considérés comme dignes de confiance pour ne pas créer de déclencheurs entraînant l’écriture d’événements non sûrs dans le journal binaire. Par défaut, la valeur du paramètre est 0, ce qui permet uniquement aux superutilisateurs de créer des déclencheurs dans un environnement avec la journalisation binaire activée. En changeant la valeur à 1, tout utilisateur émettant des instructions CREATE TRIGGER sera considéré comme capable de comprendre les implications.

Après avoir mis à jour le paramètre, déconnectez-vous en tant que root, reconnectez-vous en tant qu'utilisateur, et relancez l'instruction CREATE TRIGGER.

Pour en savoir plus sur la journalisation binaire et la réplication dans MySQL et sur la manière dont elles se rapportent aux triggers, nous vous encourageons à consulter la documentation officielle de MySQL : Le journal binaire et la journalisation binaire des programmes stockés. Vous pouvez également consulter notre tutoriel, Comment configurer la réplication dans MySQL.

Avant d’utiliser des triggers dans un environnement de production avec réplication en place, ou avec des exigences strictes de récupération à un instant précis, assurez-vous d’avoir évalué leur impact sur la cohérence des journaux binaires.

GRANT TRIGGER on *.* TO 'sammy'@'localhost';

FLUSH PRIVILEGES;

Après avoir mis à jour les permissions de l'utilisateur, déconnectez-vous en tant que root, reconnectez-vous en tant qu'utilisateur, et relancez l'instruction CREATE TRIGGER.

MySQL affichera le message suivant pour confirmer que le déclencheur a été créé avec succès :

Query OK, 1 row affected (0.009 sec)

Essayez maintenant d'insérer un nouveau collectible, en utilisant à nouveau un argument en minuscules pour la requête INSERT :

INSERT INTO collectibles VALUES ('aircraft model', 10.00);

Et encore une fois, vérifiez les lignes résultantes dans la table collectibles :

SELECT * FROM collectibles;

La sortie suivante s'affichera à l'écran :

+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
| AIRCRAFT MODEL  | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Cette fois, cependant, la nouvelle entrée indique AIRCRAFT MODEL avec toutes les lettres en majuscules — différent de l'entrée que vous avez essayé d'insérer. Le déclencheur s'est exécuté en arrière-plan et a converti la casse des lettres avant que la ligne ne soit enregistrée dans la base de données.

Toutes les nouvelles lignes sont désormais protégées par le déclencheur pour garantir que les noms seront enregistrés en majuscules. Cependant, il est toujours possible d'enregistrer des données sans restriction en utilisant des instructions UPDATE. Pour protéger les instructions UPDATE avec le même effet, créez un autre déclencheur :

CREATE TRIGGER uppercase_before_update BEFORE UPDATE

ON collectibles

FOR EACH ROW

SET NEW.name = UPPER(NEW.name);

La différence entre les deux déclencheurs réside dans les critères du déclencheur. Cette fois, il s'agit de BEFORE UPDATE, ce qui signifie que le déclencheur s'exécutera chaque fois qu'une instruction UPDATE est émise sur la table — affectant les lignes existantes à chaque mise à jour, en plus des nouvelles lignes couvertes par le déclencheur précédent.

MySQL affichera une confirmation indiquant que le déclencheur a été créé avec succès :

Query OK, 0 row affected (0.009 sec)

Pour vérifier le comportement du nouveau déclencheur, essayez de mettre à jour la valeur du prix pour le modèle de vaisseau spatial :

UPDATE collectibles SET value = 15.00 WHERE name = 'spaceship model';

La clause WHERE filtre la ligne à mettre à jour par nom, et la clause SET change la valeur à 15,00.

Vous recevrez la sortie suivante, confirmant que l'instruction a modifié une seule ligne :

Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Vérifiez les lignes résultantes dans la table collectibles :

SELECT * FROM collectibles;

La sortie suivante s'affichera à l'écran :

+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
| AIRCRAFT MODEL  | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Maintenant, en plus de la mise à jour du prix à 15.00 par l'instruction exécutée, le nom indique maintenant SPACESHIP MODEL. Lorsque vous avez exécuté l'instruction UPDATE, le déclencheur a été exécuté, affectant les valeurs de la ligne mise à jour. La colonne du nom a été convertie en majuscules avant l'enregistrement.

Dans cette section, vous avez créé deux déclencheurs fonctionnant avant les requêtes INSERT et avant les requêtes UPDATE pour conformer les données avant de les enregistrer dans la base de données. Dans la section suivante, vous utiliserez des déclencheurs BEFORE DELETE pour copier les lignes supprimées dans une table séparée pour l'archivage.

Utilisation des déclencheurs BEFORE DELETE pour exécuter des actions avant de supprimer des lignes

Même si vous ne possédez plus un objet, vous pourriez vouloir laisser une entrée concernant la suppression dans une table séparée. Au début de ce tutoriel, vous avez créé une deuxième table appelée collectibles_archive pour suivre tous les objets de collection qui ont été retirés de la collection. Dans cette section, vous allez archiver les entrées supprimées avec un déclencheur qui s’exécutera avant les instructions DELETE.

Vérifiez si la table d'archives est complètement vide en exécutant l'instruction suivante :

SELECT * FROM collectibles_archive;

La sortie suivante s'affichera à l'écran, confirmant que la table collectibles_archive est vide :

Empty set (0.000 sec)

Maintenant, si vous exécutez une requête DELETE sur la table collectibles, n'importe quelle ligne de la table pourrait être supprimée sans laisser de trace.

Pour remédier à cela, vous allez créer un déclencheur qui s'exécutera avant toutes les requêtes DELETE sur la table collectibles. Le but de ce déclencheur est de sauvegarder une copie de l'objet supprimé dans la table d'archive avant que la suppression n'ait lieu.

Exécutez la commande suivante :

CREATE TRIGGER archive_before_delete BEFORE DELETE

ON collectibles

FOR EACH ROW

INSERT INTO collectibles_archive (name, value) VALUES (OLD.name, OLD.value);

Le déclencheur est nommé archive_before_delete et se produit AVANT toute requête DELETE sur la table collectibles. Pour chaque ligne qui sera supprimée, l'instruction INSERT sera exécutée. À son tour, l'instruction INSERT insère une nouvelle ligne dans la table collectibles_archive avec des valeurs de données tirées de l'enregistrement OLD, qui est celui prévu pour la suppression : OLD.name devient la colonne name et OLD.value devient la colonne value.

La base de données confirmera la création du déclencheur :

Query OK, 0 row affected (0.009 sec)

Avec le déclencheur en place, essayez de supprimer un objet de collection de la table principale des objets de collection :

DELETE FROM collectibles WHERE name = 'SPACESHIP MODEL';

La sortie confirme que la requête a été exécutée avec succès :

Query OK, 1 row affected (0.004 sec)

Maintenant, listez tous les objets de collection :

SELECT * FROM collectibles;

La sortie suivante s'affichera à l'écran :

+----------------+-------+
| name           | value |
+----------------+-------+
| AIRCRAFT MODEL | 10.00 |
+----------------+-------+
1 row in set (0.000 sec)

Seul le MODÈLE D'AVION reste maintenant ; le MODÈLE DE VAISSEAU SPATIAL a été supprimé et n'est plus dans la table. Cependant, avec le déclencheur créé précédemment, cette suppression devrait être enregistrée dans la table collectibles_archive. Vérifions cela.

Exécuter une autre requête :

SELECT * FROM collectibles_archive;

La sortie suivante s'affichera à l'écran :

+-----------------+-------+---------------------+
| name            | value | removed_on          |
+-----------------+-------+---------------------+
| SPACESHIP MODEL | 15.00 | 2022-11-20 11:32:01 |
+-----------------+-------+---------------------+
1 row in set (0.000 sec)

La suppression a été automatiquement notée dans cette table par le déclencheur. Les colonnes name et value ont été remplies avec les données de la ligne qui a été supprimée. La troisième colonne, removed_on, n'est pas explicitement définie par le déclencheur, donc elle prend la valeur par défaut décidée lors de la création de la table : la date de création de toute nouvelle ligne. À cause de cela, chaque entrée ajoutée avec l'aide du déclencheur sera toujours annotée avec la date de suppression.

Avec ce déclencheur en place, vous pouvez maintenant être sûr que toutes les requêtes DELETE entraîneront une entrée de journal dans collectibles_archive, laissant derrière elles des informations sur les objets de collection précédemment possédés.

Dans la section suivante, vous utiliserez des déclencheurs exécutés après les instructions déclenchantes pour mettre à jour la table récapitulative avec des valeurs agrégées basées sur tous les objets de collection.

Utilisation des déclencheurs AFTER INSERT, AFTER UPDATE et AFTER DELETE pour exécuter des actions après la manipulation des données

Dans les deux sections précédentes, vous avez utilisé des triggers exécutés avant les instructions principales pour effectuer des opérations basées sur les données originales avant de mettre à jour la base de données. Dans cette section, vous mettrez à jour la table récapitulative avec un décompte toujours à jour et la valeur accumulée de tous les objets de collection en utilisant des triggers qui s'exécutent après les instructions prévues. De cette manière, vous serez certain que les données de la table récapitulative prennent en compte l'état actuel de la base de données.

Commencez par examiner la table collectibles_stats :

SELECT * FROM collectibles_stats;

Puisque vous n'avez pas encore ajouté d'informations à ce tableau, le nombre d'objets de collection possédés est 0, et donc, la valeur accumulée est NULL :

+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

Puisqu'il n'y a pas de déclencheurs pour cette table, les requêtes précédemment émises pour insérer et mettre à jour des objets de collection n'ont pas affecté cette table.

L'objectif est de définir les valeurs dans une seule ligne de la table collectibles_stats afin de présenter des informations à jour sur le nombre et la valeur totale des objets de collection. Vous souhaitez vous assurer que le contenu de la table est mis à jour après chaque opération INSERT, UPDATE ou DELETE.

Vous pouvez faire cela en créant trois déclencheurs séparés, tous exécutés après la requête correspondante. Tout d'abord, créez le déclencheur AFTER INSERT :

CREATE TRIGGER stats_after_insert AFTER INSERT

ON collectibles

FOR EACH ROW

UPDATE collectibles_stats

SET count = (

    SELECT COUNT(name) FROM collectibles

), value = (

    SELECT SUM(value) FROM collectibles

);

Le déclencheur est nommé stats_after_insert et s'exécutera APRÈS chaque requête INSERT sur la table collectibles, exécutant la requête UPDATE dans le corps du déclencheur. La requête UPDATE affecte la table collectibles_stats et définit les colonnes count et value aux valeurs retournées par les requêtes imbriquées :

• SELECT COUNT(name) FROM collectibles obtiendra le nombre de collectibles.
• SELECT SUM(value) FROM collectibles obtiendra la valeur totale de tous les objets de collection.

La base de données confirmera la création du déclencheur :

Query OK, 0 row affected (0.009 sec)

Maintenant, essayez de réinsérer le modèle de vaisseau spatial précédemment supprimé dans la table des objets de collection pour vérifier si le tableau récapitulatif sera correctement mis à jour :

INSERT INTO collectibles VALUES ('spaceship model', 15.00);

La base de données affichera le message de succès suivant :

Query OK, 1 row affected (0.009 sec)

Vous pouvez lister tous les objets de collection possédés en exécutant :

SELECT * FROM collectibles;

La sortie suivante s'affichera à l'écran :

+-----------------+-------+
| name            | value |
+-----------------+-------+
| AIRCRAFT MODEL  | 10.00 |
| SPACESHIP MODEL | 15.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Il y a deux objets de collection d'une valeur totale de 25.00. Pour examiner le tableau récapitulatif après l'élément nouvellement inséré, exécutez la requête suivante :

SELECT * FROM collectibles_stats;

Cette fois, le tableau récapitulatif indiquera le nombre de tous les objets de collection possédés comme 2 et la valeur cumulée comme 25,00, ce qui correspond à la sortie précédente :

+-------+-------+
| count | value |
+-------+-------+
|     2 | 25.00 |
+-------+-------+
1 row in set (0.000 sec)

Le déclencheur stats_after_insert s'exécute après la requête INSERT et met à jour la table collectibles_stats avec les données actuelles (count et value) concernant la collection. Des statistiques sont recueillies sur le contenu de l’ensemble de la collection, pas seulement sur le dernier ajout. Puisque la collection contient maintenant deux objets (modèles d’avions et de vaisseaux spatiaux), la table récapitulative indique deux objets et leur valeur totale. À ce stade, l’ajout de tout nouvel objet de collection dans la table collectibles mettra à jour la table récapitulative avec les valeurs correctes.

Cependant, la mise à jour des éléments existants ou la suppression d'objets de collection n'affectera pas du tout le résumé. Pour combler cette lacune, vous allez créer deux déclencheurs supplémentaires, effectuant des opérations identiques mais déclenchés par des événements différents :

CREATE TRIGGER stats_after_update AFTER UPDATE

ON collectibles

FOR EACH ROW

UPDATE collectibles_stats

SET count = (

    SELECT COUNT(name) FROM collectibles

), value = (

    SELECT SUM(value) FROM collectibles

);



CREATE TRIGGER stats_after_delete AFTER DELETE

ON collectibles

FOR EACH ROW

UPDATE collectibles_stats

SET count = (

    SELECT COUNT(name) FROM collectibles

), value = (

    SELECT SUM(value) FROM collectibles

);

Vous avez maintenant créé deux nouveaux déclencheurs : stats_after_update et stats_after_delete. Les deux déclencheurs s'exécuteront sur la table collectible_stats chaque fois que vous exécuterez une instruction UPDATE ou DELETE sur la table collectibles.

La création réussie de ces déclencheurs affichera la sortie suivante :

Query OK, 0 row affected (0.009 sec)

Maintenant, mettez à jour la valeur du prix pour l'un des objets de collection :

UPDATE collectibles SET value = 25.00 WHERE name = 'AIRCRAFT MODEL';

La clause WHERE filtre la ligne à mettre à jour par nom, et la clause SET change la valeur à 25,00.

La sortie confirme que l'instruction a modifié une seule ligne :

Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Encore une fois, vérifiez le contenu du tableau récapitulatif après la mise à jour :

SELECT * FROM collectibles_stats;

La valeur affiche maintenant 40.00, ce qui est la valeur correcte après la mise à jour :

+-------+-------+
| count | value |
+-------+-------+
|     2 | 40.00 |
+-------+-------+
1 row in set (0.000 sec)

La dernière étape consiste à vérifier que le tableau récapitulatif reflétera correctement la suppression d'un objet de collection. Essayez de supprimer le modèle d'avion avec l'instruction suivante :

DELETE FROM collectibles WHERE name = 'AIRCRAFT MODEL';

La sortie suivante confirme que la requête s'est exécutée avec succès :

Query OK, 1 row affected (0.004 sec)

Maintenant, listez tous les objets de collection :

SELECT * FROM collectibles;

La sortie suivante s'affichera à l'écran :

+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
+-----------------+-------+
1 row in set (0.000 sec)

Il ne reste maintenant que MODÈLE DE VAISSEAU SPATIAL. Ensuite, vérifiez les valeurs dans le tableau récapitulatif :

SELECT * FROM collectibles_stats;

La sortie suivante affichera :

+-------+-------+
| count | value |
+-------+-------+
|     1 | 15.00 |
+-------+-------+
1 row in set (0.000 sec)

La colonne count montre maintenant qu'il n'y a qu'un seul objet de collection dans le tableau principal. La valeur totale est 15.00, correspondant à la valeur du MODÈLE DE VAISSEAU SPATIAL.

Ces trois déclencheurs fonctionnent conjointement après les requêtes INSERT, UPDATE et DELETE pour maintenir la table récapitulative synchronisée avec la liste complète des objets de collection.

Dans la section suivante, vous apprendrez comment manipuler les déclencheurs existants dans la base de données.

Lister et Supprimer les Déclencheurs

Dans les sections précédentes, vous avez créé de nouveaux déclencheurs. Puisque les déclencheurs sont des objets nommés définis dans la base de données, tout comme les tables, vous pouvez également les lister et les manipuler lorsque c'est nécessaire.

Pour lister tous les déclencheurs, exécutez l'instruction SHOW TRIGGERS :

SHOW TRIGGERS;

La sortie inclura tous les déclencheurs, y compris leurs noms, l'événement déclencheur avec l'heure (BEFORE ou AFTER l'exécution de la déclaration), ainsi que les déclarations faisant partie du corps du déclencheur et d'autres détails étendus de la définition du déclencheur :

Output, simplified for readability
+-------------------------+--------+--------------+--------(...)+--------+(...)
| Trigger                 | Event  | Table        | Statement   | Timing |(...)
+-------------------------+--------+--------------+--------(...)+--------+(...)
| uppercase_before_insert | INSERT | collectibles | SET    (...)| BEFORE |(...)
| stats_after_insert      | INSERT | collectibles | UPDATE (...)| AFTER  |(...)
| uppercase_before_update | UPDATE | collectibles | SET    (...)| BEFORE |(...)
| stats_after_update      | UPDATE | collectibles | UPDATE (...)| AFTER  |(...)
| archive_before_delete   | DELETE | collectibles | INSERT (...)| BEFORE |(...)
| stats_after_delete      | DELETE | collectibles | UPDATE (...)| AFTER  |(...)
+-------------------------+--------+--------------+--------(...)+--------+(...)
6 rows in set (0.001 sec)

Pour supprimer les déclencheurs existants, vous pouvez utiliser les instructions SQL DROP TRIGGER. Peut-être que vous ne souhaitez plus imposer des lettres majuscules pour les noms de collectionnable, donc les déclencheurs uppercase_before_insert et uppercase_before_update ne sont plus nécessaires. Exécutez les commandes suivantes pour supprimer ces deux déclencheurs :

DROP TRIGGER uppercase_before_insert;

DROP TRIGGER uppercase_before_update;

Pour les deux commandes, MySQL répondra par un message de succès :

Query OK, 0 rows affected (0.004 sec)

Maintenant, avec les deux déclencheurs disparus, ajoutons un nouveau collectible en minuscules :

INSERT INTO collectibles VALUES ('ship model', 10.00);

La base de données confirmera l'insertion :

Query OK, 1 row affected (0.009 sec)

Vous pouvez vérifier que la ligne a été insérée en exécutant la requête SELECT :

SELECT * FROM collectibles;

La sortie suivante s'affichera à l'écran :

+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
| ship model      | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Le nouvel objet de collection ajouté est en lettres minuscules. Puisque le nom n'a pas été modifié par rapport à la sortie originale, vous avez confirmé que le déclencheur qui convertissait auparavant la casse des lettres n'est plus utilisé.

Vous savez maintenant comment lister et supprimer des déclencheurs par nom.

Conclusion

En suivant ce guide, vous avez appris ce que sont les triggers SQL et comment les utiliser dans MySQL pour manipuler les données avant les requêtes INSERT et UPDATE. Vous avez appris comment utiliser un trigger BEFORE DELETE pour archiver la ligne supprimée dans une table séparée, ainsi que comment utiliser des triggers AFTER au niveau de la requête pour garder les résumés constamment à jour.

Vous pouvez utiliser des fonctions pour déléguer une partie de la manipulation et de la validation des données au moteur de base de données, garantissant l'intégrité des données ou cachant certains comportements de la base de données à l'utilisateur quotidien de la base de données. Ce tutoriel n'a couvert que les bases de l'utilisation des déclencheurs à cet effet. Vous pouvez créer des déclencheurs complexes composés de plusieurs instructions et utiliser une logique conditionnelle pour effectuer des actions de manière encore plus granulaire. Pour en savoir plus à ce sujet, consultez la documentation MySQL sur les déclencheurs.

Si vous souhaitez en savoir plus sur différents concepts liés au langage SQL et à son utilisation, nous vous encourageons à consulter les autres guides de la série Comment utiliser SQL.