Exercices corrigés sur les triggers en Oracle - Exercice 1

Pour gérer facilement la base de données de l'école, le gestionnaire de base de données décide de créer des déclencheurs. Voici un extrait du schéma de base de données:

•  Etudiant(eid:integer, enom:string, age:integer, niveau:string, section:string)
•  Cours(cnom:string, heure_cours:time, salle: string, #pid:integer)
•  Inscription(#eid:integer, #cnom:string)
•  Professeur(pid:integer, pnom:string, #depid:integer)

Exprimez les exigences suivantes en utilisant des déclencheurs :

1. Chaque Cours a un effectif maximum de 30 étudiants.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_1
   AFTER INSERT ON Inscription FOR EACH ROW
   DECLARE
       NB NUMBER;
   BEGIN
       SELECT COUNT(*) INTO NB FROM Inscription WHERE cnom=:NEW.cnom;
       IF NB>30 THEN
           RAISE_APPLICATION_ERROR(-20005,'Cours a un effectif maximum de 30 etudiants');
       END IF;
   END;
                                               

2. Seuls les enseignants du département avec depid = 33 enseignent plus de trois cours.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_2
   AFTER INSERT OR UPDATE ON Cours FOR EACH ROW
   DECLARE
       NB NUMBER;
       DEP NUMBER;
   BEGIN
       SELECT depid INTO DEP FROM Professeur WHERE pid=:NEW.pid;
       SELECT COUNT(*) INTO NB FROM Cours WHERE pid=:NEW.pid;
       IF DEP!=33 AND NB>3 THEN
           RAISE_APPLICATION_ERROR(-20005,'Seuls les enseignants du département avec depid = 33 enseignent plus de trois cours');
       END IF;
   END;
                                               

3. Chaque étudiant doit être inscrit au cours "informatique théorique".  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_3
   AFTER INSERT ON Etudiant FOR EACH ROW
   BEGIN
       INSERT INTO Inscription VALUES(:NEW.eid,'Informatique théorique');
   END;
                                               

4. Aucun département ne peut avoir plus de 10 enseignants.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_4
   AFTER INSERT OR UPDATE ON Professeur FOR EACH ROW
   DECLARE
       NB NUMBER;
   BEGIN
       SELECT COUNT(*) INTO NB FROM Professeur WHERE depid=:NEW.depid;
       IF NB>10 THEN
           RAISE_APPLICATION_ERROR(-20005,'Aucun département ne peut avoir plus de 10 enseignants.');
       END IF;
   END;
                                               

5. Le nombre d'élèves inscrits dans la section base de données doit être supérieur au nombre d'élèves dans la section Mathématiques.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_5
   AFTER INSERT OR UPDATE ON Etudiant FOR EACH ROW
   WHEN NEW.niveau='Base de données' OR NEW.niveau='Mathématiques'
   DECLARE
       NB1 NUMBER;
       NB2 NUMBER;
   BEGIN
       SELECT COUNT(*) INTO NB1 FROM Etudiant WHERE niveau='Base de données';
       SELECT COUNT(*) INTO NB2 FROM Etudiant WHERE niveau='Mathématiques';
       IF NB2<NB1 THEN
           RAISE_APPLICATION_ERROR(-20005,'Le nombre d eleves inscrits dans la section base de donnees doit être superieur au nombre d eleves
   dans la section Mathematiques.');
       END IF;
   END;
                                               

6. le nombre d'inscription aux cours enseignés par les enseignants du département avec depid = 33 est supérieur au nombre d'inscriptions dans la section mathématique.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_6
   AFTER INSERT OR UPDATE ON Etudiant FOR EACH ROW
   WHEN NEW.niveau='Mathématiques'
   DECLARE
       NB1 NUMBER;
       NB1 NUMBER;
   BEGIN
       SELECT COUNT(*) INTO NB1 FROM Etudiant WHERE niveau='Mathématiques';
       SELECT COUNT(*) INTO NB2 FROM Professeur P INNER JOIN Cours C ON P.pid=C.pid INNER JOIN Inscription I ON I.cnom=C.cnom WHERE P.depid=33;
       IF NB1>NB2 THEN
           RAISE_APPLICATION_ERROR(-20005,'Nombre d inscription dans math depasse depid=33 ');
       END IF;
   END;
                                               

7. Les professeurs de différents départements ne peuvent pas enseigner dans la même salle.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_7
   BEFORE INSERT OR UPDATE ON Cours FOR EACH ROW
   WHEN NEW.pid>0
   DECLARE
       DEP1 NUMBER;
       NB NUMBER;
   BEGIN
       SELECT depid INTO DEP1 FROM Professeur WHERE pid=:NEW.pid;
       SELECT count(*) INTO NB FROM Cours C INNER JOIN Professeur P ON C.pid=P.pid WHERE C.salle=:NEW.salle AND P.depid!=DEP1;
       IF NB1>NB2 THEN
           RAISE_APPLICATION_ERROR(-20005,'Deux Courss ne peuvent pas se réunir dans la même salle en même temps.');
       END IF;
   END;