Aller au contenu principal

Les déclencheurs

Automatiser des actions lors de modifications dans la base de données

Notions théoriques​

Un déclencheur (ou trigger) est un mécanisme de la base de données qui permet d'exécuter automatiquement une action lorsqu’un événement spécifique se produit sur une table.

Les événements peuvent être :

  • INSERT : lorsqu’une nouvelle ligne est ajoutée
  • UPDATE : lorsqu’une ligne existante est modifiée
  • DELETE : lorsqu’une ligne est supprimée

Un déclencheur est associé à une table et à un moment :

  • BEFORE (avant l’opération)
  • AFTER (après l’opération)

Utilité des déclencheurs​

Les déclencheurs sont utiles pour :

  • Mettre à jour automatiquement des champs (date de modification, utilisateur, etc.)
  • Enregistrer un historique des modifications
  • Vérifier ou corriger des données
  • Maintenir la cohérence entre plusieurs tables

Syntaxe générale​

CREATE TRIGGER nom_du_declencheur
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON nom_de_la_table
FOR EACH ROW
BEGIN
  -- instructions SQL
END;
  • FOR EACH ROW indique que le déclencheur s’exécute pour chaque ligne affectée.
  • Dans le corps du déclencheur, on peut utiliser :
    • NEW.colonne pour accéder à la nouvelle valeur (INSERT ou UPDATE)
    • OLD.colonne pour accéder à l’ancienne valeur (UPDATE ou DELETE)

Signe =​

attention

Dans le code d'un trigger, le signe = est utilisé pour comparer et non pour affecter une valeur.

Exemple​

IF (mdpExisteHisto(NEW.mdpActuel,OLD.id) = false) THEN

Le symbole = est une comparaison et non une affectation.

Explications​

  • En SQL (et donc dans les triggers MySQL, MariaDB, etc.), l'opérateur = est utilisé pour comparer deux valeurs (équivalent à == dans d'autres langages comme C ou Java).
  • Pour l'affectation, on utilise généralement SET variable = valeur; (voir ligne 4 et 18 dans votre code).

Détail​

  • Comparaison : IF (mdpExisteHisto(NEW.mdpActuel,OLD.id) = false) vérifie si la fonction mdpExisteHisto retourne false.
  • Affectation : SET correct = true; affecte la valeur true à la variable correct.

Dans notre exemple (extrait du code d'un trigger) le signe = sert à comparer le résultat de la fonction à false.

Ce n'est pas une affectation, mais bien une comparaison.

Restrictions​

  • Il n’est pas possible d’utiliser COMMIT ou ROLLBACK dans un déclencheur.
  • Un déclencheur ne peut pas appeler une procédure contenant une transaction.
  • Les déclencheurs ne doivent pas modifier la même table sur laquelle ils sont déclenchés (risque de boucle infinie).

Exemple d’utilisation​

  • Journaliser automatiquement les suppressions d’utilisateurs dans une table log_suppressions.
  • Mettre à jour un champ updated_at à chaque modification d’une ligne.

Exemple pratique​

Il est possible de créer un déclencheur qui enregistre automatiquement les suppressions d’utilisateurs dans une table log_suppressions.

Étapes​

  1. Créer deux tables : utilisateurs et log_suppressions
  2. Ajouter quelques utilisateurs
  3. Créer un déclencheur AFTER DELETE sur utilisateurs
  4. Supprimer un utilisateur et observer l’effet du déclencheur

Code SQL​

-- 1. Table utilisateurs
CREATE TABLE utilisateurs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100),
  email VARCHAR(100)
);

-- 2. Table de logs
CREATE TABLE log_suppressions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom_utilisateur VARCHAR(100),
  email_utilisateur VARCHAR(100),
  date_suppression DATETIME
);

