TP N°5 à la découverte des triggers - Modélisation Merise

Ce document pédagogique, conçu pour les étudiants en filières informatiques et notamment ceux préparant un BTS SIO (option SLAM), constitue le Travail Pratique numéro 5. Il est dédié à l'approfondissement des concepts de conception de bases de données relationnelles à travers l'exploration et la manipulation des triggers SQL, un mécanisme essentiel pour l'intégrité et l'automatisation des données.

Il couvre les notions suivantes :

• La modélisation de données relationnelles et la création de schémas de bases de données.
• La rédaction et l'implémentation de triggers SQL pour la validation de données et la gestion des contraintes d'intégrité.
• L'automatisation des mises à jour complexes dans les bases de données.
• L'application de règles métier via des triggers pour assurer la cohérence des informations.

Modélisation Merise : TP N°5 à la découverte des triggers

Télécharger PDF

BTS SIO SLAM3 - Conception de Bases de Données : TP N°5 - SQL et Triggers

Ce document est un support de Travaux Pratiques (TP) du BTS SIO, spécialité SLAM3, axé sur la conception de bases de données. Il couvre la découverte des triggers SQL et intègre des révisions des concepts MERISE.

Les triggers sont des procédures stockées qui s'exécutent automatiquement en réponse à certains événements sur une table (INSERT, UPDATE, DELETE). Ils sont essentiels pour maintenir l'intégrité et la cohérence des données dans une base de données relationnelle.

Exercice 1 : Gestion d'un Parc Immobilier

Sujet : Modèle de Données et Triggers

Considérons le modèle de données suivant pour la gestion d'un parc immobilier :

• Immeuble(id, adrNum, adrVoie, adrCodePostal, adrVille, fibreOptique, parkingPrivatif)
  • Clé primaire : id
