Avez-vous une question? (216) 97 656 803 hajjriadh@gmail.com
MySQL

Correction exercices les Triggers en MySQL serie 01

Un cours complet pour maîtriser Correction exercices les Triggers en MySQL serie 01.

0 chapitres 0 QCM Accès gratuit
Commencer Retour aux cours
Sommaire du cours
Découverte du logiciel phpMyAdmin
Comment installer phpMyAdmin sur Windows
Comment créer une base de données MySQL avec phpMyAdmin
Comment mettre à jour PHPMyAdmin sur WampServer
Méthodes de création des tables sous MySQL
Créer et remplir des tables avec phpMyAdmin
Les moteurs de stockage de MySQL
Créer et remplir des tables avec SQL
Les contraintes d'intégrité avec MySQL
QCM MySQL Introduction
Créer une table à partir d'un fichier avec phpMyAdmin
Créer une table résultat à partir de tables existantes
Manipulation de données en SQL
Exercices MySQL Série 01
Correction exercices MySQL Série 01
Autres catégories
News
Microsoft Office
Les cours de programmation
MOS
WordPress
Ressources pédagogiques
Voir tout le catalogue

  1. Objectif

    • Utiliser adéquatement les triggers en MySQL

  2. Exercice 01

    • Énoncé
    • Solution
      • 1- Le script pour créer un trigger interceptant toutes les données supprimées dans la table "comptes" et les insérant dans une table "poubelle" :,
          CREATE TABLE poubelle (
    Num_Compte INT PRIMARY KEY,
    Solde_Compte FLOAT,
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER supprimer_comptes
AFTER DELETE ON comptes
FOR EACH ROW
INSERT INTO poubelle (Num_Compte, Solde_Compte)
VALUES (OLD.Num_Compte, OLD.Solde_Compte);
      • 2-Le script pour créer les tables de la base de données "Banque" :
          CREATE TABLE agences (
    Num_Agence INT PRIMARY KEY,
    Nom_Agence VARCHAR(255),
    Ville_Agence VARCHAR(255),
    Actif_Agence BOOLEAN,
    taux_Actif FLOAT,
    total_Actif FLOAT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE clients (
    Num_Client INT PRIMARY KEY,
    Nom_Client VARCHAR(255),
    Prenom_Client VARCHAR(255),
    sexe ENUM('M', 'F'),
    Ville_Client VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE comptes (
    Num_Compte INT PRIMARY KEY,
    Solde_Compte FLOAT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    Num_Client INT,
    Num_Agence INT,
    FOREIGN KEY (Num_Client) REFERENCES clients (Num_Client),
    FOREIGN KEY (Num_Agence) REFERENCES agences (Num_Agence)
);

CREATE TABLE emprunts (
    Num_Emprunt INT PRIMARY KEY,
    Montant_Emprunt FLOAT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    Num_Client INT,
    Num_Agence INT,
    FOREIGN KEY (Num_Client) REFERENCES clients (Num_Client),
    FOREIGN KEY (Num_Agence) REFERENCES agences (Num_Agence)
);
      • 3-Le script pour créer la table "emprunts_supprimees" et le trigger associé pour intercepter les données supprimées de la table "emprunts" :
          CREATE TABLE emprunts_supprimees (
    Num_Emprunt INT PRIMARY KEY,
    Montant_Emprunt FLOAT,
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Num_Client INT,
    Num_Agence INT,
    FOREIGN KEY (Num_Client) REFERENCES clients (Num_Client),
    FOREIGN KEY (Num_Agence) REFERENCES agences (Num_Agence)
);

CREATE TRIGGER emprunts_supprimees_trigger
AFTER DELETE ON emprunts
FOR EACH ROW
INSERT INTO emprunts_supprimees (Num_Emprunt, Montant_Emprunt, Num_Client, Num_Agence)
VALUES (OLD.Num_Emprunt, OLD.Montant_Emprunt, OLD.Num_Client, OLD.Num_Agence);
          Ce trigger interceptera toutes les données supprimées dans la table "emprunts" et les insérera dans la table "emprunts_supprimees" avec une horodatage pour la date de suppression. Il sera invoqué après l'instruction DELETE.

  • Exercice 02

    • Énoncé
    • Solution
      • 2- Créer un trigger permettant de compter le nombre de requêtes d’ajout, de mise à jour et de suppression effectuées par jour :
          CREATE TRIGGER audit_trigger 
AFTER INSERT OR UPDATE OR DELETE 
ON agences
FOR EACH ROW
BEGIN
    INSERT INTO audit (date, action) 
    VALUES (NOW(), 
        CASE 
            WHEN NEW.Num_Agence IS NOT NULL AND OLD.Num_Agence IS NULL THEN 'Ajout agence' 
            WHEN NEW.Num_Agence IS NOT NULL AND OLD.Num_Agence IS NOT NULL THEN 'Modification agence' 
            WHEN NEW.Num_Agence IS NULL AND OLD.Num_Agence IS NOT NULL THEN 'Suppression agence' 
        END);
END;
      • 3- Créer la table “audit_comptes“ :
          CREATE TABLE audit (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    date DATETIME NOT NULL,
    action VARCHAR(255) NOT NULL
);
      • 4.1- comptes_inserees_trigger” (Evèn. : INSERT, PE : AFTER)
          CREATE TRIGGER emprunts_inserees_trigger 
AFTER INSERT ON emprunts
FOR EACH ROW
BEGIN
    INSERT INTO audit_comptes (date, action) 
    VALUES (NOW(), 'Ajout emprunt');
END;
      • 4.2- comptes_supprimees_trigger” (Evén. : DELETE, PE : AFTER)
          CREATE TRIGGER emprunts_supprimees_trigger 
AFTER DELETE ON emprunts
FOR EACH ROW
BEGIN
    INSERT INTO audit_comptes (date, action) 
    VALUES (NOW(), 'Suppression emprunt');
END;
      • 4.3- comptes_modifiees_trigger” (Evén. : UPDATE, PE : AFTER)
          CREATE TRIGGER emprunts_modifiees_trigger 
AFTER UPDATE ON emprunts
FOR EACH ROW
BEGIN
    INSERT INTO audit_comptes (date, action) 
    VALUES (NOW(), 'Modification emprunt');
END;

  • Exercice 03

    • Énoncé
    • Solution
      • 2- 2- Le trigger permettant de vérifier si une "Num_Agence" ajoutée à la table "emprunts" existe dans la table "agences" :
          CREATE TRIGGER verif_agence_trigger
BEFORE INSERT ON emprunts
FOR EACH ROW
BEGIN
    DECLARE agence_count INT;
    SELECT COUNT(*) INTO agence_count FROM agences WHERE Num_Agence = NEW.Num_Agence;
    IF agence_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Num_Agence non valide';
    END IF;
END;
      • 3- Pour affecter le trigger "verif_agence_trigger" à la table "emprunts" pour l'événement "INSERT" avant l'exécution :
          ALTER TABLE emprunts
ADD CONSTRAINT verif_agence_trigger
BEFORE INSERT
FOR EACH ROW
BEGIN
    DECLARE agence_count INT;
    SELECT COUNT(*) INTO agence_count FROM agences WHERE Num_Agence = NEW.Num_Agence;
    IF agence_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Num_Agence non valide';
    END IF;
END;
      • 4- Le trigger "verif_agence_trigger" avec plusieurs instructions SQL, en utilisant la notation BEGIN/END :
          CREATE TRIGGER verif_agence_trigger
BEFORE INSERT ON emprunts
FOR EACH ROW
BEGIN
    DECLARE agence_count INT;
    SELECT COUNT(*) INTO agence_count FROM agences WHERE Num_Agence = NEW.Num_Agence;
    IF agence_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Num_Agence non valide';
    ELSE
        UPDATE comptes SET Solde = Solde - NEW.Montant WHERE Num_Client = NEW.Num_Client;
    END IF;
END;

  • Exercice 04

    • Énoncé
    • Solution
      • 1- Trigger pour vérifier la contrainte de prix de parking :
          DELIMITER $$
CREATE TRIGGER check_prix_parking
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 = 'Le prix de parking doit être NULL si l''appartement ne possède pas de place de parking';
    END IF;
END$$
DELIMITER ;
        • Pour tester ce trigger, on peut insérer un appartement avec une place de parking et un prix de parking non null, ce qui doit générer une erreur :
          • INSERT INTO Appartement (immeuble, num, description, loyer, superficie, terrasse, classeConso, chauffage, placeParking, prixParking) VALUES (1, 1, 'Bel appartement avec place de parking', 800, 50, 0, 'D', 'E', 1, 50);
      • 2- Trigger pour mettre à jour la superficie d'un appartement :
          DELIMITER $$
CREATE TRIGGER update_superficie_appartement
AFTER INSERT ON Piece
FOR EACH ROW
BEGIN
    UPDATE Appartement SET superficie = (
        SELECT SUM(superficie)
        FROM Piece
        WHERE immeuble = NEW.immeuble AND appartement = NEW.appartement
    )
    WHERE immeuble = NEW.immeuble AND num = NEW.appartement;
END$$
DELIMITER ;
      • 3- Trigger pour vérifier la contrainte de place de parking, initialiser la superficie à 0 et empêcher la modification de la superficie :
          DELIMITER $$
CREATE TRIGGER check_place_parking_and_init_superficie
BEFORE INSERT ON Appartement
FOR EACH ROW
BEGIN
    IF NEW.placeParking = 1 AND (SELECT parkingPrivatif FROM Immeuble WHERE Id = NEW.immeuble) = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Un appartement ne peut avoir de place de parking si l''immeuble n''a pas de parking privatif';
    END IF;
    SET NEW.superficie = 0;
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER forbid_superficie_update
BEFORE UPDATE ON Appartement
FOR EACH ROW
BEGIN
    IF NEW.superficie <> OLD.superficie THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La modification de la superficie d''un appartement est interdite';
    END IF;
END$$
DELIMITER ;
      • 4- Trigger pour mettre à jour la superficie à la modification et suppression d'une pièce :
          DELIMITER $$
CREATE TRIGGER update_superficie_appartement_update
AFTER UPDATE ON Piece
FOR EACH ROW
BEGIN
    UPDATE Appartement SET superficie = (
        SELECT SUM(superficie)
        FROM Piece
        WHERE immeuble = NEW.immeuble AND appartement = NEW.appartement
    )
    WHERE immeuble = NEW.immeuble AND num = NEW.appartement;
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER update_superficie_appartement_delete
AFTER DELETE ON Piece
FOR EACH ROW
BEGIN
    UPDATE Appartement SET superficie = (
        SELECT SUM(superficie)
        FROM Piece
        WHERE immeuble = OLD.immeuble AND appartement = OLD.appartement
    )
    WHERE immeuble = OLD
      Cours MySQL
      Créer des triggers dans MySQL