-- 3. Insertion de données
INSERT INTO utilisateurs (nom, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');

-- 4. Déclencheur
DELIMITER //
CREATE TRIGGER log_suppression_utilisateur
AFTER DELETE ON utilisateurs
FOR EACH ROW
BEGIN
  INSERT INTO log_suppressions (nom_utilisateur, email_utilisateur, date_suppression)
  VALUES (OLD.nom, OLD.email, NOW());
END;
//
DELIMITER ;

-- 5. Suppression d’un utilisateur
DELETE FROM utilisateurs WHERE nom = 'Alice';

-- 6. Vérification
SELECT * FROM log_suppressions;

Test de mémorisation/compréhension​


Quel événement peut déclencher un déclencheur ?


Quel mot-clé permet d’exécuter un déclencheur avant une modification ?


Quel mot-clé permet d'accéder aux anciennes valeurs dans un déclencheur ?


Quel mot-clé permet d’accéder aux nouvelles valeurs dans un déclencheur ?


Un déclencheur peut-il modifier la même table sur laquelle il est déclenché ?


Combien de fois un déclencheur est-il exécuté pour un `DELETE` affectant 3 lignes ?


Quel mot-clé est interdit dans un déclencheur ?


Quelle clause est obligatoire dans un déclencheur ?


Quel est le bon ordre pour créer un déclencheur ?


Quel est l’effet du déclencheur dans l’exemple pratique ?


TP pour réfléchir et résoudre des problèmes​

Télécharger le fichier suivant :
👉 Télécharger le fichier 'cdi.sql' - Télécharger le MCD

Importer ce fichier dans votre base de données MariaDB pour créer la base bibliotheque et ses tables.

Étape 1 : Créer la base de données​

Importer le fichier cdi.sql dans votre environnement MariaDB.

Une solution

Étape 2 : Créer une table log_emprunts pour enregistrer les nouveaux emprunts​

Créer une table log_emprunts qui enregistrera les emprunts effectués dans la bibliothèque.
Chaque fois qu’un emprunt est ajouté dans la table emprunts, un enregistrement sera ajouté dans log_emprunts.

Structure de la table log_emprunts :

  • idlog : identifiant auto-incrémenté
  • idlivre : identifiant du livre emprunté
  • idemprunteur : identifiant de l’emprunteur
  • date_emprunt : date de l’emprunt (prise depuis datepret)
  • horodatage : date et heure de l’enregistrement dans le log (NOW())
Une solution

Étape 3 : Créer un déclencheur AFTER INSERT sur la table emprunts​

Créer un déclencheur appelé log_nouvel_emprunt qui s’exécute après chaque insertion dans la table emprunts.

Ce déclencheur doit insérer dans la table log_emprunts les informations suivantes :

  • idlivre : NEW.idlivre
  • idemprunteur : NEW.idemprunteur
  • date_emprunt : NEW.datepret
  • horodatage : NOW()
Une solution

Étape 4 : Tester le déclencheur en insérant un nouvel emprunt​

Ajouter un nouvel emprunt dans la table emprunts.
Vérifier ensuite que la table log_emprunts contient bien une ligne correspondante.

Une solution

Étape 5 : Créer une table log_modifications_auteur pour tracer les modifications d’auteurs​

Créer une table qui permettra de conserver une trace des anciennes valeurs lorsqu’un auteur est modifié.

Structure de la table :

  • idlog : identifiant auto-incrémenté
  • idauteur : identifiant de l’auteur modifié
  • ancien_nom : ancienne valeur du nom
  • ancien_prenom : ancienne valeur du prénom
  • horodatage : date et heure de la modification
Une solution

Étape 6 : Créer un déclencheur BEFORE UPDATE sur la table auteurs​

Créer un déclencheur log_modif_auteur qui s’exécute avant chaque modification dans la table auteurs.

Ce déclencheur doit insérer dans log_modifications_auteur :

  • idauteur : OLD.idauteur
  • ancien_nom : OLD.nom
  • ancien_prenom : OLD.prenom
  • horodatage : NOW()
Une solution

Étape 7 : Tester le déclencheur en modifiant un auteur​

Modifier le nom ou le prénom d’un auteur existant.
Vérifier ensuite que la table log_modifications_auteur contient bien une trace de l’ancienne version.

Une solution

Étape 8 : Créer une table log_prix_supprimes pour tracer les livres supprimés​

Créer une table log_prix_supprimes pour stocker les informations des livres supprimés, notamment leur prix.

Structure de la table :

  • idlog : identifiant auto-incrémenté
  • idlivre : identifiant du livre supprimé
  • titre : titre du livre
  • prix : prix du livre
  • horodatage : date et heure de la suppression
Une solution

Étape 9 : Créer un déclencheur AFTER DELETE sur la table livres​

Créer un déclencheur log_suppression_livre qui s’exécute après chaque suppression dans la table livres.

Ce déclencheur doit insérer dans log_prix_supprimes :

  • idlivre : OLD.idlivre
  • titre : OLD.titre
  • prix : OLD.prix
  • horodatage : NOW()
Une solution

Étape 10 : Tester le déclencheur en supprimant un livre​

Supprimer un livre existant dans la table livres.

Vérifier que les informations du livre supprimé ont bien été enregistrées dans log_prix_supprimes :

-- Suppression d’un livre
DELETE FROM livres WHERE idlivre = 2;

-- Vérification du log
SELECT * FROM log_prix_supprimes ORDER BY idlog DESC LIMIT 1;

Résultat attendu : une ligne contenant les informations du livre supprimé (ex. : Le Cid, 6.99 €).