Procédures stockées et triggers (aperçu)

Une procédure stockée, une fonction stockée ou un trigger est un programme SQL enregistré dans la base et exécuté par le serveur MySQL. Ce chapitre montre à quoi ils servent, avec un exemple de chaque sur la base d'exemple parc ; le cours Procédures stockées et triggers enseigne à les écrire.

Procédures, fonctions et triggers

Un exemple de chaque

Une procédure qui clôture un ticket dans une transaction, et annule tout en cas d'erreur :

DELIMITER //
CREATE PROCEDURE cloturer_ticket(
  IN p_id_ticket     INT,
  IN p_id_technicien INT,
  IN p_duree_min     SMALLINT,
  IN p_compte_rendu  VARCHAR(255)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;            -- renvoie l'erreur d'origine à l'appelant
  END;

  START TRANSACTION;
  INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min, compte_rendu)
  VALUES (p_id_ticket, p_id_technicien, NOW(), p_duree_min, p_compte_rendu);
  UPDATE ticket SET statut = 'resolu', date_cloture = NOW()
  WHERE id_ticket = p_id_ticket;
  COMMIT;
END //
DELIMITER ;

CALL cloturer_ticket(6, 12, 25, 'Ventilateur remplacé');

Une fonction qui renvoie le temps passé sur un ticket :

DELIMITER //
CREATE FUNCTION duree_totale_ticket(p_id_ticket INT) RETURNS INT
READS SQL DATA
BEGIN
  DECLARE v_total INT;
  SELECT COALESCE(SUM(duree_min), 0) INTO v_total
  FROM intervention
  WHERE id_ticket = p_id_ticket;
  RETURN v_total;
END //
DELIMITER ;

SELECT id_ticket, titre, duree_totale_ticket(id_ticket) AS minutes
FROM ticket
WHERE id_ticket IN (1, 7, 9);

La mention READS SQL DATA est obligatoire quand le journal binaire est actif (le cas par défaut) : sans elle, ni DETERMINISTIC ni NO SQL, MySQL refuse de créer la fonction (erreur 1418).

Un trigger qui applique une règle que les clés étrangères ne savent pas exprimer : seul un membre du service informatique peut être technicien d'une intervention.

DELIMITER //
CREATE TRIGGER trg_intervention_technicien
BEFORE INSERT ON intervention
FOR EACH ROW
BEGIN
  IF NOT EXISTS (SELECT 1
                 FROM employe e
                 JOIN service s ON s.id_service = e.id_service
                 WHERE e.id_employe = NEW.id_technicien AND s.code = 'INFO') THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Seul un membre du service informatique peut intervenir';
  END IF;
END //
DELIMITER ;

ERROR 1644 (45000): Seul un membre du service informatique peut intervenir

SIGNAL lève une erreur personnalisée ; l'INSERT est annulé. Un trigger BEFORE peut aussi corriger NEW avant l'enregistrement (SET NEW.titre = TRIM(NEW.titre);).

Dans la base ou dans l'application ?

Le compromis courant : les contraintes d'intégrité (clés, CHECK, triggers de contrôle et d'audit) dans la base, qui en est la gardienne ; la logique métier riche dans l'application. Les sujets d'E7 SLAM donnent souvent un trigger à lire, à compléter ou à corriger : sache lire OLD, NEW, BEFORE, AFTER et SIGNAL.

Si le sujet est en SQL Server

Plusieurs sujets récents d'E7 SLAM sont écrits pour SQL Server. Trois différences reviennent.

Les triggers. Un trigger T-SQL s'exécute une fois par instruction, pas une fois par ligne : il n'y a ni FOR EACH ROW, ni OLD, ni NEW. À la place, deux pseudo-tables contiennent toutes les lignes touchées : deleted (les lignes avant la modification) et inserted (après). Pour un UPDATE, on les joint sur la clé. L'audit des changements de statut devient :

-- SQL Server (T-SQL)
CREATE TRIGGER trg_ticket_audit_statut ON ticket
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  INSERT INTO audit_ticket (id_ticket, ancien_statut, nouveau_statut, modifie_par, modifie_le)
  SELECT i.id_ticket, d.statut, i.statut, SUSER_SNAME(), SYSDATETIME()
  FROM inserted i
  JOIN deleted d ON d.id_ticket = i.id_ticket
  WHERE i.statut <> d.statut;
END;
GO

Un UPDATE qui modifie dix tickets déclenche le trigger une seule fois, avec dix lignes dans inserted et deleted : écris toujours le corps pour un ensemble de lignes. Pour refuser une modification, THROW 50001, N'Message', 1; joue le rôle de SIGNAL : l'erreur annule l'instruction. Il n'y a pas de trigger BEFORE ; un trigger INSTEAD OF remplace l'instruction.

Les comptes. SQL Server sépare la connexion au serveur (LOGIN) et l'utilisateur d'une base (USER), sans partie hôte :

-- SQL Server (T-SQL)
CREATE LOGIN app_parc WITH PASSWORD = 'Mot-de-passe-long-et-unique-2026';
GO
USE parc;
GO
CREATE USER app_parc FOR LOGIN app_parc;
GRANT SELECT ON SCHEMA::dbo TO app_parc;           -- toutes les tables du schéma dbo
GRANT INSERT, UPDATE ON dbo.ticket TO app_parc;
CREATE ROLE r_technicien;
ALTER ROLE r_technicien ADD MEMBER app_parc;       -- l'équivalent de GRANT rôle TO compte

Les lots. GO sépare les lots de SSMS et de sqlcmd (chiffrer une colonne avec SQL Server). Un CREATE TRIGGER, CREATE PROCEDURE ou CREATE VIEW doit être la première instruction de son lot, et une colonne créée par ALTER TABLE n'est utilisable que dans le lot suivant.

Exercices

CALL cloturer_ticket(11, 12, 20, 'Certificat renouvelé');

DELIMITER //
CREATE FUNCTION nb_tickets_en_attente(p_id_employe INT) RETURNS INT
READS SQL DATA
BEGIN
  DECLARE v_nb INT;
  SELECT COUNT(*) INTO v_nb
  FROM ticket
  WHERE id_demandeur = p_id_employe AND statut <> 'resolu';
  RETURN v_nb;
END //
DELIMITER ;

SELECT nom, prenom, nb_tickets_en_attente(id_employe) AS en_attente
FROM employe
WHERE nb_tickets_en_attente(id_employe) > 0
ORDER BY nom;

DELIMITER //
CREATE TRIGGER trg_ticket_cloture
BEFORE UPDATE ON ticket
FOR EACH ROW
BEGIN
  IF NEW.statut = 'resolu' AND OLD.statut <> 'resolu' THEN
    SET NEW.date_cloture = NOW();
  END IF;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER trg_ticket_cloture
BEFORE UPDATE ON ticket
FOR EACH ROW
BEGIN
  IF NEW.statut = 'resolu' AND OLD.statut <> 'resolu' THEN
    SET NEW.date_cloture = NOW();
  ELSEIF NEW.statut <> 'resolu' THEN
    SET NEW.date_cloture = NULL;
  END IF;
END //
DELIMITER ;