Back

Correction exercices les Triggers en MySQL serie 01

Correction exercices les Triggers en MySQL serie 01

  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
    author avatar
    Riadh HAJJI

    Riadh HAJJI

    You may also like

    Les curseurs dans MySQL
    18 mai, 2024

    Les triggers de base de données MySQL Les curseurs dans MySQL Objectifs À la fin de ce cours, vous serez capables de : Comprendre le concept et l’utilité des curseurs en SQL : Déclarer et utiliser des curseurs dans MySQL …

    Exercices les bases de données MySQL Série 01
    11 septembre, 2022

    Les jointures en MySQL Jointure interne MySQL Jointure externe MySQL Exercices les bases de données MySQL Série 01 (Correction) Exercices les bases de données MySQL Série 01 Exercice 01 Énoncé Écrire le schéma relationnel de la base de données en …

    Correction exercices les procédures stockées MySQL : Série 01
    8 décembre, 2021

    Les procédures stockées de base de données MySQL Créer une procédure stockée à l’aide de ligne de commande MySQL Créer une procédure stockée à l’aide de l’assistant MySQL Workbench Créer une procédure stockée paramétrée dans MySQL Exercices les procédures stockées …