Question 17 user1

GRANT SELECT, UPDATE, DELETE, REFERENCES, ALTER ON employe2 TO i1b12b;
GRANT SELECT, UPDATE, DELETE, REFERENCES, ALTER ON service2 TO i1b12b;


CREATE OR REPLACE TRIGGER sum_duree BEFORE INSERT OR UPDATE OF duree ON i1b12b.copy_travail
DECLARE
rec_employe employe2%ROWTYPE;
BEGIN
SELECT * INTO rec_employe FROM employe2 e
WHERE (SELECT SUM(duree) FROM i1b12b.copy_travail t WHERE e.nuempl=t.nuempl)> e.hebdo;
RAISE_APPLICATION_ERROR (-20004,'Viol de la règle: La somme des durées de
travail dun employé ne peut exceder son temps de travail hebdomadaire');
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR (-20005,'Plusieurs erreurs: trop demployés ayant
une durée de travail supérieure à leur temps de travail hebdomadaire');
END;



/*2*/
CREATE OR REPLACE TRIGGER supprimer_employe after DELETE ON i1b12b.copy_travail
BEGIN
DELETE FROM i1b12b.copy_travail t WHERE t.nuempl NOT IN(SELECT nuempl FROM
i1b14b.Employe);
END;


/*Test trigger*/
INSERT INTO employe2 VALUES(4242, 'Luc', 20, 4, 1500);
INSERT INTO i1b12b.copy_travail VALUES(4242, 103, 10);
DELETE FROM employe2 WHERE NUEMPL=4242;