CREATE OR REPLACE TRIGGER trig_employe BEFORE INSERT OR UPDATE ON EMPLOYE FOR each ROW
DECLARE
    com FLOAT;

BEGIN
    SELECT AVG(commission)
    INTO com
    FROM EMPLOYE;

    IF :NEW.fonction = 'Vendeur' AND :NEW.commission > :NEW.salaire/2
    THEN
        :NEW.salaire := :NEW.salaire + 0.1 * :NEW.salaire;
    END IF;
    IF :NEW.date_embauche < TO_DATE('01/01/2002', 'dd/mm/yyyy') AND :NEW.commission IS NULL
    THEN
        :NEW.commission := com;
    END IF;
END;
.


############################################################################

############################################################################


CREATE OR REPLACE TRIGGER update_nbEmp AFTER INSERT ON EMPLOYE FOR EACH ROW
BEGIN
    UPDATE SERVICE
    SET nbEmployes = nbEmployes + 1
    WHERE serNo = :NEW.serNo;
END;
.