• Appartement(#immeuble, num, description, loyer, superficie, terrasse, classeConso, chauffage, placeParking, prixParking)
  • Clé primaire : (immeuble, num)
  • Clé étrangère : immeuble en référence à Immeuble(id)
• Piece(#(immeuble, appartement), num, superficie, fonction)
  • Clé primaire : (immeuble, appartement, num)
  • Clés étrangères : (immeuble, appartement) en référence à Appartement(immeuble, num)
• Photo(num, titre, description, uri, #(immeuble, appartement))
  • Clé primaire : num
  • Clé étrangère : (immeuble, appartement) en référence à Appartement(immeuble, num)

Script de Création des Tables MySQL

Voici le script SQL permettant de créer les tables décrites ci-dessus dans une base de données MySQL :

CREATE TABLE Immeuble(
  Id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  adrNum VARCHAR(7) NOT NULL,
  adrVoie VARCHAR(100) NOT NULL,
  adrCodePostal VARCHAR(5) NOT NULL,
  adrVille VARCHAR(30) NOT NULL,
  fibreOptique TINYINT NOT NULL,
  parkingPrivatif TINYINT NOT NULL
);

CREATE TABLE Appartement(
  immeuble INT(11),
  num INT(3) NOT NULL,
  description LONGTEXT,
  loyer DOUBLE NOT NULL,
  superficie DOUBLE NOT NULL,
  terrasse TINYINT(1) NOT NULL,
  classeConso CHAR(1) NOT NULL,
  chauffage CHAR(1) NOT NULL,
  placeParking TINYINT(1) NOT NULL,
  prixParking DOUBLE,
  CONSTRAINT pk_appartement PRIMARY KEY (immeuble, num),
  CONSTRAINT fk_immeuble FOREIGN KEY (immeuble) REFERENCES Immeuble(id)
);

CREATE TABLE Photo(
  immeuble INT(11),
  appartement INT(3),
  reference INT(11) NOT NULL,
  titre VARCHAR(75),
  description VARCHAR(255),
  uri VARCHAR(120) NOT NULL,
  CONSTRAINT pk_photo PRIMARY KEY (immeuble, appartement, reference),
  CONSTRAINT fk_appartement_photo FOREIGN KEY (immeuble, appartement) REFERENCES Appartement(immeuble, num)
);

CREATE TABLE Piece(
  immeuble INT(11),
  appartement INT(3),
  num INT(2) NOT NULL,
  superficie DOUBLE,
  fonction VARCHAR(30),
  CONSTRAINT pk_piece PRIMARY KEY (immeuble, appartement, num),
  CONSTRAINT fk_appartement_piece FOREIGN KEY (immeuble, appartement) REFERENCES Appartement(immeuble, num)
);
    

Il est recommandé de créer ces tables dans votre environnement MySQL avant de poursuivre les exercices.

Question 1 : Gestion du Prix de Parking dans les Appartements

Rédigez le trigger permettant de vérifier la contrainte suivante : le prix de la place de parking d'un appartement (prixParking) peut et doit être NULL si l'appartement ne possède pas de place de parking (placeParking = 0). Testez le bon fonctionnement de votre trigger.

Solution 1.1 : Trigger de mise à jour automatique des valeurs

Ce trigger s'exécute avant l'insertion d'un appartement et ajuste la valeur de prixParking si aucune place de parking n'est spécifiée.

DROP TRIGGER IF EXISTS check_appartment_before_insert;
DELIMITER $$
CREATE TRIGGER check_appartment_before_insert BEFORE INSERT ON appartement FOR EACH ROW
BEGIN
    IF NEW.placeParking = 0 THEN
        SET NEW.prixParking := NULL;
    END IF;
END $$
DELIMITER ;
    

Note : Pour une gestion complète de la contrainte, un trigger similaire doit être ajouté pour les mises à jour (BEFORE UPDATE) afin d'assurer la cohérence des données si la propriété placeParking est modifiée après l'insertion.

Solution 1.2 : Trigger avec interruption de transaction (méthode plus stricte)

Cette approche, plus classique pour les contraintes d'intégrité, empêche l'insertion ou la modification si la règle n'est pas respectée, en signalant une erreur SQL.

DELIMITER $$
CREATE TRIGGER check_appartment_before_insert_strict BEFORE INSERT ON appartement FOR EACH ROW
BEGIN
    IF NEW.placeParking = 0 AND NEW.prixParking IS NOT NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Violation de contrainte : un appartement sans place de parking ne doit pas avoir de prix de parking.';
    END IF;
END $$
DELIMITER ;
    

Pour des tests complets, ajoutez un trigger BEFORE UPDATE avec une logique similaire.

Tests Unitaires

Pour vérifier le comportement des triggers :

-- Insertion d'un immeuble (nécessaire pour la contrainte de clé étrangère)
INSERT INTO immeuble
(Id, adrNum, adrVoie, adrCodePostal, adrVille, fibreOptique, parkingPrivatif)
VALUES
(1, '7', 'Place de l\'étoile', '45000', 'ORLEANS', 1, 1);

-- Insertion de deux appartements : le premier avec placeParking=0 et un prixParking spécifié, le second conforme.
-- Avec le trigger Solution 1.1, la première insertion réussira et prixParking sera mis à NULL.
-- Avec le trigger Solution 1.2, la première insertion échouera.
INSERT INTO appartement
(immeuble, num, description, loyer, superficie, terrasse, classeConso, chauffage, placeParking, prixParking)
VALUES
(1, 1, 'Appartement 1.1', 825.00, 86, 1, 'C', 'E', 0, 75.00),
(1, 2, 'Appartement 1.2', 750.00, 86, 1, 'C', 'E', 1, 80.00);
    

Question 2 : Mise à Jour Automatique de la Superficie Totale de l'Appartement

La superficie totale d'un appartement doit être égale à la somme de la superficie de chacune de ses pièces. Créez le trigger qui permet de mettre à jour la superficie d'un appartement à l'insertion d'une pièce.

Solution 2.1 : Version longue (recalcule la somme)

DELIMITER $$
CREATE TRIGGER maj_superficie_appartement_after_insert_piece AFTER INSERT ON piece FOR EACH ROW
BEGIN
    DECLARE superficieTotale DOUBLE;
    -- Calcule la superficie totale des pièces de l'appartement concerné
    SELECT SUM(superficie) INTO superficieTotale
    FROM piece
    WHERE immeuble = NEW.immeuble
    AND appartement = NEW.appartement;

    -- Met à jour la superficie de l'appartement
    UPDATE appartement
    SET superficie = superficieTotale
    WHERE immeuble = NEW.immeuble
    AND num = NEW.appartement;
END $$
DELIMITER ;
    

Solution 2.2 : Version avec variable de session

DELIMITER $$
CREATE TRIGGER maj_superficie_appartement_after_insert_piece_var AFTER INSERT ON piece FOR EACH ROW
BEGIN
    SELECT SUM(superficie) INTO @superficieTotale
    FROM piece
    WHERE immeuble = NEW.immeuble
    AND appartement = NEW.appartement;

    UPDATE appartement
    SET superficie = @superficieTotale
    WHERE immeuble = NEW.immeuble
    AND num = NEW.appartement;
END $$
DELIMITER ;
    

Solution 2.3 : Version courte et plus performante (incrémentielle)

Cette version est généralement préférée car elle effectue une seule opération d'addition, ce qui est plus efficace pour de grandes quantités de données et réduit la charge sur le serveur.

DELIMITER $$
CREATE TRIGGER maj_superficie_appartement_after_insert_piece_inc AFTER INSERT ON piece FOR EACH ROW
BEGIN
    -- Incrémente la superficie de l'appartement avec celle de la nouvelle pièce
    UPDATE appartement
    SET superficie = superficie + NEW.superficie
    WHERE immeuble = NEW.immeuble
    AND num = NEW.appartement;
END $$
DELIMITER ;
    

Test unitaire : Insérez une pièce avec une superficie X pour un appartement existant et vérifiez que la superficie de cet appartement a bien augmenté de X.

Question 3 : Adaptation du Trigger d'Appartement

Adaptez le trigger de la question 1 afin :

1. De vérifier la contrainte suivante : un appartement ne peut avoir de place de parking si l'immeuble n'a pas de parking privatif.
2. D'initialiser la superficie de l'appartement à 0 à l'insertion d'un appartement.
3. D'empêcher la modification de la superficie d'un appartement en cas de mise à jour d'un appartement.

Solution 3.1 : Trigger pour l'insertion (points 1 et 2)

DROP TRIGGER IF EXISTS check_appartment_before_insert;
DELIMITER $$
CREATE TRIGGER check_appartment_before_insert BEFORE INSERT ON appartement FOR EACH ROW
BEGIN
    -- Point 1 : Vérification du parking privatif de l'immeuble
    SELECT parkingPrivatif INTO @privatif
    FROM Immeuble
    WHERE id = NEW.immeuble;

    IF @privatif = 0 THEN
        SET NEW.placeParking := 0;
        SET NEW.prixParking := NULL;
    END IF;

    -- Point 2 : Initialisation de la superficie à 0
    SET NEW.superficie = 0;
END $$
DELIMITER ;
    

Solution 3.2 : Trigger pour la mise à jour (point 3)

Ce trigger est activé avant toute modification d'un enregistrement dans la table appartement.

DROP TRIGGER IF EXISTS check_appt_before_update;
DELIMITER $$
CREATE TRIGGER check_appt_before_update BEFORE UPDATE ON appartement FOR EACH ROW
BEGIN
    -- Point 3 : Empêcher la modification directe de la superficie
    IF NEW.superficie != OLD.superficie THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La superficie d\'un appartement ne peut pas être modifiée directement.';
    END IF;
END $$
DELIMITER ;
    

Question 4 : Triggers pour la Mise à Jour et la Suppression de Pièces

En vous inspirant du trigger de la question 2, rédigez celui qui permet de mettre à jour la superficie d'un appartement à la mise à jour d'une pièce. Rédigez également le trigger qui met à jour la superficie d'un appartement à la suppression d'une pièce.

Rappel : NEW permet de manipuler en lecture/écriture la ligne nouvellement insérée ou modifiée. OLD permet de manipuler la ligne avant modification ou suppression.

Solution 4.1 : Trigger pour la mise à jour d'une pièce

DROP TRIGGER IF EXISTS maj_superficie_appartement_after_update_piece;
DELIMITER $$
CREATE TRIGGER maj_superficie_appartement_after_update_piece AFTER UPDATE ON Piece FOR EACH ROW
BEGIN
    -- Met à jour la superficie de l'appartement en soustrayant l'ancienne superficie et en ajoutant la nouvelle
    UPDATE Appartement
    SET superficie = superficie - OLD.superficie + NEW.superficie
    WHERE num = NEW.appartement
    AND immeuble = NEW.immeuble;
END $$
DELIMITER ;
    

Solution 4.2 : Trigger pour la suppression d'une pièce

DROP TRIGGER IF EXISTS maj_superficie_appartement_after_delete_piece;
DELIMITER $$
CREATE TRIGGER maj_superficie_appartement_after_delete_piece AFTER DELETE ON Piece FOR EACH ROW
BEGIN
    -- Met à jour la superficie de l'appartement en soustrayant la superficie de la pièce supprimée
    UPDATE Appartement
    SET superficie = superficie - OLD.superficie
    WHERE num = OLD.appartement
    AND immeuble = OLD.immeuble;
END $$
DELIMITER ;
    

Exercice 2 : Gestion des Produits et Commandes

Cet exercice explore l'utilisation des triggers pour des règles métier complexes liées aux produits et aux commandes, notamment en ce qui concerne les variations de prix ou les contraintes sur les types d'articles.

Sujet : Modèle de Données

Voici le modèle de données proposé pour cet exercice :

• Produit(code, libelle, prix, formation, poids)
  • Clé primaire : code
  • Commentaire : le champ formation prend la valeur 1 (vrai) ou 0 (faux).
• Mode_Livraison(num, libelle)
  • Clé primaire : num
• Frais_Transport(#livraison, num, superficie, fonction)
  • Clé primaire : (livraison, num)
  • Clé étrangère : livraison en référence à Mode_Livraison(num)
• Commande(num, destinataire, prixTotal, #(livraison, transport))
  • Clé primaire : num
  • Clé étrangère : (livraison, transport) en référence à Frais_Transport(livraison, num)
• Ligne_Commande(#commande, #produit, prixu, quantite, prixt)
  • Clé primaire : (commande, produit)
  • Clés étrangères :
    • commande en référence à Commande(num)
    • produit en référence à Produit(code)

Note sur les champs de Frais_Transport : Les champs superficie et fonction ne semblent pas pertinents pour des frais de transport. Pour un modèle réaliste, des champs comme montant ou description_frais seraient plus appropriés.

Questions

1. Rédiger le script de création de table relatif à la base de données ci-avant décrite.
2. Rédiger le trigger permettant de vérifier la contrainte suivante : si une commande porte sur une formation, alors elle ne peut porter sur un ou plusieurs livres.
3. Rédiger le trigger permettant de vérifier la contrainte réciproque : si une commande porte sur un ou plusieurs livres, alors elle ne peut porter sur une formation.
4. Adapter le trigger de la question 2 afin de vérifier la contrainte suivante : si la commande porte sur une formation, alors il ne doit pas y avoir de frais de transport.
5. Adapter le trigger de la question 2 afin que le prix total d'une commande portant sur une formation soit automatiquement mis à jour.
6. Adapter le trigger de la question 3 de sorte que, lorsqu'une commande porte sur un ou plusieurs livres, le prix total de la commande soit automatiquement mis à jour lorsqu'une ligne de commande est insérée.
7. Préciser les cas que nous n'avons pas traités au travers des questions 1 à 6.

Ces questions invitent à réfléchir sur l'implémentation de règles métier complexes et l'automatisation des mises à jour via des triggers.

Exercice 3 : Système de Location de Véhicules

Cet exercice se concentre sur la validation des contraintes métiers lors de la location de véhicules, notamment en s'assurant que le conducteur possède le permis requis.

Sujet : Modèle de Données

Le modèle de données pour ce système de location de véhicules est le suivant :

(Le modèle de données visuel ou textuel n'est pas fourni dans le document original pour cet exercice, mais les questions impliquent un schéma avec des entités comme "Location", "Conducteur", "Véhicule" et "Permis".)

Questions

1. Créer sous MySQL une base de données correspondant au modèle de données ci-dessus (fournir le script de création de tables).
2. Rédiger le trigger qui permet, à l'insertion et à la modification d'une Location (INSERT ou UPDATE sur la table "Location"), de vérifier que le conducteur spécifié correspond à un conducteur ayant parmi ses permis celui requis pour conduire le véhicule.

Cet exercice souligne l'importance des triggers pour la validation de la logique métier complexe et pour garantir la conformité des données.

Foire Aux Questions (FAQ) sur les Triggers SQL

Qu'est-ce qu'un trigger SQL ?

Un trigger SQL est une procédure stockée qui s'exécute automatiquement (ou "se déclenche") en réponse à un événement spécifique sur une table ou une vue dans une base de données. Ces événements peuvent être des opérations de manipulation de données (INSERT, UPDATE ou DELETE). Les triggers sont utilisés pour maintenir l'intégrité des données, appliquer des règles métier complexes ou automatiser des tâches sans intervention explicite du programme d'application.

Quand utiliser un trigger SQL ?

Les triggers sont utiles dans plusieurs scénarios, notamment pour :

• Valider des données : Assurer que les données insérées ou modifiées respectent des règles métier spécifiques qui ne peuvent pas être gérées par des contraintes de table simples (comme les clés étrangères ou les contraintes CHECK).
• Maintenir la cohérence des données : Mettre à jour automatiquement des champs calculés (par exemple, une superficie totale ou un prix total) ou des tables d'audit pour suivre les modifications.
• Appliquer des règles métier complexes : Empêcher certaines opérations en fonction de conditions complexes ou enregistrer des changements historiques.

Quelle est la différence entre un trigger BEFORE et AFTER ?

La principale différence réside dans le moment d'exécution par rapport à l'opération DML (Data Manipulation Language) :

• Un trigger BEFORE s'exécute avant que l'opération DML (INSERT, UPDATE, DELETE) ne soit appliquée à la table. Il permet de modifier les données qui vont être insérées ou mises à jour (en utilisant la pseudo-ligne NEW) ou d'annuler l'opération si une condition n'est pas remplie (avec SIGNAL SQLSTATE).
• Un trigger AFTER s'exécute après que l'opération DML soit terminée. Il est souvent utilisé pour mettre à jour d'autres tables, effectuer des enregistrements d'audit ou réagir aux changements de données une fois qu'ils sont persistés. Il peut accéder aux états OLD et NEW des données.

Cela peut vous intéresser :

Rattrapage Avril2018 Solution - Modélisation Merise

Ce document pédagogique est spécifiquement conçu pour les étudiants universitaires, notamment ceux inscrits en Parcours Gestion. Il présente un examen...

Solution de l’examen 1 en informatique de gestion - Modélisa

Ce document propose la solution complète de l'examen en informatique de gestion, spécifiquement conçu pour les étudiants du Parcours Gestion en semest...

Examen en Informatique de gestion - Modélisation Merise

Ce document est un examen d'informatique de gestion conçu pour les étudiants universitaires du parcours Gestion (niveau S5). Il a pour objectif d'éval...

Correction examen conception - Modélisation Merise

Ce document pédagogique est destiné aux étudiants universitaires des filières d'informatique de gestion et des domaines connexes. Il a pour objectif d...

Devoir n°1 Merise 2 et SQL corrigé - Modélisation Merise

Ce document universitaire présente la correction détaillée du Devoir n°1, axé sur la conception de bases de données. Il est destiné aux étudiants univ...

Devoir 1 sujet - Modélisation Merise

Ce document est un devoir universitaire destiné aux étudiants en BTS CGO, axé sur l'organisation des systèmes d'information. Il présente une étude de ...

Sujet 1 mcd Gestion d'un pressing - Modélisation Merise

Ce document est destiné aux étudiants universitaires en informatique, spécifiquement ceux abordant la conception de bases de données. Il contient deux...

Solution Examen 2 en informatique de gestion - Modélisation

Ce document présente la solution détaillée d'un examen en informatique de gestion, spécifiquement conçu pour les étudiants universitaires. Il s'adress...

Voir tous →