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

Considérez le schéma relationnel suivant. Un employé peut travailler dans plus d'un département; le champ pct_temps de la relation Travail indique le pourcentage de temps pendant lequel un employé donné travaille dans un service donné.

•  Employe(eid:integer, enom:string, age:integer, salaire:real)
•  Travail(#eid:integer, #depid:integer, pct_temps:integer)
•  Departement(depid:integer, budget:real, #responsable_id:integer)

Exprimez les exigences suivantes en utilisant des déclencheurs :

1. Les employés doivent gagner un salaire minimum de 1000$.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_1
   BEFORE INSERT OR UPDATE ON Employe FOR EACH ROW
   BEGIN
       IF :NEW.salaire < 1000 THEN
           RAISE_APPLICATION_ERROR(-20005,'Les employés doivent gagner un salaire minimum de 1000$.');
       END IF;
   END;
                                               

2. Le pourcentage total du temp de travail pour un employé doit être inférieur à 100%.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_2
   AFTER INSERT OR UPDATE ON Travail FOR EACH ROW
   DECLARE
       pct NUMBER;
   BEGIN
       SELECT SUM(pct_temps) INTO pct FROM Travail WHERE eid=:NEW.eid;
       IF pct >=100 THEN
           RAISE_APPLICATION_ERROR(-20005,'Le pourcentage total du temp de travail pour un employé doit être inférieur à 100');
       END IF;
   END;
                                               

3. Un responsable doit toujours avoir un salaire plus élevé que n'importe quel employé qu'il ou elle dirige.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_3
   AFTER INSERT OR UPDATE ON Travail FOR EACH ROW
   DECLARE
       SAL REAL;
   BEGIN
       SELECT E.salaire INTO SAL FROM Departement D INNER JOIN EMPLOYE E ON E.eid=D.responsable_id WHERE D.depid=:NEW.depid; 
       IF :NEW.salaire > SAL THEN
           RAISE_APPLICATION_ERROR(-20005,'Un responsable doit toujours avoir un salaire plus élevé');
       END IF;
   END;
                                               

4. Chaque fois qu'un employé reçoit une augmentation, le salaire du responsable doit être augmenté du même montant.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_4
   AFTER UPDATE ON EMPLOYE FOR EACH ROW
   WHEN NEW.salaire>OLD.salaire
   DECLARE
       DEP NUMBER;
       RESP NUMBER;
       DIFF REAL;
   BEGIN
       DIFF:=:NEW.salaire-:OLD.salaire;
       SELECT T.depid INTO DEP FROM Travail T INNER JOIN Employe E ON E.eid=T.eid WHERE E.eid=:NEW.eid;
       SELECT responsable_id INTO RESP FROM Departement WHERE depid=DEP;
       UPDATE Employe SET salaire=salaire+DIFF WHERE eid = RESP;
   END;
                                               

5. Chaque fois qu'un employé reçoit une augmentation, le salaire du responsable doit être augmenté au moins autant. De plus, chaque fois qu'un employé reçoit une augmentation, le budget du département doit être augmenté pour être supérieur à la somme des salaires des employés du département.  Voir la réponse 

   CREATE OR REPLACE TRIGGER quest_5
   AFTER UPDATE ON EMPLOYE FOR EACH ROW
   FOLLOWS quest_4
   WHEN NEW.salaire>OLD.salaire
   
   DECLARE
       DEP NUMBER;
       BUDG REAL;
       DIFF REAL;
       SOMME REAL;
   BEGIN
       DIFF:=:NEW.salaire-:OLD.salaire;
       SELECT T.depid INTO DEP FROM Travail T INNER JOIN Employe E ON E.eid=T.eid WHERE E.eid=:NEW.eid;
       SELECT budget INTO BUDG FROM Departement WHERE depid=DEP;
       SELECT SUM(E.salaire) INTO SOMME FROM Travail T INNER JOIN Employe E ON E.eid=T.eid WHERE T.depid=DEP; 
       IF SOMME>BUDG THEN
           UPDATE Employe SET salaire=salaire+(SOMME-BUDG) WHERE eid = RESP;
       END IF;
   END;