Déclencheurs MySQL pour l'automatisation

Introduction

Dans ce laboratoire, vous apprendrez à utiliser les déclencheurs (triggers) MySQL pour automatiser les actions de base de données. Les déclencheurs sont des programmes stockés qui s'exécutent automatiquement en réponse à des événements spécifiques sur une table, tels que les opérations INSERT, UPDATE ou DELETE.

Vous allez créer une table products pour stocker les informations sur les articles et une table product_logs pour auditer les modifications. Vous implémenterez ensuite un déclencheur qui ajoutera automatiquement une entrée de journal dans product_logs chaque fois qu'un nouveau produit sera inséré dans la table products. Le laboratoire vous guidera également dans les étapes de test, de mise à jour et de suppression du déclencheur.

Configurer la base de données et les tables

Avant de créer un déclencheur, vous avez besoin d'une base de données et des tables nécessaires. Dans cette étape, vous allez créer une base de données nommée mydatabase et deux tables : products pour stocker les données des produits et product_logs pour stocker les journaux d'audit.

Tout d'abord, ouvrez le terminal depuis votre bureau.

Connectez-vous au serveur MySQL en tant qu'utilisateur root. Comme il s'agit d'un environnement de laboratoire, vous pouvez utiliser sudo pour vous connecter sans mot de passe.

sudo mysql -u root

Une fois connecté, vous verrez l'invite MySQL (mysql>).

Maintenant, créez la base de données mydatabase et basculez vers celle-ci. La clause IF NOT EXISTS empêche une erreur si la base de données existe déjà.

CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;

Ensuite, créez la table products. Cette table stockera l'ID, le nom et le prix de chaque produit.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

Maintenant, créez la table product_logs. Cette table stockera un journal de chaque nouveau produit ajouté.

CREATE TABLE product_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    action_description VARCHAR(255),
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Vous pouvez vérifier que les deux tables ont été créées avec succès en exécutant :

SHOW TABLES;

Vous devriez voir les tables products et product_logs listées dans la sortie.

+--------------------+
| Tables_in_mydatabase |
+--------------------+
| product_logs       |
| products           |
+--------------------+
2 rows in set (0.00 sec)

Maintenant que la base de données et les tables sont prêtes, vous pouvez passer à l'étape suivante.

Créer et tester un déclencheur

Une fois les tables en place, vous pouvez maintenant créer un déclencheur. Ce déclencheur s'activera après l'insertion d'une nouvelle ligne dans la table products et ajoutera une entrée correspondante dans la table product_logs.

Basculez vers votre base de données.

USE mydatabase;

Maintenant, créez le déclencheur. La commande DELIMITER est utilisée pour changer le délimiteur standard (;) en // afin que le point-virgule à l'intérieur du corps du déclencheur ne soit pas interprété comme la fin de l'instruction CREATE TRIGGER.

DELIMITER //

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, action_description)
    VALUES (NEW.id, CONCAT('New product added: ', NEW.name));
END//

DELIMITER ;

Décomposons cette instruction :

• CREATE TRIGGER products_after_insert : Définit un nouveau déclencheur nommé products_after_insert.
• AFTER INSERT ON products : Spécifie que le déclencheur se déclenchera après un événement INSERT sur la table products.
• FOR EACH ROW : Indique que la logique du déclencheur s'exécutera pour chaque ligne insérée.
• BEGIN...END : Encadre les instructions SQL du déclencheur.
• NEW.id, NEW.name : Le mot-clé NEW fait référence à la ligne qui vient d'être insérée. NEW.id est l'id du nouveau produit, et NEW.name est son nom.

Testons maintenant le déclencheur en insérant un nouveau produit.

INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);

Cette opération INSERT aurait dû activer le déclencheur. Pour le confirmer, vérifiez le contenu de la table product_logs.

SELECT * FROM product_logs;

Vous devriez voir un enregistrement dans la table product_logs, qui a été créé automatiquement par le déclencheur. La sortie ressemblera à ceci (le log_id et le log_time varieront) :

+--------+------------+-----------------------------+---------------------+
| log_id | product_id | action_description          | log_time            |
+--------+------------+-----------------------------+---------------------+
|      1 |          1 | New product added: Laptop   | 2024-05-27 10:30:00 |
+--------+------------+-----------------------------+---------------------+
1 row in set (0.00 sec)

