L’auteur a choisi the Apache Software Foundation pour recevoir un don dans le cadre du programme Write for DOnations.
Dans MySQL, un trigger (ou déclencheur) est une commande SQL définie par l’utilisateur qui est automatiquement invoquée lors d’une opération INSERT
, DELETE
ou UPDATE
. Le code trigger est associé à une table et est détruit une fois que la table est supprimée. Vous pouvez spécifier une heure de déclenchement de l’action et définir si elle sera activée avant ou après l’événement défini dans la base de données.
Les triggers ont plusieurs avantages. Par exemple, vous pouvez les utiliser pour générer la valeur d’une colonne dérivée lors d’une instruction INSERT
. Un autre cas d’utilisation consiste à faire respecter l’intégrité référentielle par laquelle vous pouvez utiliser un trigger pour sauvegarder un enregistrement dans plusieurs tables apparentées. D’autres avantages incluent la journalisation des actions des utilisateurs dans les tables d’audit ainsi que la copie en direct des données dans différents schémas de base de données à des fins de redondance, pour éviter un point de défaillance.
Vous pouvez également utiliser des triggers pour maintenir des règles de validation au niveau de la base de données. Cela permet de partager la source de données entre plusieurs applications sans rompre la logique commerciale. Cela réduit considérablement les allers-retours vers le serveur de la base de données, ce qui améliore donc le temps de réponse de vos applications. Comme le serveur de base de données exécute des triggers, ils peuvent profiter de ressources serveur améliorées telles que la RAM et le processeur.
Dans ce tutoriel, vous allez créer, utiliser et supprimer différents types de triggers dans votre base de données MySQL.
Avant de commencer, assurez-vous que vous disposez de ce qui suit :
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.
Tout d’abord, connectez-vous à votre serveur MySQL en tant que root :
Entrez votre mot de passe root MySQL lorsqu’il vous est demandé et cliquez sur ENTER
pour continuer. Lorsque vous voyez l’invite mysql>
, exécutez la commande suivante pour créer une base de données test_db
:
OutputQuery OK, 1 row affected (0.00 sec)
Ensuite, accédez à test_db
avec :
OutputDatabase changed
Vous commencerez par créer une table de clients customers
. Cette table contiendra les enregistrements des clients (customers), y compris les customer_id
, customer_name
, et level
. Il y aura deux niveaux de clients : BASIC
et VIP
.
OutputQuery OK, 0 rows affected (0.01 sec)
Maintenant, ajoutez quelques enregistrements à la table customers
. Pour ce faire, exécutez les commandes suivantes une par une :
Vous verrez la sortie suivante après avoir exécuté chacune des commandes INSERT
:
OutputQuery OK, 1 row affected (0.01 sec)
Pour vous assurer que les échantillons d’enregistrements ont été insérés avec succès, exécutez la commande SELECT
:
Output+-------------+---------------+-------+
| customer_id | customer_name | level |
+-------------+---------------+-------+
| 1 | JOHN DOE | BASIC |
| 2 | MARY ROE | BASIC |
| 3 | JOHN DOE | VIP |
+-------------+---------------+-------+
3 rows in set (0.00 sec)
Vous allez également créer une autre table pour conserver les informations relatives au compte de clients customers
. La table contiendra les champs customer_id
et status_notes
Exécutez la commande suivante :
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
:
OutputQuery OK, 0 rows affected (0.01 sec)
Vous allez ajouter des échantillons de données aux données de vente sales
lors des étapes à venir, tout en testant les triggers. 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 :
OutputQuery OK, 0 rows affected (0.02 sec)
Avec la base de données test_db
et les quatre tables en place, vous allez maintenant travailler avec les différents triggers MySQL dans votre base de données.
Dans cette étape, vous allez examiner la syntaxe d’un trigger MySQL avant d’appliquer cette logique pour créer un trigger BEFORE INSERT
qui valide le champ sales_amount
lorsque des données sont insérées dans la table sales
.
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
.
Remarque : Lorsque vous créez le trigger body, vous pouvez utiliser les mots-clés OLD
et NEW
pour accéder aux valeurs de colonnes anciennes et nouvelles entrées lors d’une opération INSERT
, UPDATE
et DELETE
. Dans un déclencheur DELETE
, seul le mot-clé OLD
peut être utilisé (que vous utiliserez à l’étape 4).
Vous allez maintenant créer votre premier trigger BEFORE INSERT
. Ce trigger sera associé à la table des ventes sales
et il sera invoqué avant l’insertion d’un enregistrement pour valider le sales_amount
. La fonction du trigger 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).
Assurez-vous que vous êtes connecté au serveur MySQL. Ensuite, entrez les commandes MySQL suivantes une par une :
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
.
Pour faire apparaître un message d’erreur générique, vous utilisez les lignes suivantes pour informer l’utilisateur de l’erreur :
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
Ensuite, insérez un enregistrement avec un sales_amount
de 11000
dans la table des ventes
pour vérifier si le trigger arrêtera l’opération :
OutputERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.
Cette erreur montre que le code du trigger fonctionne comme prévu.
Essayez maintenant un nouvel enregistrement avec une valeur de 7500
pour vérifier si la commande aboutira :
Comme la valeur est dans la plage recommandée, vous verrez la sortie suivante :
OutputQuery OK, 1 row affected (0.01 sec)
Pour confirmer que les données ont été insérées, exécutez la commande suivante :
La sortie confirme que les données sont dans la table :
Output+----------+-------------+--------------+
| sales_id | customer_id | sales_amount |
+----------+-------------+--------------+
| 1 | 1 | 7500 |
+----------+-------------+--------------+
1 row in set (0.00 sec)
Dans cette étape, vous avez testé des triggers pour valider les données avant leur insertion dans une base de données.
Ensuite, vous allez travailler avec le trigger AFTER INSERT
pour enregistrer les informations connexes dans différentes tables.
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 :
OutputQuery OK, 0 rows affected (0.00 sec)
Ici, vous 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
.
Maintenant, insérez un nouvel enregistrement dans la table customers
pour confirmer que votre code de trigger sera invoqué :
OutputQuery OK, 1 row affected (0.01 sec)
Comme l’enregistrement a été inséré avec succès, vérifiez qu’un nouvel enregistrement de statut a été inséré dans la table customer_status
:
Output+-------------+-----------------------------+
| customer_id | status_notes |
+-------------+-----------------------------+
| 4 | ACCOUNT OPENED SUCCESSFULLY |
+-------------+-----------------------------+
1 row in set (0.00 sec)
La sortie confirme que le trigger a bien été exécuté.
Le trigger AFTER INSERT
est utile pour surveiller le cycle de vie d’un client. Dans un environnement de production, les comptes clients peuvent subir différentes étapes telles que l’ouverture, la suspension et la fermeture du compte.
Dans les étapes suivantes, vous allez travailler avec des triggers UPDATE
.
Un trigger BEFORE UPDATE
est similaire au trigger BEFORE INSERT
— la différence est le moment où ils sont invoqués. Vous pouvez utiliser le trigger BEFORE UPDATE
pour vérifier une logique commerciale avant de mettre à jour un enregistrement. Pour tester cela, vous utiliserez la table customers
dans laquelle vous avez déjà inséré certaines données.
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 retrogradé 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.
Entrez les commandes SQL suivantes une par une pour créer le trigger BEFORE UPDATE
:
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.
Ensuite, exécutez la commande SQL suivante qui tente de rétrograder un compte client associé au customer_id
3
:
Vous verrez la sortie suivante fournissant le SET MESSAGE_TEXT
: :
OutputERROR 1644 (45000): A VIP customer can not be downgraded.
Si vous exécutez la même commande pour un client de niveau BASIC
, et que vous essayez de faire passer le compte au niveau VIP
, la commande sera exécutée avec succès :
OutputRows matched: 1 Changed: 1 Warnings: 0
Vous avez utilisé le trigger BEFORE UPDATE
pour faire appliquer une règle commerciale. Vous allez maintenant utiliser un trigger AFTER UPDATE
pour l’enregistrement des audits.
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.
Vous allez créer 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
.
Pour créer le trigger, exécutez les commandes SQL suivantes :
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 :
OutputQuery OK, 1 row affected (0.00 sec)
Ensuite, mettez à jour l’enregistrement :
Vous verrez la sortie suivante :
OutputRows matched: 1 Changed: 1 Warnings: 0
Exécutez maintenant la commande suivante pour vérifier si le trigger AFTER UPDATE
a pu enregistrer un nouvel enregistrement dans la table audit_log
:
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 :
Output+--------+----------+-----------------+------------+----------------+---------------------+
| log_id | sales_id | previous_amount | new_amount | updated_by | updated_on |
+--------+----------+-----------------+------------+----------------+---------------------+
| 1 | 5 | 8000 | 9000 | root@localhost | 2019-11-07 09:28:36 |
+--------+----------+-----------------+------------+----------------+---------------------+
1 row in set (0.00 sec)
Vous disposez également de la date et de l’heure auxquelles la mise à jour a été effectuée, ce qui est précieux à des fins d’audit.
Ensuite, vous utiliserez le trigger DELETE
pour faire respecter l’intégrité du référencement au niveau de la base de données.
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. 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.
Pour éviter cela, vous pouvez créer un trigger BEFORE DELETE
pour faire appliquer votre logique. Exécutez les commandes SQL suivantes une par une :
Maintenant, essayez de supprimer un client qui a un enregistrement de vente connexe :
Vous recevrez alors la sortie suivante :
OutputERROR 1644 (45000): The customer has a related sales record.
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.
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é. Par exemple, vous allez définir 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
. Exécutez la commande suivante pour créer votre trigger :
Ensuite, exécutez ce qui suit pour supprimer tous les enregistrements de ventes associés à un customer_id
de 2
:
OutputQuery OK, 1 row affected (0.00 sec)
Vérifiez maintenant s’il existe des enregistrements pour le client dans la table sales
:
Vous recevrez une sortie Empty Set
puisque l’enregistrement client associé au customer_id
de 2
a été supprimé par le trigger :
OutputEmpty set (0.00 sec)
Vous avez maintenant utilisé chacune des différentes formes de triggers pour effectuer des fonctions spécifiques. Ensuite, vous allez voir comment vous pouvez supprimer un trigger de la base de données si vous n’en avez plus besoin.
Comme pour tout autre objet de la base de données, vous pouvez supprimer des triggers à l’aide de la commande DROP
. Voici la syntaxe de suppression d’un trigger :
Drop trigger [TRIGGER NAME];
Par exemple, pour supprimer le dernier trigger AFTER DELETE
que vous avez créé, exécutez la commande suivante :
OutputQuery OK, 0 rows affected (0.00 sec)
La nécessité de supprimer des triggers se pose lorsque vous voulez en recréer la structure. Dans ce cas, vous pouvez supprimer le trigger et en redéfinir un nouveau avec les différentes commandes de trigger.
Dans ce tutoriel, vous avez créé, utilisé et supprimé les différents types de triggers d’une base de données MySQL. À l’aide d’un exemple de base de données relative aux clients, vous avez mis en place des triggers pour différents cas d’utilisation tels que la validation des données, une application de logique d’entreprise, l’enregistrement des audits et la mise en application de l’intégrité référentielle.
Pour obtenir des informations supplémentaires sur l’utilisation de votre base de données MySQL, consultez ce qui suit :
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!