CREATE OR REPLACE TRIGGER t_b_i_affecter
after INSERT ON affecter
FOR each ROW
DECLARE
type_cours creneau.typec%TYPE;
type_salle salle.tsal%TYPE;
BEGIN
    SELECT c.typec, s.tsal INTO type_cours, type_salle
    FROM salle s, creneau c
    WHERE c.debsemc = :NEW.debsemc
    AND c.jourc = :NEW.jourc
    AND c.heuredc = :NEW.heuredc
    AND c.grpc = :NEW.grpc
    AND s.nsalle = :NEW.salc;

    IF type_cours != type_salle THEN
        raise_application_error(-20001, 'La salle ne convient pas pour ce type d''enseignement');
    END IF;
END;


INSERT INTO affecter VALUES('05/06/17', 'mardi', '08:00', 'InS2F2', 'IN209'); --Déclanchement du TRIGGER
INSERT INTO affecter VALUES('05/06/17', 'mardi', '08:00', 'InS2F2', 'InJade'); --INSERTION correct0
ROLLBACK;
INSERT INTO affecter VALUES('05/06/17', 'mardi', '08:00', 'InSxx', 'IN209'); --Déclanchement du TRIGGER
INSERT INTO affecter VALUES('05/06/17', 'mardi', '08:00', 'InS2F2', 'INxxx'); --Déclanchement du TRIGGER

/* 5 - B */
DROP TRIGGER t_b_i_affecter; --Destruction du trigger

CREATE OR REPLACE TRIGGER t_b_i_affecter
BEFORE UPDATE ON affecter
FOR each ROW
DECLARE
type_cours creneau.typec%TYPE;
type_salle salle.tsal%TYPE;
BEGIN
    SELECT c.typec, s.tsal INTO type_cours, type_salle
    FROM salle s, creneau c
    WHERE c.debsemc = :NEW.debsemc
    AND c.jourc = :NEW.jourc
    AND c.heuredc = :NEW.heuredc
    AND c.grpc = :NEW.grpc
    AND s.nsalle = :NEW.salc;

    IF type_cours != type_salle THEN
        raise_application_error(-20001, 'La salle ne convient pas pour ce type d''enseignement');
    END IF;
END;

/* TEST DU trigger */

/*Déclanchement du TRIGGER */
UPDATE AFFECTER
SET SALC = 'IN209'
WHERE AFFECTER.DEBSEMC = '05/06/17'
AND AFFECTER.JOURC = 'vendredi'
AND AFFECTER.HEUREDC = '08:00'
AND AFFECTER.GRPC = 'InS2A1'
AND AFFECTER.SALC = 'InRubis';

/*Insertion CORRECT */
UPDATE AFFECTER
SET SALC = 'InJade'
WHERE AFFECTER.DEBSEMC = '05/06/17'
AND AFFECTER.JOURC = 'vendredi'
AND AFFECTER.HEUREDC = '08:00'
AND AFFECTER.GRPC = 'InS2A1'
AND AFFECTER.SALC = 'InRubis';

ROLLBACK;

/* 5 - C */
DROP TRIGGER t_b_i_affecter; --Destruction du trigger

CREATE OR REPLACE TRIGGER t_b_iu_affecter_cap
after INSERT OR UPDATE ON affecter
FOR each ROW
DECLARE
    effectif groupe.eff%TYPE;
    capacite salle.capacite%TYPE;
BEGIN
    SELECT effectif, capacite INTO effectif, capacite
    FROM groupe g, salle s
    WHERE g.grpc = :NEW.grpc
    AND s.nsalle = :NEW.salc;
    IF effectif > capacite THEN
      raise_application_error(-20002, 'La salle ne convient pas autant d''élèves');
    END IF;
END;

INSERT INTO creneau VALUES('17/07/17', 'mercredi', '17:00', 'CM', '18:30', 'InS2', 'InM2203');

INSERT INTO affecter(debsemc, jourc, heuredc, grpc, salc) VALUES('17/07/17', 'mercredi', '17:00', 'InS2', 'IR028');

ROLLBACK;

INSERT INTO creneau VALUES('17/07/17', 'mercredi', '17:00', 'CM', '18:30', 'InS2', 'InM2203');

INSERT INTO affecter (debsemc, jourc, heuredc, grpc, salc) VALUES('17/07/17', 'mercredi', '17:00', 'InS2', 'In203');

ROLLBACK;

INSERT INTO creneau VALUES('17/07/17', 'mercredi', '17:00', 'CM', '18:30', 'InS2', 'InM2203');

