Exercices Corrigés:Triggers,Déclencheur

Soit le schéma relationnel suivant:

-- Table Cours
CREATE TABLE Cours (
    ID_Cours INT PRIMARY KEY,
    NomCours VARCHAR(255),
    CodeCours VARCHAR(20)
);

-- Table Enseignant
CREATE TABLE Enseignant (
    ID_Enseignant INT PRIMARY KEY,
    Nom VARCHAR(255),
    Prenom VARCHAR(255),
    Titre VARCHAR(50)
);

-- Table Inscription
CREATE TABLE Inscription (
    ID_Inscription INT PRIMARY KEY,
    ID_Etudiant INT,
    ID_Cours INT,
    Annee INT,
    FOREIGN KEY (ID_Etudiant) REFERENCES Etudiant(ID_Etudiant),
    FOREIGN KEY (ID_Cours) REFERENCES Cours(ID_Cours)
);

Questions & Réponse

Déclencheur pour mettre à jour la date de modification d'un enseignant :

DELIMITER //
CREATE TRIGGER UpdateEnseignantModified
BEFORE UPDATE ON Enseignant
FOR EACH ROW
SET NEW.DateModification = NOW();
//
DELIMITER ;

Déclencheur pour vérifier la validité de l'année lors de l'insertion d'une inscription :

DELIMITER //
CREATE TRIGGER CheckYearValidity
BEFORE INSERT ON Inscription
FOR EACH ROW
BEGIN
    IF NEW.Annee < YEAR(NOW()) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'L\'année d\'inscription ne peut pas être antérieure à l\'année actuelle.';
    END IF;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour la moyenne générale d'un étudiant lors de l'insertion d'une note :

DELIMITER //
CREATE TRIGGER UpdateAverageGrade
AFTER INSERT ON Notes
FOR EACH ROW
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

    SELECT AVG(Note) INTO avgGrade
    FROM Notes
    WHERE ID_Etudiant = NEW.ID_Etudiant;

    UPDATE Etudiant
    SET MoyenneGenerale = avgGrade
    WHERE ID_Etudiant = NEW.ID_Etudiant;
END;
//
DELIMITER ;

Déclencheur pour empêcher la suppression d'un enseignant lié à un cours :

DELIMITER //
CREATE TRIGGER PreventDeleteTeacherWithCourses
BEFORE DELETE ON Enseignant
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM Cours WHERE ID_Enseignant = OLD.ID_Enseignant) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Impossible de supprimer un enseignant lié à un cours.';
    END IF;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour la mention d'un étudiant lors de l'insertion d'une nouvelle inscription :

DELIMITER //
CREATE TRIGGER UpdateStudentMention
AFTER INSERT ON Inscription
FOR EACH ROW
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

    SELECT AVG(Note) INTO avgGrade
    FROM Notes
    WHERE ID_Etudiant = NEW.ID_Etudiant;

    CASE
        WHEN avgGrade >= 16 THEN
            UPDATE Inscription SET Mention = 'Très Bien' WHERE ID_Etudiant = NEW.ID_Etudiant AND ID_Cours = NEW.ID_Cours;
        WHEN avgGrade >= 14 THEN
            UPDATE Inscription SET Mention = 'Bien' WHERE ID_Etudiant = NEW.ID_Etudiant AND ID_Cours = NEW.ID_Cours;
        WHEN avgGrade >= 12 THEN
            UPDATE Inscription SET Mention = 'Assez Bien' WHERE ID_Etudiant = NEW.ID_Etudiant AND ID_Cours = NEW.ID_Cours;
        ELSE
            UPDATE Inscription SET Mention = 'Non Classé' WHERE ID_Etudiant = NEW.ID_Etudiant AND ID_Cours = NEW.ID_Cours;
    END CASE;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour le nombre d'étudiants inscrits lors de l'insertion ou la suppression d'une inscription :

DELIMITER //
CREATE TRIGGER UpdateEnrollmentCount
AFTER INSERT, DELETE ON Inscription
FOR EACH ROW
BEGIN
    UPDATE Cours
    SET NombreEtudiantsInscrits = (SELECT COUNT(*) FROM Inscription WHERE ID_Cours = NEW.ID_Cours)
    WHERE ID_Cours = NEW.ID_Cours;
END;
//
DELIMITER ;

Déclencheur pour empêcher la suppression d'un cours lié à une inscription existante :

DELIMITER //
CREATE TRIGGER PreventDeleteCourseWithEnrollment
BEFORE DELETE ON Cours
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM Inscription WHERE ID_Cours = OLD.ID_Cours) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Impossible de supprimer un cours lié à une inscription existante.';
    END IF;
END;
//
DELIMITER ;

Déclencheur pour vérifier la validité d'une note lors de l'insertion ou la mise à jour d'une note :

DELIMITER //
CREATE TRIGGER CheckGradeValidity
BEFORE INSERT, UPDATE ON Notes
FOR EACH ROW
BEGIN
    IF NEW.Note < 0 OR NEW.Note > 20 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La note doit être comprise entre 0 et 20.';
    END IF;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour le statut de l'enseignant lors de la suppression d'un cours lié à cet enseignant :

