D4-A07 : Exercice applicatif sur les déclencheurs : Gestion automatisée des données d'un parc immobilier

Soit le modèle relationnel des données suivant :

immeuble(id, adrNum, adrVoie, adrCodePostal, adrVille, fibreOptique, parkingPrivatif)
Clef primaire : id (autoincrémenté) 
Remarque : tous les attributs sont non null, fibreOptique et parkingPrivatif sont de type booléen 

appartement(idImmeuble, numAppart, description, loyer, superficie, terrasse, classeConso, chauffage, placeParking, prixParking)
Clef primaire : idImmeuble, numAppart
Clef étrangère : idImmeuble en référence à immeuble(id)
Remarque : attributs NULL par défaut, placeParking est de type booléen

piece(idImmeuble, idAppartement, numPiece, superficie, fonction)
Clef primaire : idImmeuble, idAppartement, numPiece
Clef étrangère : (idImmeuble, idAppartement) en référence à appartement(idImmeuble, numAppart)
Remarque : attributs NULL par défaut

1. créer le script de la base de données nommé votreNomBDParcImmo.sql
2. implanter ce script dans votre SGBD
3. vous avez ci-dessous le code d'un déclencheur permettant de vérifier la contrainte suivante : le prix de la place de parking d’un appartement peut et doit être NULL si l’appartement ne possède pas de place de parking. Programmer le déclencheur avec votre environnement de gestion de bases de données MySQL.

   1. DROP TRIGGER IF EXISTS prixAppartBeforeInsert;
      DELIMITER $$ 
      CREATE TRIGGER prixAppartBeforeInsert BEFORE INSERT ON appartement FOR EACH ROW
      BEGIN
          IF NEW.placeParking = 0 THEN
              SET NEW.prixParking := NULL; -- affectation de variable
          END IF;
      END $$
      DELIMITER ;

4. Créer le même trigger pour l’événement « BEFORE UPDATE »
5. Alternative empêchant l’insertion (aucune action, transaction interrompue) si il y a un prix et pas de place de parking, compléter vos déclencheurs avec cette alternative :

   1.     IF NEW.placeParking = 0  AND NEW.prixParking IS NOT NULL THEN
              -- Interrompt la transaction en levant une erreur, i.e. empêche l’insertion (ou la modification)
              SIGNAL sqlstate '45000' SET message_text ='prix parking sans parking';
          END IF;

6. Tests unitaires : vérifier vos programmes avec le jeu d'essai suivant :
   1. Est-ce que l'appartement 1.1 a été créé ? Pourquoi ?
   2. Est-ce que l'appartement 1.2 a été créé ? Pourquoi ?
   3. Re-inséré-le si besoin.
   4. Modifier l'appartement 1.2 afin qu'il n'ai plus de parking : que se passe t'il ? Quelle solution proposez vous pour éviter de lever une erreur ? Appliquer cette solution et tester-la.

-- 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 sans place de parking, l’autre avec une 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);

1. Adapter les triggers précédents 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 ;
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 les triggers nécessaires.
   1. Combien en créez-vous ? sur quels événements ? sur quelle(s) tables() ?
   2. Comment les appelez-vous ?
3. Gérer par défaut la superficie de l’appartement à 0 à l’insertion d’un appartement ;

• Insérer une pièce de superficie X et constater que la superficie de l’appartement à augmenter de X.
• Supprimer une pièce, constater la mise à jour de la superficie.
• Modifier la superficie d’une pièce, constater la mise à jour de la superficie.
• …