Procédure stockée, fonction, trigger : c'est quoi ?

Une procédure stockée est un programme SQL enregistré dans la base de données sous un nom, que le serveur exécute quand on l'appelle avec CALL. Avec la fonction stockée, le trigger (en français, déclencheur) et l'événement planifié, elle forme la famille des programmes stockés : du code qui vit dans le SGBD, à côté des données, au lieu de vivre dans l'application. Ce chapitre les présente, explique quand les utiliser et quand s'en passer, puis installe ton environnement.

Les quatre programmes stockés

Tous s'écrivent dans le même langage : le SQL que tu connais, enrichi de variables, de conditions, de boucles et de gestion d'erreurs. Ce qui les distingue, c'est qui les déclenche.

Les procédures et les fonctions forment les routines (c'est le mot de la documentation MySQL et de la table information_schema.routines). Un trigger n'est jamais appelé : il réagit. Un événement n'est jamais appelé non plus : il se réveille à l'heure dite.

Un exemple de chaque

Pour fixer les idées, voici à quoi ressemble chaque objet sur la base parc. Tu apprendras à les écrire dans les chapitres suivants ; pour l'instant, lis-les sans les exécuter : ces objets n'existent pas encore, et l'INSERT ajouterait à ta base une intervention qu'aucun contrôle n'aurait refusée.

-- Une procédure : appelée explicitement, elle peut enchaîner plusieurs requêtes
CALL charge_technicien(11, @nb, @minutes);   -- Chloé Lambert : 6 interventions, 295 minutes

-- Une fonction : elle s'utilise dans une requête, comme UPPER() ou NOW()
SELECT titre, duree_totale_ticket(id_ticket) AS minutes FROM ticket WHERE id_ticket = 9;

-- Un trigger : rien à appeler, il s'exécute à chaque INSERT dans intervention
INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min)
VALUES (12, 9, '2026-10-22 10:00:00', 15);
-- ERROR 1644 (45000): Seul un membre du service informatique peut intervenir

-- Un événement : le serveur l'exécute seul, ici chaque nuit à 3 heures
CREATE EVENT ev_purge_audit
  ON SCHEDULE EVERY 1 DAY STARTS '2026-11-09 03:00:00'
  DO DELETE FROM audit_ticket WHERE modifie_le < NOW() - INTERVAL 1 YEAR;

Pourquoi mettre du code dans la base

Sans programme stocké, toute la logique vit dans l'application : le code PHP construit ses requêtes, les envoie une par une et décide de la suite. C'est souvent le bon choix. Mais quatre situations justifient de placer du code dans le SGBD.

Une règle qui doit s'appliquer à tous les accès. À Sorbier Logistique, la base parc est modifiée par l'application d'assistance, par les techniciens dans un client SQL, et chaque trimestre par le script d'import de l'outil d'inventaire. Une règle écrite dans l'application (« un logiciel ne s'installe pas au-delà de ses licences ») ne protège que l'application. Écrite dans un trigger, elle s'applique à tout INSERT, d'où qu'il vienne. C'est l'argument attendu quand un sujet d'E7 demande de « justifier le choix d'un déclencheur ».

Un traitement en plusieurs étapes, atomique. Clôturer un ticket, c'est ajouter l'intervention et changer le statut. Dans une procédure, les deux requêtes forment une seule transaction (chapitre 7) : l'application fait un seul appel et ne peut pas oublier une étape.

Des droits plus fins que la table. Une application qui n'a que le droit EXECUTE sur cloturer_ticket peut clôturer un ticket de la manière prévue, et rien d'autre : pas de UPDATE ticket libre, pas de DELETE (chapitre 11). Si elle est compromise, les dégâts sont limités.

Moins d'allers-retours. Un traitement qui lit puis écrit des milliers de lignes s'exécute près des données, au lieu de les faire transiter sur le réseau.

Quand ne pas le faire

Le code stocké a aussi des inconvénients réels, que les équipes de développement connaissent bien :

