Trigger & Stored Procedures MySQL, Automatiser une tâche SQL

Dans le monde des bases de données, l’automatisation des tâches est essentielle pour améliorer la performance, la cohérence et la sécurité des transactions SQL. MySQL offre deux fonctionnalités puissantes pour y parvenir : trigger et stored procedures.

Trigger et stored procedures permettent d’exécuter automatiquement des requêtes SQL en réponse à certains événements ou d’encapsuler des suites d’instructions SQL pour une exécution optimisée.

Dans ce tutoriel, nous allons explorer en profondeur les concepts de trigger et stored procedures, voir comment les implémenter et identifier les cas d’usage les plus pertinents.

Comprendre le Trigger en MySQL

Qu’est-ce qu’un Trigger en MySQL ?

Un Trigger (ou déclencheur) est une fonction automatique dans MySQL qui s’exécute lorsqu’un événement spécifique se produit sur une table. Il permet d’effectuer des actions immédiatement après (ou avant) qu’une donnée soit insérée, modifiée ou supprimée.

Par exemple, si un utilisateur ajoute une nouvelle commande dans une base de données, un trigger peut automatiquement mettre à jour le stock sans intervention humaine.

Les triggers sont très utiles pour assurer la cohérence et l’intégrité des données. Ils permettent d’automatiser certaines tâches répétitives et d’éviter les erreurs humaines. Une fois créés, ils s’exécutent toujours en arrière-plan sans nécessiter d’action supplémentaire de la part des utilisateurs.

Cas précis pour utiliser un Trigger en MySQL

Imaginons que tu gères un site e-commerce et que tu veux garder une trace de toutes les suppressions de commandes dans une table spéciale. Avec un Trigger, chaque fois qu’une commande est supprimée, elle est automatiquement enregistrée dans une autre table pour conserver un historique. Cela permet d’éviter la perte d’informations et d’assurer un suivi en cas d’erreur ou de fraude.

Syntaxe d’un Trigger en MySQL

Un trigger (ou déclencheur) est donc un élément de programmation SQL qui s’exécute automatiquement lorsqu’un événement spécifique se produit sur une table. Il est souvent utilisé pour garantir l’intégrité des données ou pour automatiser des mises à jour en fonction d’une action (INSERT, UPDATE ou DELETE).

Voici la structure générale d’un trigger :

CREATE TRIGGER nom_du_trigger
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON nom_de_la_table
    FOR EACH ROW
BEGIN
    -- Instructions SQL
END;

• BEFORE | AFTER : Indique si le trigger s’exécute avant ou après l’action.
• INSERT | UPDATE | DELETE : Spécifie l’événement qui déclenche le trigger.
• FOR EACH ROW : Le trigger s’exécute pour chaque ligne concernée par l’événement.

Exemple d’un Trigger en MySQL

Prenons un exemple concret. Imaginons que nous ayons une table ventes et que nous souhaitons automatiquement mettre à jour une table stock chaque fois qu’une vente est enregistrée.

Création de la table stock

CREATE TABLE stock (
    produit_id INT PRIMARY KEY,
    quantite INT NOT NULL
);

Création de la table ventes