DELIMITER //
CREATE TRIGGER UpdateTeacherStatus
AFTER DELETE ON Cours
FOR EACH ROW
BEGIN
    UPDATE Enseignant
    SET Statut = 'Inactif'
    WHERE ID_Enseignant = OLD.ID_Enseignant
        AND NOT EXISTS (SELECT 1 FROM Cours WHERE ID_Enseignant = OLD.ID_Enseignant);
END;
//
DELIMITER ;

Déclencheur pour mettre à jour le nombre d'inscriptions d'un étudiant lors de l'insertion ou la suppression d'une inscription :

DELIMITER //
CREATE TRIGGER UpdateStudentEnrollmentCount
AFTER INSERT, DELETE ON Inscription
FOR EACH ROW
BEGIN
    UPDATE Etudiant
    SET NombreInscriptions = (SELECT COUNT(*) FROM Inscription WHERE ID_Etudiant = NEW.ID_Etudiant)
    WHERE ID_Etudiant = NEW.ID_Etudiant;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour le nombre de cours enseignés par un enseignant lors de la suppression d'un cours :

DELIMITER //
CREATE TRIGGER UpdateTeacherCourseCount
AFTER DELETE ON Cours
FOR EACH ROW
BEGIN
    UPDATE Enseignant
    SET NombreCoursEnseignes = (SELECT COUNT(*) FROM Cours WHERE ID_Enseignant = OLD.ID_Enseignant)
    WHERE ID_Enseignant = OLD.ID_Enseignant;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour la liste des cours d'un enseignant lors de la suppression d'un cours :

DELIMITER //
CREATE TRIGGER UpdateTeacherCourseList
AFTER DELETE ON Cours
FOR EACH ROW
BEGIN
    UPDATE Enseignant
    SET CoursEnseignes = (SELECT GROUP_CONCAT(NomCours SEPARATOR ', ') FROM Cours WHERE ID_Enseignant = OLD.ID_Enseignant)
    WHERE ID_Enseignant = OLD.ID_Enseignant;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour la moyenne générale d'un étudiant lors de la suppression d'une note :

DELIMITER //
CREATE TRIGGER UpdateAverageGradeOnNoteDelete
AFTER DELETE ON Notes
FOR EACH ROW
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

    SELECT AVG(Note) INTO avgGrade
    FROM Notes
    WHERE ID_Etudiant = OLD.ID_Etudiant;

    UPDATE Etudiant
    SET MoyenneGenerale = avgGrade
    WHERE ID_Etudiant = OLD.ID_Etudiant;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour la mention d'un étudiant lors de la suppression d'une inscription :

DELIMITER //
CREATE TRIGGER UpdateStudentMentionOnInscriptionDelete
AFTER DELETE ON Inscription
FOR EACH ROW
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

    SELECT AVG(Note) INTO avgGrade
    FROM Notes
    WHERE ID_Etudiant = OLD.ID_Etudiant;

    CASE
        WHEN avgGrade >= 16 THEN
            UPDATE Inscription SET Mention = 'Très Bien' WHERE ID_Etudiant = OLD.ID_Etudiant AND ID_Cours = OLD.ID_Cours;
        WHEN avgGrade >= 14 THEN
            UPDATE Inscription SET Mention = 'Bien' WHERE ID_Etudiant = OLD.ID_Etudiant AND ID_Cours = OLD.ID_Cours;
        WHEN avgGrade >= 12 THEN
            UPDATE Inscription SET Mention = 'Assez Bien' WHERE ID_Etudiant = OLD.ID_Etudiant AND ID_Cours = OLD.ID_Cours;
        ELSE
            UPDATE Inscription SET Mention = 'Non Classé' WHERE ID_Etudiant = OLD.ID_Etudiant AND ID_Cours = OLD.ID_Cours;
    END CASE;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour le nombre d'étudiants inscrits lors de la suppression d'une inscription :

DELIMITER //
CREATE TRIGGER UpdateEnrollmentCountOnInscriptionDelete
AFTER DELETE ON Inscription
FOR EACH ROW
BEGIN
    UPDATE Cours
    SET NombreEtudiantsInscrits = (SELECT COUNT(*) FROM Inscription WHERE ID_Cours = OLD.ID_Cours)
    WHERE ID_Cours = OLD.ID_Cours;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour la liste des cours d'un enseignant lors de la suppression d'un cours :

DELIMITER //
CREATE TRIGGER UpdateTeacherCourseListOnCourseDelete
AFTER DELETE ON Cours
FOR EACH ROW
BEGIN
    UPDATE Enseignant
    SET CoursEnseignes = (SELECT GROUP_CONCAT(NomCours SEPARATOR ', ') FROM Cours WHERE ID_Enseignant = OLD.ID_Enseignant)
    WHERE ID_Enseignant = OLD.ID_Enseignant;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour le nombre d'inscriptions d'un étudiant lors de la suppression de l'étudiant :

