adplus-dvertising

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

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 :

  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;
                                                
Partager ce cours avec tes amis :
Rédigé par ESSADDOUKI Mostafa
ESSADDOUKI
The education of the 21st century opens up opportunities to not merely teach, but to coach, mentor, nurture and inspire.