• Il est invisible. Un développeur qui lit le code PHP ne voit pas qu'un trigger modifie trois autres tables à chaque INSERT. Un comportement « magique » de la base fait perdre des heures de débogage.
• Il se teste et se débogue moins bien. Pas de débogueur pas à pas dans MySQL, peu d'outils de test : il faut écrire ses propres scripts (chapitre 13).
• Il se versionne mal si on n'y prend pas garde. Une procédure modifiée directement sur le serveur n'est dans aucun dépôt Git. La parade : des scripts de migration versionnés.
• Il dépend du SGBD. Le SQL procédural de MySQL, celui de PostgreSQL (PL/pgSQL) et celui de SQL Server (T-SQL) diffèrent beaucoup (chapitre 14). Changer de SGBD oblige à tout réécrire.
• Il charge le serveur de base de données, la ressource la plus difficile à multiplier. Les serveurs d'application, eux, se dupliquent facilement.

Choisir le bon outil

Avant d'écrire du code stocké, pose-toi ces questions dans l'ordre :

1. Une contrainte déclarative suffit-elle ? Une clé étrangère, NOT NULL, UNIQUE ou CHECK (cours SQL) est plus simple, plus rapide et plus lisible qu'un trigger. CHECK (duree_min > 0) n'a pas besoin de trigger.
2. La règle doit-elle s'appliquer à toute modification d'une table, quelle que soit son origine ? Si oui, et qu'elle porte sur d'autres lignes ou d'autres tables (une contrainte CHECK ne voit que la ligne en cours), c'est un trigger.
3. Est-ce un traitement en plusieurs étapes, appelé par une ou plusieurs applications ? C'est une procédure, avec sa transaction.
4. Est-ce un calcul qui renvoie une valeur, à réutiliser dans des requêtes ? C'est une fonction, ou parfois une simple vue.
5. Est-ce une tâche périodique interne à la base ? C'est un événement ; si elle touche aussi des fichiers ou d'autres serveurs (une sauvegarde), c'est une tâche cron.
6. Sinon, la logique va dans l'application.

Où ça tombe au BTS SIO

Le référentiel. Le bloc 2 de l'option SLAM contient la compétence « Développer des fonctionnalités applicatives au sein d'un système de gestion de base de données (relationnel ou non) ». L'annexe II.E, qui décrit l'environnement de tes réalisations, demande un SGBD avec un langage de programmation associé. Concrètement : la commission de l'E6 s'attend à trouver dans tes réalisations des traitements programmés dans la base (une procédure stockée, une fonction, un trigger) que tu sais expliquer ligne par ligne, et l'expression des besoins de l'heure de réalisation peut te demander d'en ajouter ou d'en modifier un (épreuve E6 SLAM).

L'écrit E7 SLAM (Cybersécurité des services informatiques, 4 heures) a donné un trigger à lire, à compléter ou à corriger, sous l'angle de la sécurité, dans six des huit sujets SLAM de 2022 à 2026 étudiés dans le cours Épreuve E7 SLAM ; les sujets GSIC (2024) et NDV (2026) s'en tenaient aux droits, aux vues et au chiffrement. Les sujets 2022 à 2024 portaient le code U6, celui de l'épreuve avant la renumérotation de 2025 :

Les sujets sont presque tous écrits pour MySQL ; celui de 2024 (GSIC) utilisait SQL Server pour chiffrer une colonne, d'où l'intérêt de savoir lire le T-SQL. Le cours Épreuve E7 SLAM classe ces questions et le corrigé du sujet Casterman déroule le trigger bogué. Le chapitre 15 de ce cours t'entraîne sur des cas du même type.

Installer ton environnement

Il te faut un serveur MySQL 8.4, la base parc du cours SQL et les tables complémentaires de ce cours. Si tu as déjà suivi l'installation du cours SQL, garde ton serveur ; sinon, le plus simple est Docker. Télécharge d'abord les deux scripts : parc.sql (la base du cours SQL) et parc-complements.sql (ce cours). Lance ensuite les commandes depuis le dossier où tu les as enregistrés :