Cela confirme que votre déclencheur fonctionne correctement.

Mettre à jour la logique du déclencheur

Vos exigences peuvent changer avec le temps. Par exemple, vous pourriez vouloir enregistrer plus de détails. Dans cette étape, vous allez mettre à jour le déclencheur pour qu'il enregistre également le prix du nouveau produit.

Dans MySQL, vous ne pouvez pas modifier directement un déclencheur. Vous devez d'abord supprimer le déclencheur existant, puis en créer un nouveau avec la logique mise à jour.

Basculez vers votre base de données.

USE mydatabase;

Tout d'abord, supprimez le déclencheur existant. La clause IF EXISTS empêche une erreur si le déclencheur a déjà été supprimé.

DROP TRIGGER IF EXISTS products_after_insert;

Maintenant, recréez le déclencheur avec la logique mise à jour. Cette nouvelle version inclura le prix du produit dans la action_description.

DELIMITER //

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, action_description)
    VALUES (NEW.id, CONCAT('New product added: ', NEW.name, ' with price ', NEW.price));
END//

DELIMITER ;

Pour tester le déclencheur mis à jour, insérez un autre produit.

INSERT INTO products (name, price) VALUES ('Mouse', 25.00);

Maintenant, interrogez la table product_logs pour voir toutes les entrées du journal.

SELECT * FROM product_logs;

La sortie devrait maintenant afficher deux entrées de journal. La deuxième entrée, pour la 'Mouse', devrait inclure le prix dans sa description, confirmant que votre déclencheur mis à jour fonctionne.

+--------+------------+----------------------------------------------+---------------------+
| log_id | product_id | action_description                           | log_time            |
+--------+------------+----------------------------------------------+---------------------+
|      1 |          1 | New product added: Laptop                    | 2024-05-27 10:30:00 |
|      2 |          2 | New product added: Mouse with price 25.00    | 2024-05-27 10:35:00 |
+--------+------------+----------------------------------------------+---------------------+
2 rows in set (0.00 sec)

Vous avez mis à jour le déclencheur avec succès.

Supprimer le déclencheur

Si un déclencheur n'est plus nécessaire, vous devez le supprimer pour éviter un traitement inutile et des problèmes de maintenance potentiels. Dans cette dernière étape, vous allez supprimer le déclencheur products_after_insert.

Basculez vers votre base de données.

USE mydatabase;

Pour supprimer le déclencheur, utilisez l'instruction DROP TRIGGER.

DROP TRIGGER IF EXISTS products_after_insert;

Vous pouvez vérifier que le déclencheur a été supprimé en exécutant la commande SHOW TRIGGERS.

SHOW TRIGGERS;

Cette commande devrait produire un ensemble vide, confirmant que le déclencheur a disparu.

Empty set (0.00 sec)

Pour en être absolument sûr, insérez un produit de plus.

INSERT INTO products (name, price) VALUES ('Keyboard', 75.00);

Maintenant, vérifiez la table product_logs.

SELECT * FROM product_logs;

La table ne devrait toujours contenir que les deux premières entrées de journal. Le produit 'Keyboard' ne devrait pas avoir d'entrée de journal, car le déclencheur a été supprimé avant l'insertion.

+--------+------------+----------------------------------------------+---------------------+
| log_id | product_id | action_description                           | log_time            |
+--------+------------+----------------------------------------------+---------------------+
|      1 |          1 | New product added: Laptop                    | 2024-05-27 10:30:00 |
|      2 |          2 | New product added: Mouse with price 25.00    | 2024-05-27 10:35:00 |
+--------+------------+----------------------------------------------+---------------------+
2 rows in set (0.00 sec)

Cela confirme que le déclencheur a été supprimé avec succès. Vous pouvez maintenant quitter le shell MySQL.

exit;

Résumé

Dans ce laboratoire, vous avez appris les bases de l'utilisation des déclencheurs MySQL pour l'automatisation. Vous avez réussi à configurer un environnement de base de données, à créer des tables et à implémenter un déclencheur pour enregistrer les événements d'insertion de données. Vous avez également pratiqué le cycle de vie complet d'un déclencheur, y compris sa création, son test, sa mise à jour et sa suppression.

Ces compétences sont essentielles pour des tâches telles que la création de pistes d'audit, l'application de règles métier complexes et le maintien automatique de l'intégrité des données au sein de votre base de données.