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)
Recommandé : Veuillez d'abord résoudre l'exercice, avant de passer à la solution.
Travail à faire
Exprimez les exigences suivantes en utilisant des déclencheurs :
- 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;
- 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;
- 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;
- 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;
- 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;
- 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;
- 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;
Partager ce cours avec tes amis :
Rédigé par
ESSADDOUKI
Mostafa