CREATE TABLE ventes (
    vente_id INT PRIMARY KEY AUTO_INCREMENT,
    produit_id INT,
    quantite_vendue INT,
    date_vente TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Création du trigger de mise à jour du stock

CREATE TRIGGER mise_a_jour_stock
AFTER INSERT ON ventes
FOR EACH ROW
BEGIN
    UPDATE stock
    SET quantite = quantite - NEW.quantite_vendue
    WHERE produit_id = NEW.produit_id;
END;

1. Chaque fois qu’une nouvelle vente est insérée dans ventes, le trigger mise_a_jour_stock s’exécute.
2. Il met à jour la quantité disponible dans stock en soustrayant la quantité vendue.
3. NEW.quantite_vendue fait référence à la nouvelle ligne insérée.

Ce type d’automatisation permet de garantir que les données restent cohérentes sans intervention manuelle.

Comprendre les Stored Procedures en MySQL

Qu’est-ce qu’une Stored Procedure en MySQL ?

Une Stored Procedure (ou procédure stockée) est un ensemble d’instructions SQL enregistrées directement dans la base de données. Plutôt que d’écrire plusieurs requêtes à chaque utilisation, on peut regrouper ces requêtes dans une procédure et l’exécuter en une seule commande. Cela permet de gagner du temps et de simplifier la gestion des données.

Les procédures stockées sont très pratiques pour automatiser des traitements complexes, comme la génération de rapports ou la mise à jour de plusieurs tables en une seule opération. Elles améliorent également la sécurité, car elles permettent aux utilisateurs d’exécuter des actions spécifiques sans leur donner un accès direct aux tables sensibles de la base de données.

Des formations informatique pour tous !

Débutant ou curieux ? Apprenez le développement web, le référencement, le webmarketing, la bureautique, à maîtriser vos appareils Apple et bien plus encore…

Formateur indépendant, professionnel du web depuis 2006, je vous accompagne pas à pas et en cours particulier, que vous soyez débutant ou que vous souhaitiez progresser. En visio, à votre rythme, et toujours avec pédagogie.

Découvrez mes formations Qui suis-je ?

Cas précis pour utiliser une Stored Procedure en MySQL

Supposons que tu veuilles générer un rapport mensuel sur les ventes de ton site e-commerce. Plutôt que d’écrire à chaque fois plusieurs requêtes SQL pour récupérer les ventes, calculer le total et afficher les résultats, tu peux créer une Stored Procedure qui regroupe toutes ces actions. Ensuite, en lançant une simple commande, tu obtiens directement ton rapport sans avoir à tout retaper à chaque fois. Cela te fait gagner du temps et évite les erreurs.

Syntaxe d’une Stored Procedure

Une stored procedure (procédure stockée) est donc un ensemble d’instructions SQL enregistrées sur le serveur MySQL et exécutables à la demande. Elle permet d’automatiser des tâches complexes, d’améliorer la performance des requêtes et de simplifier la gestion des bases de données.

Voici la structure générale d’une procédure stockée en MySQL :

DELIMITER $$

CREATE PROCEDURE nom_de_la_procedure(param1 TYPE, param2 TYPE)
BEGIN
    -- Instructions SQL
END $$

DELIMITER ;

• DELIMITER $$ : Change le délimiteur pour éviter les conflits avec ; dans le code SQL.
• CREATE PROCEDURE : Déclare une nouvelle procédure stockée.
• param1 TYPE, param2 TYPE : Définit les paramètres d’entrée.
• BEGIN ... END : Contient le code SQL exécuté lorsque la procédure est appelée.

Exemple de Stored Procedure en MySQL

Imaginons que nous souhaitons récupérer toutes les ventes d’un produit donné sur une période spécifique.

Création de la procédure

DELIMITER $$

CREATE PROCEDURE get_ventes_produit(
    IN produit_id_param INT, 
    IN date_debut DATE, 
    IN date_fin DATE
)
BEGIN
    SELECT * FROM ventes
    WHERE produit_id = produit_id_param 
    AND date_vente BETWEEN date_debut AND date_fin;
END $$

DELIMITER ;

Exécution de la procédure

CALL get_ventes_produit(1, '2024-01-01', '2024-03-31');

Avantages

• Réutilisation : Une procédure stockée peut être appelée plusieurs fois sans réécrire la requête.
• Optimisation des performances : MySQL précompile les procédures pour une exécution plus rapide.
• Sécurité : Permet de limiter l’accès direct aux tables en donnant uniquement accès à des procédures spécifiques.

Comparaison entre Triggers et Stored Procedures

Les triggers sont idéaux pour gérer des actions automatiques liées aux transactions (mise à jour de stock, gestion des logs, calculs automatiques).
Les stored procedures sont parfaites pour regrouper plusieurs opérations complexes et éviter la redondance des requêtes.

Cas pratiques d’utilisation

Trigger pour journaliser les suppressions

Si vous voulez conserver une trace des suppressions dans une table historique_suppressions :

CREATE TABLE historique_suppressions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    produit_id INT,
    date_suppression TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER log_suppression
AFTER DELETE ON ventes
FOR EACH ROW
BEGIN
    INSERT INTO historique_suppressions (produit_id)
    VALUES (OLD.produit_id);
END;

Stored Procedure pour calculer le chiffre d’affaires

DELIMITER $$

CREATE PROCEDURE calculer_CA(IN date_debut DATE, IN date_fin DATE)
BEGIN
    SELECT SUM(quantite_vendue) AS total_vendu 
    FROM ventes
    WHERE date_vente BETWEEN date_debut AND date_fin;
END $$

DELIMITER ;

CALL calculer_CA('2024-01-01', '2024-12-31');

Les triggers et les stored procedures sont deux outils puissants pour automatiser les tâches SQL dans MySQL.

• Utilisez des triggers pour exécuter des actions automatiquement en réponse à des modifications de données.
• Utilisez des procédures stockées pour regrouper et optimiser des requêtes SQL complexes.

En les combinant intelligemment, vous pouvez améliorer la performance et la fiabilité de vos bases de données. 🚀

Pour aller plus loin :

• Schéma relationnel d’une base de données MySQL
• Liste complète des requêtes MySQL
• La requête JOIN en MySQL
• Types de clé et index MySQL
• Stocker des données JSON dans une base de données MySQL
• Exporter une base de donnés au format CSV

• Se protéger des injections SQL en PHP
• Gestion d’une base de données MySQL en POO PHP

Alban

Fondateur de l’agence Créa-troyes.
Intervenant en Freelance.
Contactez-moi

crea-troyes.fr