BTS SIO TP N°5 : SQL et triggers
SLAM3 - Conception de BD À la découverte des triggers
TP N°5 : SQL et triggers
Thème : à la découverte des triggers
Exercice 1 : parc immobilier (exemple)
Sujet :
Soit le modèle de données suivant :
Immeuble(id, adrNum, adrVoie, adrCodePostal, adrVille, fibreOptique, parkingPrivatif)
Clef primaire : id
Appartement(#immeuble, num, description, loyer, superficie, terrasse, classeConso, chauffage,
placeParking, prixParking)
Clef primaire : immeuble, num
Clef étrangère : immeuble en référence à Immeuble(id)
Piece(#(immeuble, appartement), num, superficie, fonction)
Clef primaire : immeuble, appartement, num
Clefs étrangères : (immeuble, appartement) en référence à Appartement(immeuble, num)
Photo(num, titre, description, uri, #(immeuble, appartement))
Clef primaire : num
Clef étrangère : (immeuble, appartement) en référence à Appartement(immeuble, num)
J. Paquereau 1/8
BTS SIO TP N°5 : SQL et triggers
SLAM3 - Conception de BD À la découverte des triggers
En voici le script de création de tables :
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)
);
Créer ses tables dans une base de données sous MySQL.
Questions :
1. Rédiger le trigger permettant de vérifier la contrainte suivante : le prix de la place de parking d’un ap-
partement peut et doit être NULL si l’appartement ne possède pas de place de parking. Tester le bon
fonctionnement de votre trigger.
DROP TRIGGER IF EXISTS check_appartment;
DELIMITER $$
CREATE TRIGGER check_appartment_before_insert BEFORE INSERT ON appartement FOR EACH ROW
BEGIN
IF [Link] = 0 THEN
SET [Link] := NULL;
END IF;
J. Paquereau 2/8
BTS SIO TP N°5 : SQL et triggers
SLAM3 - Conception de BD À la découverte des triggers
END $$
DELIMITER ; -- N.B. : le même trigger doit encore être ajouté « BEFORE UPDATE »
Alternative empêchant littéralement l’insertion (méthode plus classique) :
…
IF [Link] = 0 THEN
-- Interrompt la transaction en levant une erreur, i.e. empêche l’insertion (ou la modification)
SIGNAL sqlstate '45000' SET message_text = 'Constraint violation: expected "placeParking"=0';
END IF;
…
Tests unitaires :
-- Insertion d'un immeuble
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, l’un avec une place de parking, l’autre sans place de parking, et
-- les deux ayant un prix de place de parking
-- La première insertion doit échouer, la seconde réussir.
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);
2. On souhaite que la contrainte suivante soit vérifiée : la superficie totale d’un appartement doit être
égale à la somme de la superficie de chacune de ses pièces. Pour ce faire, créer le trigger qui permet de
mettre à jour la superficie d’un appartement à l’insertion d’une pièce.
Version longue :
DELIMITER $$
CREATE TRIGGER maj_supercifie_appartment_before_insert AFTER INSERT ON piece FOR EACH ROW
BEGIN
DECLARE superficieTotale INT;
-- calcule la superficie totale de l'appartement
SELECT sum(superficie) INTO superficieTotale
FROM piece
WHERE immeuble = [Link]
AND appartement = [Link];
-- met à jour la superficie de l'appartement
UPDATE appartement
SET superficie = superficieTotale
WHERE immeuble = [Link]
AND num = [Link];
J. Paquereau 3/8
BTS SIO TP N°5 : SQL et triggers
SLAM3 - Conception de BD À la découverte des triggers
END $$
DELIMITER ;
Notation allégée pour les variables :
DELIMITER $$
CREATE TRIGGER maj_supercifie_appartment_before_insert AFTER INSERT ON piece FOR EACH ROW
BEGIN
SELECT sum(superficie) INTO @superficieTotale
FROM piece
WHERE immeuble = [Link]
AND appartement = [Link];
-- met à jour la superficie de l'appartement
UPDATE appartement
SET superficie = @superficieTotale
WHERE immeuble = [Link]
AND num = [Link];
END $$
DELIMITER ;
Version courte et soit dit en passant plus performante :
DELIMITER $$
CREATE TRIGGER maj_supercifie_apprt_before_insert BEFORE INSERT ON piece FOR EACH ROW
BEGIN
-- on incrémente très simplement la superficie de l'appartement
UPDATE appartement
SET superficie = superficie + [Link]
WHERE immeuble = [Link]
AND num = [Link];
END $$
DELIMITER ;
Test unitaire :
Insérer une pièce de superficie X et constater que la superficie de l’appartement à augmenter de X.
3. Adapter le trigger de la question 1 afin :
- de vérifier la contrainte suivante : un appartement ne peut avoir de place de parking si l’immeuble n’a
pas de parking privatif ;
- d’initialiser la superficie de l’appartement à 0 à l’insertion d’un appartement ;
- d’empêcher la modification de la superficie d’un appartement en cas de mise à jour d’un appartement.
CREATE TRIGGER check_appartment_before_insert BEFORE INSERT ON appartement FOR EACH ROW
J. Paquereau 4/8
BTS SIO TP N°5 : SQL et triggers
SLAM3 - Conception de BD À la découverte des triggers
BEGIN
-- 1er point :
SELECT parkingPrivatif INTO @privatif
FROM Immeuble
WHERE id = [Link] ;
IF @privatif = 0 THEN
SET [Link] := 0
SET [Link] := NULL ;
END IF ;
-- 2ème point :
SET [Link] = 0 ;
END ;
-- 3ème point :
CREATE TRIGGER check_appt_before_update BEFORE INSERT ON appartement FOR EACH ROW
BEGIN
IF [Link] != [Link] THEN
SIGNAL sqlstate '45000' SET message_text = 'Superficie non modifiable';
END IF ;
END ;
4. En vous inspirant du trigger de la question 2, rédiger celui qui permet de mettre à jour la superficie
d’un appartement à la mise à jour d’une pièce. Rédiger également le trigger qui met à jour la superficie
d’un appartement à la suppression d’une pièce.
N.B. : si 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.
CREATE TRIGGER check_sup_piece_before_update BEFORE UPDATE ON Piece FOR EACH ROW
BEGIN
UPDATE Appartement
SET superficie = superficie + [Link] - [Link]
WHERE num = [Link]
AND [Link] = [Link] ;
END ;
CREATE TRIGGER check_sup_piece_after_delete AFTER DELETE ON Piece FOR EACH ROW
BEGIN
UPDATE Appartement
SET superficie = superficie - [Link]
WHERE num = [Link]
AND [Link] = [Link] ;
END ;
J. Paquereau 5/8
BTS SIO TP N°5 : SQL et triggers
SLAM3 - Conception de BD À la découverte des triggers
Exercice 2 : des produits au prix variant au fil du temps
Sujet :
Soit le modèle de données suivant :
Produit(code, libelle, prix, formation, poids)
Clef primaire : code
Commentaire : le champ discrimination formation prend la valeur 1 (vrai) ou 0 (faux)
Mode_Livraison(num, libelle)
Clef primaire : num
Frais_Transport(#livraison, num, superficie, fonction)
Clef primaire : livraison, num
Clefs étrangères : livraison en référence à Mode_Livraison(num)
Commande(num, destinataire, prixTotal, #(livraion, transport))
Clef primaire : num
Clef étrangère : (livraison, transport) en référence à Frais_Transport(livraison, num)
Ligne_Commande(#commande, #produit, prixu, quantite, prixt)
Clef primaire : commande, produit
Clef étrangères :
- commande en référence à Commande(num)
- produit en référence à Produit(code)
J. Paquereau 6/8
BTS SIO TP N°5 : SQL et triggers
SLAM3 - Conception de BD À la découverte des triggers
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.
J. Paquereau 7/8
BTS SIO TP N°5 : SQL et triggers
SLAM3 - Conception de BD À la découverte des triggers
Exercice 3 : location de véhicules
Sujet :
Soit le modèle de données suivant :
1,1
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.
J. Paquereau 8/8