Cours MariaDB / Procédures, fonctions et déclencheurs : rôles, droits et transactions
Une procédure stockée exécute une opération explicite sur le serveur avec CALL, une fonction stockée renvoie une valeur dans une expression SQL, et un déclencheur, ou trigger, réagit automatiquement à une modification de table. Ces trois objets stockent du SQL sur le serveur, mais leur mode d’appel, leurs droits et leur rôle dans les transactions diffèrent.
Choisir l’objet selon son rôle
| Objet | Déclenchement | Rôle courant |
|---|---|---|
| Procédure stockée | CALL nom(arguments) | Opération explicite réunissant plusieurs instructions SQL |
| Fonction stockée | nom(arguments) dans une expression SQL | Règle réutilisable renvoyant une valeur calculée |
| Déclencheur | Automatiquement sur INSERT, UPDATE ou DELETE | Action courte exécutée pour chaque ligne concernée par un changement de table |
Une procédure n’est pas obligée de modifier des données et une fonction stockée n’est pas, par définition, limitée à l’arithmétique. Pour rendre la comparaison concrète, cet exercice utilise une procédure qui modifie le stock, une fonction qui calcule un montant et un déclencheur qui enregistre l’ancien et le nouveau stock. Un changement portant sur plusieurs lignes entraîne le travail du déclencheur pour chacune d’elles.
Distinguez une opération que l’application appelle volontairement d’une logique exécutée à chaque écriture correspondante. Les comportements implicites peuvent compliquer le diagnostic. Avant de déplacer une règle vers la base, déterminez si elle relève de la validation applicative ou si plusieurs chemins d’écriture doivent impérativement l’appliquer. Le contexte d’exécution des vues SQL réutilisables constitue un autre point de comparaison.
Préparer les droits et deux tables InnoDB
Utilisez uniquement le schéma d’apprentissage appops_lab et un compte disposant de CREATE ROUTINE, de TRIGGER et des droits nécessaires sur les tables. L’appel des routines demande également EXECUTE. Le guide des comptes et des autorisations GRANT explique comment limiter ce périmètre sans donner les droits d’un administrateur global à une application.
La politique de journalisation binaire peut imposer des conditions supplémentaires pour créer une fonction. Faites vérifier cette politique par l’administrateur de la base au lieu d’assouplir les paramètres de confiance du serveur pour faire passer l’exercice. Si appops_lab existe déjà, confirmez qu’il s’agit bien de votre environnement d’apprentissage.
CREATE DATABASE IF NOT EXISTS appops_lab
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE routine_stock (
product_id INT NOT NULL PRIMARY KEY,
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB;
CREATE TABLE routine_audit (
audit_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
old_stock INT NOT NULL,
new_stock INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO routine_stock VALUES (1, 8);
Les deux tables utilisent le moteur transactionnel InnoDB : la modification du stock et l’insertion effectuée par le déclencheur peuvent ainsi être annulées ensemble. Si une table ou une routine de cet exercice existe déjà, examinez sa définition et ses données avant de répéter sa création. Les commandes de préparation ne suppriment volontairement aucun objet existant.
Créer les trois objets avec leurs définitions complètes
Le script suivant s’exécute dans le client en ligne de commande mariadb, connecté à appops_lab. DELIMITER règle la manière dont ce client reconnaît la fin d’une instruction ; ce n’est pas une instruction SQL du serveur. Dans un éditeur graphique, utilisez son mode d’exécution de script compatible. Avec un pilote Python, envoyez chaque instruction CREATE complète en un seul appel, sans les lignes DELIMITER ni les terminateurs //.
DELIMITER //
CREATE FUNCTION line_total(p_price DECIMAL(10,2), p_qty INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL SQL SECURITY INVOKER
BEGIN
IF p_price IS NULL OR p_qty IS NULL OR p_price < 0 OR p_qty < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonnegative price and quantity required';
END IF;
RETURN ROUND(p_price * p_qty, 2);
END//
CREATE PROCEDURE adjust_stock(IN p_product_id INT, IN p_delta INT)
SQL SECURITY INVOKER
MODIFIES SQL DATA
BEGIN
IF p_delta IS NULL OR p_delta = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonzero stock delta required';
END IF;
UPDATE routine_stock SET stock = stock + p_delta
WHERE product_id = p_product_id AND stock + p_delta >= 0;
IF ROW_COUNT() <> 1 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Missing product or insufficient stock';
END IF;
END//
CREATE TRIGGER audit_stock_update
AFTER UPDATE ON routine_stock FOR EACH ROW
BEGIN
IF OLD.stock <> NEW.stock THEN
INSERT INTO routine_audit (product_id, old_stock, new_stock)
VALUES (NEW.product_id, OLD.stock, NEW.stock);
END IF;
END//
DELIMITER ;
line_total refuse un prix ou une quantité négatifs ou NULL et renvoie un montant à deux décimales. Une quantité zéro est admise par cette fonction et donne un montant nul ; elle n’obéit pas aux mêmes conditions que le paramètre de variation du stock. Recalculez la plage des types DECIMAL pour des prix ou quantités importants. Les caractéristiques DETERMINISTIC NO SQL décrivent cette implémentation et doivent rester exactes si son code change. La référence CREATE FUNCTION décrit ces caractéristiques et le contexte de sécurité.
adjust_stock modifie exactement un produit existant si son stock final reste positif ou nul. Sinon, elle signale une erreur métier avec SIGNAL. La variation ne doit être ni NULL ni égale à zéro. ROW_COUNT() est lu immédiatement après l’UPDATE, car une autre instruction pourrait changer l’opération dont il rapporte le résultat. La référence CREATE PROCEDURE précise les paramètres et la sécurité des appels.
Le déclencheur insère une ligne dans routine_audit uniquement lorsque le stock change réellement. OLD et NEW désignent les valeurs de la ligne avant et après la modification. Cet enregistrement est un exercice de suivi des changements, pas un journal indépendant protégé contre toute altération. La référence CREATE TRIGGER explique son moment d’exécution et les valeurs de ligne disponibles.
Comparer les deux tables après ROLLBACK puis COMMIT
Dans la même connexion et avec les données initiales intactes, exécutez :
SELECT line_total(12.50, 3) AS amount;
START TRANSACTION;
CALL adjust_stock(1, -3);
SELECT product_id, stock FROM routine_stock;
SELECT product_id, old_stock, new_stock FROM routine_audit;
ROLLBACK;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT COUNT(*) AS audit_rows FROM routine_audit;
Le montant calculé vaut 37.50. Pendant la transaction, le stock passe de 8 à 5 et le déclencheur enregistre ce changement dans la table de suivi. Après ROLLBACK, le stock doit être revenu à 8 et le nombre de lignes de suivi à zéro. Une valeur AUTO_INCREMENT attribuée avant l’annulation n’est pas nécessairement réutilisée : un trou dans les identifiants du suivi ne prouve pas un échec du rollback.
Comparez maintenant avec une validation explicite :
START TRANSACTION;
CALL adjust_stock(1, -2);
COMMIT;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT old_stock, new_stock FROM routine_audit ORDER BY audit_id;
Après ce COMMIT, le stock vaut 6 et une seule ligne de suivi décrit le passage de 8 à 6. La procédure ne contient volontairement aucun COMMIT : l’appelant peut intégrer son action dans une transaction plus large. Le déclencheur s’exécute dans le contexte transactionnel de l’instruction qui l’active et ne valide pas indépendamment ses changements. Le guide des transactions InnoDB, COMMIT et ROLLBACK détaille ces frontières.
Observer les entrées invalides et les limites de droits
Exécutez séparément les deux instructions suivantes. La première tente de soustraire plus que le stock de 6 disponible ; la seconde fournit un prix négatif. Chacune doit produire l’erreur métier SQLSTATE 45000.
CALL adjust_stock(1, -99);
SELECT line_total(-1, 2);
Vérifiez ensuite que le stock vaut toujours 6 et que la table de suivi contient toujours sa seule ligne validée. Si vous intégrez ces appels à une transaction comprenant d’autres modifications, l’application doit traiter l’erreur et décider d’annuler toute l’opération : le signal d’erreur ne remplace pas cette gestion transactionnelle.
La fonction et la procédure déclarent SQL SECURITY INVOKER : l’accès aux tables dépend des droits de leur appelant. Un compte possédant EXECUTE, mais pas UPDATE, ne peut donc pas modifier le stock par cette procédure. Le déclencheur utilise en revanche le contexte de son DEFINER ; il n’est pas déclaré INVOKER. Gérez le compte définisseur, sa durée de vie et ses droits sur les tables en conséquence.
Les limites des déclencheurs MariaDB encadrent notamment les opérations transactionnelles et les modifications de la table qui a provoqué leur exécution. Gardez un travail court et directement lié à la transaction de base de données plutôt que d’y cacher des opérations externes longues.
Examiner les définitions et supprimer seulement les objets d’exercice
SHOW CREATE PROCEDURE adjust_stock;
SHOW CREATE FUNCTION line_total;
SHOW TRIGGERS FROM appops_lab;
Consignez les définitions et les contextes de sécurité. Vérifiez que votre sauvegarde et votre restauration incluent les routines et les déclencheurs requis : leur présence ne doit pas être supposée sans examen du périmètre de l’outil. Une définition restaurée sur un autre serveur peut échouer si son compte DEFINER manque ou n’a pas les autorisations nécessaires.
Lorsque cet exercice isolé est terminé, confirmez que ces noms désignent bien ses objets devenus inutiles avant d’exécuter le nettoyage suivant :
DROP TRIGGER appops_lab.audit_stock_update;
DROP PROCEDURE appops_lab.adjust_stock;
DROP FUNCTION appops_lab.line_total;
DROP TABLE appops_lab.routine_audit;
DROP TABLE appops_lab.routine_stock;
Un ROLLBACK ordinaire ne récupère pas les objets supprimés. Ne supprimez pas le schéma complet s’il contient d’autres exercices. Modifier des routines en production demande aussi de coordonner les programmes appelants et de prévoir un processus de déploiement et de récupération.