# 1. Un serveur MySQL 8.4 dans un conteneur, joignable sur le port 3306 de ta machine
docker run --name mysql-cours -e MYSQL_ROOT_PASSWORD=change-moi -p 3306:3306 -d mysql:8.4

# 2. La base parc (cours SQL), puis les compléments de ce cours, dans cet ordre
docker exec -i mysql-cours sh -c 'exec mysql --default-character-set=utf8mb4 -uroot -p"$MYSQL_ROOT_PASSWORD"' < parc.sql
docker exec -i mysql-cours sh -c 'exec mysql --default-character-set=utf8mb4 -uroot -p"$MYSQL_ROOT_PASSWORD"' < parc-complements.sql

# 3. Ouvrir le client sur la base parc
docker exec -it mysql-cours mysql --default-character-set=utf8mb4 -u root -p parc

Après le docker run, attends que docker logs mysql-cours affiche la ligne « ready for connections » qui mentionne port: 3306 (une trentaine de secondes au premier démarrage) avant d'importer les scripts. Une ligne « ready for connections » avec port: 0 vient du serveur temporaire qui initialise le conteneur : importer à ce moment-là échoue ou s'interrompt.

Le script complémentaire ne touche pas aux sept tables du cours SQL. Il ajoute sept tables :

-- Compléments de la base « parc » pour le cours Procédures stockées et triggers
-- À exécuter après parc.sql (cours SQL), sur MySQL 8.4 LTS ou MariaDB 10.11 et plus.
-- Rejouable : les tables du cours sont supprimées puis recréées, la base parc n'est pas touchée.
USE parc;

DROP TABLE IF EXISTS audit_ticket, historique_affectation, historique_mdp,
                     journal_securite, journal_erreur, stat_mensuelle, compte;

