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

Soit le schéma relationnel suivant :

•  Departement (NomD, N_Dep, Directeur)
•  Employe (Matricule, Nom, Prénom, DateNaissance, Adresse, Salaire, #N_dep, #superieur)
•  Projet (NomP, N_pro, Lieu, #N_Dep)
•  Travaille (#Matricule, #N_Proj, Heures)

Exprimez les exigences suivantes en utilisant des triggers:

1. un employé ne peut pas travailler sur un projet qui n'appartient pas à son département  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_1
   AFTER INSERT OR UPDATE ON Travaille FOR EACH ROW
   DECLARE
       DEP1 NUMBER;
       DEP2 NUMBER;
   BEGIN
       SELECT N_Dep INTO DEP1 FROM Projet WHERE N_Proj=:NEW.N_Proj;
       SELECT N_Dep INTO DEP2 FROM Employe WHERE Matricule=:NEW.Matricule;
       IF DEP1 !=DEP2 THEN
           RAISE_APPLICATION_ERROR(-20005,'un employé ne peut pas travailler sur un projet qui n’appartient pas à son département');
       END IF;
   END;
                                               

2. Supposons qu'il existe une règle dans l'entreprise stipulant que le salaire d'un employé ne peut pas être modifié de plus de 20% du salaire initial. Créez un trigger 'Suivi_changements_salaire' pour faire respecter cette contrainte.  Voir la réponse 

   CREATE OR REPLACE TRIGGER Suivi_changements_salaire
   AFTER UPDATE ON EMPLOYE FOR EACH ROW
   BEGIN
       IF ((:NEW.Salaire/:OLD.Salaire) >= 1.2) OR ((:OLD.Salaire/:NEW.Salaire) >= 1.2) THEN
           RAISE_APPLICATION_ERROR(-20002, 'le salaire d’un employé ne peut pas être modifié de plus de 20% du salaire initial');
       END IF;
   END;
                                               

3. Une fois qu'un département est créé, nous ne pouvons pas changer son nom ou le supprimer.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_3
   BEFORE UPDATE of NomD OR DELETE ON Departement
   BEGIN
       RAISE_APPLICATION_ERROR (-20501, 'Une fois qu’un département est créé, nous ne pouvons pas changer son nom ou le supprimer');
   END;
                                               

4. Un employé ne peut pas travailler plus de 200 heures.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_4
   AFTER INSERT OR UPDATE ON Travaille FOR EACH ROW
   DECLARE
       SOMME NUMBER;
   BEGIN
       SELECT SUM(Heures) INTO SOMME FROM Travaille WHERE Matricule=:NEW.Matricule;
       IF SOMME > 200 THEN
           RAISE_APPLICATION_ERROR(-20005,'Un employé ne peut pas travailler plus de 200 heures.');
       END IF;
   END;