Exercices les Triggers en MySQL : Série 01

Exercices les Triggers en MySQL : Série 01

  1. Objectif

    • Utiliser adéquatement les triggers en MySQL
  2. Exercice 01

    1. Énoncé
      • Création d’une “poubelle”
      • Créer un trigger interceptant toutes les données supprimées pour les mettre dans une table “poubelle” afin d’éviter les suppression accidentelles.
      • Utiliser la base de données “Banque” suivante :
      • Exercices les Triggers en MySQL : Série 01

      • Rédiger le script de création de tables relatif à la base de données ci-avant décrite.
      • Créer la table “emprunts_supprimees” Affecter à la table emprunts le trigger nommé “emprunts_supprimees_trigger
      • Utiliser l’événement DELETE
      • Le trigger sera invoqué après l’instruction DELETE
    2. Solution
      • Essayez de faire l’exercice de votre côté avant de regarder la Solution !



  3. Exercice 02

    1. Énoncé
      • Création d’un système d’audit
      • Utiliser la base de données “Banque” suivante :
      • Exercices les Triggers en MySQL : Série 01

      • Rédiger le script de création de table relatif à la base de données ci-avant décrite.
      • Créer un trigger permettant de compter le nombre de requêtes d’ajout, de mise à jour et de suppression effectués par jour
      • Créer la table “audit_comptes
      • Affecter à la table “emprunts” les triggers suivant:
        1. comptes_inserees_trigger” (Evèn. : INSERT, PE : AFTER)
        2. comptes_supprimees_trigger” (Evén. : DELETE, PE : AFTER)
        3. comptes_modifiees_trigger” (Evén. : UPDATE, PE : AFTER)
    2. Solution
      • Essayez de faire l’exercice de votre côté avant de regarder la Solution !
  4. Exercice 03

    1. Énoncé
      • Émulation Intégrité Référentielle
      • Utiliser la base de données “Banque” suivante :
      • Exercices les Triggers en MySQL : Série 01

      • Rédiger le script de création de table relatif à la base de données ci-avant décrite.
      • Créer un trigger permettant de vérifier si avant d’ajouter une “Num_Agence” dans la table “emprunts” , elle fait référence à une agence existante dans la table “agences
      • Affecter à la table “emprunts” le trigger “verif_agence_trigger” (Evén. : INSERT, PE : BEFORE)
      • Adopter la notation pour un trigger comportant plusieurs instructions SQL
    2. Solution
      • Essayez de faire l’exercice de votre côté avant de regarder la Solution !
  5. Exercice 04

    1. Énoncé
      • Voici le script de création de tables d’une base de données “parc-immobilier” :
      • 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)
        );
        
        1. Créer ses tables dans une base de données sous MySQL.
        2. Questions :
          1. Rédiger le trigger 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. Tester le bon fonctionnement de votre trigger.
          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.
          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.
          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.

    2. Solution
      • Essayez de faire l’exercice de votre côté avant de regarder la Solution !