-- Changements de statut des tickets (trigger d'audit, chapitre 9)
CREATE TABLE audit_ticket (
  id_audit       INT AUTO_INCREMENT PRIMARY KEY,
  id_ticket      INT          NOT NULL,          -- volontairement sans clé étrangère
  ancien_statut  VARCHAR(10)  NOT NULL,
  nouveau_statut VARCHAR(10)  NOT NULL,
  modifie_par    VARCHAR(300) NOT NULL,
  modifie_le     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Qui a eu quel poste, et quand (trigger d'historique, chapitre 9)
CREATE TABLE historique_affectation (
  id_historique  INT AUTO_INCREMENT PRIMARY KEY,
  id_poste       INT          NOT NULL,
  ancien_employe INT,                            -- NULL : le poste sortait du stock
  nouvel_employe INT,                            -- NULL : le poste retourne en stock
  modifie_par    VARCHAR(300) NOT NULL,
  modifie_le     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Comptes de l'application d'assistance (sécurité, chapitres 9 et 11)
CREATE TABLE compte (
  id_compte    INT AUTO_INCREMENT PRIMARY KEY,
  login        VARCHAR(30)  NOT NULL UNIQUE,
  mot_de_passe VARCHAR(255) NOT NULL,            -- empreinte bcrypt calculée par l'application
  role         VARCHAR(12)  NOT NULL DEFAULT 'demandeur',
  actif        BOOLEAN      NOT NULL DEFAULT TRUE,
  id_employe   INT          NOT NULL UNIQUE,
  CONSTRAINT ck_compte_role CHECK (role IN ('demandeur', 'technicien', 'responsable', 'admin')),
  CONSTRAINT fk_compte_employe FOREIGN KEY (id_employe) REFERENCES employe (id_employe)
);

-- Anciennes empreintes de mot de passe (chapitre 9)
CREATE TABLE historique_mdp (
  id_compte    INT          NOT NULL,
  mot_de_passe VARCHAR(255) NOT NULL,
  remplace_le  DATETIME(6)  NOT NULL,
  PRIMARY KEY (id_compte, remplace_le)
);

-- Journal de sécurité : changements de rôle, comptes désactivés (chapitre 9)
CREATE TABLE journal_securite (
  id_journal     INT AUTO_INCREMENT PRIMARY KEY,
  evenement      VARCHAR(30)  NOT NULL,
  id_compte      INT          NOT NULL,
  detail         VARCHAR(255),
  auteur         VARCHAR(300) NOT NULL,
  date_evenement DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Erreurs interceptées par les procédures (chapitre 6)
CREATE TABLE journal_erreur (
  id_erreur   INT AUTO_INCREMENT PRIMARY KEY,
  routine     VARCHAR(64)  NOT NULL,
  etat_sql    CHAR(5),
  num_erreur  INT,
  message     VARCHAR(512),
  survenue_le DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indicateurs calculés chaque mois par un événement planifié (chapitre 10)
CREATE TABLE stat_mensuelle (
  mois            CHAR(7)  PRIMARY KEY,          -- '2026-10'
  nb_ouverts      INT      NOT NULL,
  nb_resolus      INT      NOT NULL,
  minutes_passees INT      NOT NULL,
  calcule_le      DATETIME NOT NULL
);

INSERT INTO compte (id_compte, login, mot_de_passe, role, actif, id_employe) VALUES
  (1, 'hgarnier', '$2y$12$9JcYgqcPOCggPHLugkJVm.KOwQqkno2yb13bvHO2a1ueaEDrSdR7C', 'responsable', TRUE, 1),
  (2, 'kmorel',   '$2y$12$uP.TRXU1iyrKT.l/ZTqfZ.8dWMo4poPSAzmgY02oSL0v73JxUX6TG', 'demandeur',   TRUE, 2),
  (3, 'sbenali',  '$2y$12$bQixHBhL5XqwmKy/69cJpuTapc6q79TnPQwgmryUkGH03kLQG7NQG', 'demandeur',   TRUE, 4),
  (4, 'amercier', '$2y$12$WYjIpEw6M6uPEF5URBFuxeVAdpfcrXaDxUkmGUmeOHC/fepXT/3W2', 'admin',       TRUE, 10),
  (5, 'clambert', '$2y$12$qvmYk70i4D0UsCrEsIBiueLUb66t5IUzyacqK3hpnA7SAu2ufo5gW', 'technicien',  TRUE, 11),
  (6, 'mdiallo',  '$2y$12$hj7yByoX6UPy8EZ0RUq9geu.JCctbEH.BcXXS1v97nTs7Of3MpX9K', 'technicien',  TRUE, 12);

Six salariés ont un compte dans l'application : Hélène Garnier (responsable), Karim Morel et Sofiane Benali (demandeurs), Antoine Mercier (administrateur), Chloé Lambert et Moussa Diallo (techniciens). Les mots de passe sont stockés sous forme d'empreintes bcrypt, calculées par l'application (stocker un mot de passe) : la base ne connaît jamais le mot de passe lui-même.

Le client. Dans le client mysql en ligne de commande, tu auras besoin de la commande DELIMITER (chapitre 2). Dans phpMyAdmin, l'onglet SQL comprend DELIMITER et propose aussi un champ « Délimiteur » sous la zone de saisie. Dans DBeaver, exécute tes scripts avec « Exécuter le script » (Alt+X), pas avec le bouton qui exécute une seule instruction. MySQL Workbench comprend DELIMITER dans ses onglets de requêtes.

Exercices

CREATE TRIGGER trg_poste_affectation AFTER UPDATE ON poste FOR EACH ROW …
CREATE FUNCTION anciennete_ans(p_date DATE) RETURNS INT …
CREATE EVENT ev_stat_mensuelle ON SCHEDULE EVERY 1 MONTH …
CREATE PROCEDURE installer_logiciel(IN p_id_poste INT, IN p_id_logiciel INT) …