INSERT INTO affecter(debsemc, jourc, heuredc, grpc, salc) VALUES ('17/07/17', 'mercredi', '17:00', 'InS2', 'IR028');

UPDATE affecter SET salc = 'InJade'
WHERE affecter.debsemc = '24/04/17'
AND affecter.jourc = 'lundi'
AND affecter.heuredc = '15:30'
AND affecter.grpc = 'InS2'
AND affecter.salc = 'IR028';

ROLLBACK;


-- 5.d

CREATE OR REPLACE TRIGGER t_b_i_creneau
BEFORE INSERT ON creneau
FOR each ROW
DECLARE
    counts INT;
BEGIN
    SELECT COUNT(*) INTO counts
    FROM creneau
    WHERE debsemc = :NEW.debsemc
    AND jourc = :NEW.jourc
    AND grpc = :NEW.grpc
    AND (
        to_date(:NEW.heuredc, 'HH24:MI') BETWEEN to_date(heuredc, 'HH24:MI') AND to_date(heurefc, 'HH24:MI')
        OR to_date(:NEW.heurefc, 'HH24:MI') BETWEEN to_date(heuredc, 'HH24:MI') AND to_date(heurefc, 'HH24:MI')
        OR (to_date(:NEW.heuredc,  'HH24:MI') < to_date(heuredc, 'HH24:MI') AND to_date(:NEW.heurefc, 'HH24:MI') > to_date(heurefc, 'HH24:MI'))
    );

    IF counts <> 0 THEN
        raise_application_error(-20003, 'Un groupe ne peut pas avoir deux enseignements en même temps');
    END IF;
END;

INSERT INTO creneau VALUES('05/06/17', 'mardi', '08:05', 'TP', '09:30', 'InS2F2', 'InM2104');

INSERT INTO creneau VALUES('05/06/17', 'mardi', '07:45', 'TP', '13:00', 'InS2F2', 'InM2104');

ROLLBACK;

-- 6.

SELECT * FROM enseigner;

SELECT * FROM creneau;

SELECT * FROM enseignant;

SELECT * FROM nbhdispenseparenseignant;

SET serveroutput ON;

CREATE OR REPLACE TRIGGER t_b_i_enseigner
BEFORE INSERT OR UPDATE OR DELETE ON Enseigner
FOR each ROW
DECLARE
typeCours creneau.typec%TYPE;
heureFin creneau.heurefc%TYPE;
toAdd FLOAT;
BEGIN
    SELECT typec, heurefc INTO typeCours, heureFin
    FROM creneau
    WHERE debsemc = :NEW.debsemc
    AND jourc = :NEW.jourc
    AND heuredc = :NEW.heuredc
    AND grpc = :NEW.grpc;

    IF typeCours IN ('CM', 'TD', 'TP') THEN
        toAdd := (to_date(heureFin, 'HH24:MI') - to_date(:NEW.heuredc, 'HH24:MI')) * 24;
        IF typeCours = 'CM' THEN
            toAdd := toAdd * 1.5;
        END IF;

        IF DELETING THEN
            toAdd := -toAdd;
        END IF;

        UPDATE Enseignant
        SET nbhdisp = nbhdisp + toAdd
        WHERE idenseign = :NEW.ensc;
    END IF;
END;

SELECT * FROM enseignant WHERE idenseign = 'DBJ';

INSERT INTO enseigner VALUES('06/02/17', 'mardi', '15:30', 'InS4C', 'DBJ');

SELECT * FROM enseignant WHERE idenseign = 'DBJ';

ROLLBACK;

SELECT * FROM enseigner
WHERE debsemc = '06/02/17'
AND jourc = 'mardi'
AND heuredc = '15:30'
AND grpc = 'InS4C';

DELETE FROM enseigner
WHERE debsemc = '06/02/17'
AND jourc = 'mardi'
AND heuredc = '15:30'
AND grpc = 'InS4C'
AND ensc = 'DBJ';

SELECT * FROM enseignant WHERE idenseign = 'DBJ';

DELETE FROM enseigner
WHERE debsemc = '30/01/17'
AND jourc = 'vendredi'
AND heuredc = '09:30'
AND grpc = 'InS2F'
AND ensc = 'DBJ';

SELECT * FROM enseignant WHERE idenseign IN ('DBJ', 'EEI');

UPDATE enseigner
SET ENSC = 'EEI'
WHERE DEBSEMC = '14/11/16'
AND JOURC = 'vendredi'
AND HEUREDC = '08:00'
AND GRPC = 'InS3A2'
AND ENSC = 'DBJ';

SELECT * FROM enseignant WHERE idenseign IN ('DBJ', 'EEI');