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