TP – MySQL Triggers, Procédures stockées, Fonctions & gestion d’erreurs PHP (BTS SIO SLAM – 4 h)

Objectifs

• Mettre en œuvre un trigger BEFORE INSERT pour contrôler la disponibilité d’un livre.
• Créer une procédure stockée pour gérer le retour d’un livre.
• Créer une fonction SQL pour calculer le nombre d’emprunts actifs d’un adhérent.
• Gérer les erreurs SQL côté PHP via PDO et try/catch (affichage d’un message explicite).

Scénario

Mini-gestion de bibliothèque : on empêche qu’un livre déjà emprunté soit réemprunté tant qu’il n’a pas été rendu. Les emprunts et retours sont tracés. Le code PHP doit remonter proprement les erreurs métier.

Étape 1 — Création de la base et des tables

Exécuter dans MySQL (par ex. via phpMyAdmin ou le client en ligne de commande) :

CREATE DATABASE IF NOT EXISTS biblio_tp
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;
USE biblio_tp;

-- Tables
CREATE TABLE adherent (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(50) NOT NULL,
  prenom VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE livre (
  id INT AUTO_INCREMENT PRIMARY KEY,
  titre VARCHAR(100) NOT NULL,
  disponible BOOLEAN NOT NULL DEFAULT TRUE
) ENGINE=InnoDB;

CREATE TABLE emprunt (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_adherent INT NOT NULL,
  id_livre INT NOT NULL,
  date_emprunt DATE NOT NULL DEFAULT (CURRENT_DATE()),
  date_retour DATE NULL,
  CONSTRAINT fk_emprunt_adherent FOREIGN KEY (id_adherent) REFERENCES adherent(id),
  CONSTRAINT fk_emprunt_livre FOREIGN KEY (id_livre) REFERENCES livre(id)
) ENGINE=InnoDB;

-- Données de test
INSERT INTO adherent (nom, prenom) VALUES
('Durand', 'Alice'),
('Martin', 'Paul');

INSERT INTO livre (titre) VALUES
('Le Meilleur des mondes'),
('1984'),
('Dune');

Exemple  après  l’execution  de  cette requête  SQL : 

Étape 2 — Trigger : empêcher le double emprunt d’un même livre

Ce trigger vérifie la disponibilité dans livre et lève une erreur si le livre est déjà emprunté. S’il est disponible, il le marque comme indisponible au moment de l’insertion.

ATTENTION : Ce trigger ce met sur la table emprunt ! Et c’est un BEFORE INSERT.

DELIMITER //
CREATE TRIGGER verif_disponibilite
BEFORE INSERT ON emprunt
FOR EACH ROW
BEGIN
  DECLARE v_dispo BOOLEAN;

  -- Vérifie l'existence du livre
  IF (SELECT COUNT(*) FROM livre WHERE id = NEW.id_livre) = 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Livre inexistant.';
  END IF;

  -- Récupère la disponibilité
  SELECT disponible INTO v_dispo FROM livre WHERE id = NEW.id_livre;

  IF v_dispo = FALSE THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Le livre est déjà emprunté.';
  ELSE
    UPDATE livre SET disponible = FALSE WHERE id = NEW.id_livre;
  END IF;
END//
DELIMITER ;

Exemple sous MySQLWorkbench

Étape 3 — Procédure stockée : enregistrer le retour d’un livre

La procédure met la date de retour sur l’emprunt en cours et remet le livre disponible. Si aucun emprunt en cours n’est trouvé, elle lève une erreur.

DELIMITER //
CREATE PROCEDURE rendre_livre(IN p_id_livre INT)
BEGIN
  DECLARE v_rows INT DEFAULT 0;

  UPDATE emprunt
  SET date_retour = CURRENT_DATE()
  WHERE id_livre = p_id_livre AND date_retour IS NULL;

  SET v_rows = ROW_COUNT();

  IF v_rows = 0 THEN
    SIGNAL SQLSTATE '46000'
      SET MESSAGE_TEXT = 'Aucun emprunt en cours pour ce livre.';
  ELSE
    UPDATE livre SET disponible = TRUE WHERE id = p_id_livre;
  END IF;
END//
DELIMITER ;

TAF :

• Tester cette procédure en simulant un retour du livre Le meilleur des mondes !
• Vérifié que la date de retour à été mise à jour et que le livre est bien à nouveau disponible.

Étape 4 — Fonction : nombre d’emprunts actifs d’un adhérent

DELIMITER //
CREATE FUNCTION nb_emprunts_actifs(p_id_adherent INT)
RETURNS INT
READS SQL DATA
BEGIN
  DECLARE v_nb INT;
  SELECT COUNT(*) INTO v_nb
  FROM emprunt
  WHERE id_adherent = p_id_adherent
    AND date_retour IS NULL;
  RETURN v_nb;
END//
DELIMITER ;

-- Test
SELECT nb_emprunts_actifs(1) AS nb_actifs_adherent_1;

TAF :

Refaite des emprunts et vérifier que tout se passe normalement.

Étape 5 — PHP (PDO + try/catch) : tester et afficher les erreurs

Créer un fichier test_emprunt.php (adapter l’utilisateur/mot de passe MySQL si besoin). Lancer dans le navigateur ; le script tente un double emprunt pour déclencher l’erreur du trigger, puis appelle la procédure de retour.

Remarque : La classe BiblioDB joue le rôle d’un pont entre le PHP et la base de données : elle illustre la communication avec MySQL, pas encore la modélisation métier, il n’est pas nécessaire de créer des classes Livre, Emprunt, Adherent à ce stade : ce serait une complexification inutile pour une simple démonstration d’accès aux données et de gestion d’erreurs SQL.

<?php
class BiblioDB {
  private PDO $pdo;

  public function __construct() {
    try {
      $this->pdo = new PDO(
        'mysql:host=localhost;dbname=biblio_tp;charset=utf8mb4',
        'root', // <-- utilisateur MySQL
        '',     // <-- mot de passe MySQL
        [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
      );
    } catch (PDOException $e) {
      // Erreur de connexion : on arrête tout de suite
      die('Erreur de connexion : ' . htmlspecialchars($e->getMessage()));
    }
  }

  public function emprunterLivre(int $idAdherent, int $idLivre): void {
    try {
      $stmt = $this->pdo->prepare(
        'INSERT INTO emprunt (id_adherent, id_livre) VALUES (:a, :l)'
      );
      $stmt->execute([':a' => $idAdherent, ':l' => $idLivre]);
      echo '<p style="color:green">Emprunt enregistré (adhérent ' . $idAdherent . ', livre ' . $idLivre . ').</p>';
    } catch (PDOException $e) {
      // Affiche le message levé par SIGNAL (trigger) avec SQLSTATE 45000
      echo '<p style="color:#b00020"><strong>Erreur emprunt</strong> : ' . htmlspecialchars($e->getMessage()) . '</p>';
      // Pour un traitement fin, on peut aussi exploiter $e->getCode() (SQLSTATE)
      // echo '<p>SQLSTATE : ' . htmlspecialchars($e->getCode()) . '</p>';
    }
  }

  public function rendreLivre(int $idLivre): void {
    try {
      $this->pdo->exec('CALL rendre_livre(' . (int)$idLivre . ')');
      echo '<p style="color:green">Livre ' . $idLivre . ' rendu avec succès.</p>';
    } catch (PDOException $e) {
      echo '<p style="color:#b00020"><strong>Erreur retour</strong> : ' . htmlspecialchars($e->getMessage()) . '</p>';
    }
  }

  public function nbEmpruntsActifs(int $idAdherent): void {
    $stmt = $this->pdo->query('SELECT nb_emprunts_actifs(' . (int)$idAdherent . ') AS nb');
    $row = $stmt->fetch();
    echo '<p>Emprunts actifs de l’adhérent ' . $idAdherent . ' : <strong>' . (int)$row['nb'] . '</strong></p>';
  }
}

/* ---------- Démo contrôlée ---------- */
$db = new BiblioDB();
echo '<h3>Démo</h3>';

/* 1) Emprunt du livre 1 par l’adhérent 1 (OK) */
$db->emprunterLivre(1, 1);

/* 2) Tentative de réemprunter le même livre (déclenche le trigger => erreur) */
$db->emprunterLivre(2, 1);

/* 3) Statistiques adhérent 1 */
$db->nbEmpruntsActifs(1);

/* 4) Retour du livre 1 (procédure stockée) */
$db->rendreLivre(1);

/* 5) Ré-emprunt possible après retour */
$db->emprunterLivre(2, 1);
?>

Questions de validation (rapides)

1. Que se passe-t-il si on insère un emprunt pour un livre dont disponible = FALSE ? Quelle est la SQLSTATE remontée ?
2. Que se passe-t-il si on appelle rendre_livre sur un livre qui n’a aucun emprunt en cours ?
3. Expliquez l’intérêt de lever l’erreur côté MySQL (trigger) plutôt que de ne gérer la règle qu’en PHP.

TAF

• Ajouter une contrainte d’âge d’emprunt maximum (ex. 30 jours) et une fonction qui calcule le nombre de jours de retard.
• Enrichir le trigger pour interdire un nouvel emprunt si l’adhérent a déjà N emprunts actifs (utiliser la fonction créée).
• Journaliser les erreurs métier dans une table journal_erreurs (horodatage + message) via un second trigger AFTER INSERT sur emprunt en cas d’échec simulé (à discuter en classe).