DELIMITER //
CREATE TRIGGER UpdateStudentEnrollmentCountOnStudentDelete
AFTER DELETE ON Etudiant
FOR EACH ROW
BEGIN
    UPDATE Etudiant
    SET NombreInscriptions = 0
    WHERE ID_Etudiant = OLD.ID_Etudiant;
END;
//
DELIMITER ;

Déclencheur pour supprimer les notes associées à un étudiant lors de la suppression de l'étudiant :

DELIMITER //
CREATE TRIGGER DeleteNotesOnStudentDelete
AFTER DELETE ON Etudiant
FOR EACH ROW
BEGIN
    DELETE FROM Notes
    WHERE ID_Etudiant = OLD.ID_Etudiant;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour le nombre de cours enseignés par un enseignant lors de la suppression d'un enseignant :

DELIMITER //
CREATE TRIGGER UpdateTeacherCourseCountOnTeacherDelete
AFTER DELETE ON Enseignant
FOR EACH ROW
BEGIN
    UPDATE Enseignant
    SET NombreCoursEnseignes = 0
    WHERE ID_Enseignant = OLD.ID_Enseignant;
END;
//
DELIMITER ;

Déclencheur pour supprimer les cours associés à un enseignant lors de la suppression de l'enseignant :

DELIMITER //
CREATE TRIGGER DeleteCoursesOnTeacherDelete
AFTER DELETE ON Enseignant
FOR EACH ROW
BEGIN
    DELETE FROM Cours
    WHERE ID_Enseignant = OLD.ID_Enseignant;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour la mention d'un étudiant lors de la suppression d'une note :

DELIMITER //
CREATE TRIGGER UpdateStudentMentionOnNoteDelete
AFTER DELETE ON Notes
FOR EACH ROW
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

    SELECT AVG(Note) INTO avgGrade
    FROM Notes
    WHERE ID_Etudiant = OLD.ID_Etudiant;

    CASE
        WHEN avgGrade >= 16 THEN
            UPDATE Inscription SET Mention = 'Très Bien' WHERE ID_Etudiant = OLD.ID_Etudiant AND ID_Cours = OLD.ID_Cours;
        WHEN avgGrade >= 14 THEN
            UPDATE Inscription SET Mention = 'Bien' WHERE ID_Etudiant = OLD.ID_Etudiant AND ID_Cours = OLD.ID_Cours;
        WHEN avgGrade >= 12 THEN
            UPDATE Inscription SET Mention = 'Assez Bien' WHERE ID_Etudiant = OLD.ID_Etudiant AND ID_Cours = OLD.ID_Cours;
        ELSE
            UPDATE Inscription SET Mention = 'Non Classé' WHERE ID_Etudiant = OLD.ID_Etudiant AND ID_Cours = OLD.ID_Cours;
    END CASE;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour la moyenne générale d'un étudiant lors de la suppression d'une note :

DELIMITER //
CREATE TRIGGER UpdateAverageGradeOnNoteDelete
AFTER DELETE ON Notes
FOR EACH ROW
BEGIN
    DECLARE avgGrade DECIMAL(5,2);

    SELECT AVG(Note) INTO avgGrade
    FROM Notes
    WHERE ID_Etudiant = OLD.ID_Etudiant;

    UPDATE Etudiant
    SET MoyenneGenerale = avgGrade
    WHERE ID_Etudiant = OLD.ID_Etudiant;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour le nombre d'étudiants inscrits lors de la suppression d'une inscription :

DELIMITER //
CREATE TRIGGER UpdateEnrollmentCountOnInscriptionDelete
AFTER DELETE ON Inscription
FOR EACH ROW
BEGIN
    UPDATE Cours
    SET NombreEtudiantsInscrits = (SELECT COUNT(*) FROM Inscription WHERE ID_Cours = OLD.ID_Cours)
    WHERE ID_Cours = OLD.ID_Cours;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour le nombre d'inscriptions d'un étudiant lors de la suppression d'un étudiant :

DELIMITER //
CREATE TRIGGER UpdateStudentEnrollmentCountOnStudentDelete
AFTER DELETE ON Etudiant
FOR EACH ROW
BEGIN
    UPDATE Etudiant
    SET NombreInscriptions = 0
    WHERE ID_Etudiant = OLD.ID_Etudiant;
END;
//
DELIMITER ;

Déclencheur pour mettre à jour le nombre de cours enseignés par un enseignant lors de la suppression d'un enseignant :

DELIMITER //
CREATE TRIGGER UpdateTeacherCourseCountOnTeacherDelete
AFTER DELETE ON Enseignant
FOR EACH ROW
BEGIN
    UPDATE Enseignant
    SET NombreCoursEnseignes = 0
    WHERE ID_Enseignant = OLD.ID_Enseignant;
END;
//
DELIMITER ;

Fonctions et procédures

Les triggers

la Sécurité de données

Exercices

Mysql

SELECT

DataTypes

UPDATE

DELETE

CREATE

DROP

RENAME

ALTER

Tables

Exercices Corrigés:Triggers,Déclencheur