M3106C BDA - TP3

/* Première étape */
    /* Question 1 */
    CREATE OR REPLACE TRIGGER SUPMATENS
    BEFORE DELETE ON MODULE
    FOR EACH ROW
    DECLARE
        CODEENS ENSEIGNT.CODE % TYPE;
    BEGIN
        SELECT DISTINCT CODE INTO CODEENS FROM ENSEIGNT WHERE ENSEIGNT.CODE = :OLD.CODE;
        IF :OLD.CODE = CODEENS
        THEN
            RAISE_APPLICATION_ERROR( -20001, 'Supression impossible, module enseigné.' );
        END IF;
    END;

    /* Question 2 */
    CREATE OR REPLACE TRIGGER INSMATENS
    BEFORE INSERT ON ENSEIGNT
    FOR EACH ROW
    DECLARE
        CODEENS ENSEIGNT.CODE % TYPE;
    BEGIN
        SELECT DISTINCT CODE INTO CODEENS FROM MODULE WHERE MODULE.CODE = :NEW.CODE;
        IF :NEW.CODE != CODEENS
        THEN
            RAISE_APPLICATION_ERROR( -20001, 'Insertion impossible, module non enseigné.' );
        END IF;
    END;

/* Deuxième étape */
    /* Question 3 */
    CREATE OR REPLACE TRIGGER MOYNULL
    BEFORE INSERT OR UPDATE OF MOY_CC, MOY_TEST ON NOTATION
    FOR EACH ROW
    BEGIN
        IF :NEW.MOY_TEST IS NULL THEN :NEW.MOY_TEST := 0; END IF;
        IF :NEW.MOY_CC IS NULL THEN :NEW.MOY_CC := 0; END IF;
    END;

/* Troisième étape */
    /* Question 4 */
    CREATE OR REPLACE TRIGGER ETUNOTA
    BEFORE INSERT OR UPDATE ON NOTATION
    FOR EACH ROW
    DECLARE
        MATSUIVI NOTATION.CODE % TYPE;
    BEGIN
        SELECT DISTINCT CODE INTO MATSUIVI FROM ENSEIGNT WHERE ENSEIGNT.NUM_ET = :NEW.NUM_ET;
        IF :NEW.CODE != MATSUIVI THEN RAISE_APPLICATION_ERROR( -20001, 'Cet étudiant ne suit pas ce module.' ); END IF;
    END;

    /* Question 5 */
    CREATE OR REPLACE TRIGGER COEFCONTR
    BEFORE INSERT OR UPDATE ON MODULE
    FOR EACH ROW
    BEGIN
        IF (:NEW.COEFF_TEST + :NEW.COEFF_CC) != 100 THEN RAISE_APPLICATION_ERROR( -20001, 'Les coefficients ne sont pas égaux à 100.' ); END IF;
    END;

/* Quatrième étape */
    /* Question 6 */
    CREATE TABLE GROUPE(ANNEE NUMBER(2,0), NUMERO NUMBER(2,0), EFFECTIF NUMBER(3,0), CONSTRAINT pk_ANNEEGR PRIMARY KEY (ANNEE,NUMERO));

    DECLARE
        NB_GROUPE1 NUMBER(1,0);
        NB_GROUPE2 NUMBER(1,0);
        EFFECTIF GROUPE.EFFECTIF%TYPE;
        AUCUN_ETUDIANT EXCEPTION;
    BEGIN
        COMMIT;
        SELECT COUNT(DISTINCT GROUPE) INTO NB_GROUPE1 FROM ETUDIANT WHERE ANNEE = 1;
        SELECT COUNT(DISTINCT GROUPE) INTO NB_GROUPE2 FROM ETUDIANT WHERE ANNEE = 2;
        IF NB_GROUPE1 = 0 OR NB_GROUPE2 = 0 THEN RAISE AUCUN_ETUDIANT; END IF;
        FOR i IN 1 .. NB_GROUPE1
        LOOP
            SELECT COUNT(*) INTO EFFECTIF FROM ETUDIANT WHERE GROUPE = i AND ANNEE = 1;
            INSERT INTO GROUPE (ANNEE, NUMERO, EFFECTIF) VALUES (1, i, EFFECTIF);
        END LOOP;
        FOR i IN 1 .. NB_GROUPE2
        LOOP
            SELECT COUNT(*) INTO EFFECTIF FROM ETUDIANT WHERE GROUPE = i AND ANNEE = 2;
            INSERT INTO GROUPE (ANNEE, NUMERO, EFFECTIF) VALUES (2, i, EFFECTIF);
        END LOOP;
        COMMIT;
    EXCEPTION
        WHEN AUCUN_ETUDIANT THEN DBMS_OUTPUT.PUT_LINE('Il n''y a pas d''étudiant');
    END;

    /* Question 7 */
    CREATE OR REPLACE TRIGGER AUTOEFFECT
    AFTER INSERT OR UPDATE ON ETUDIANT
    FOR EACH ROW
    BEGIN
        INSERT INTO GROUPE (ANNEE, NUMERO, EFFECTIF) VALUES (:NEW.ANNEE, :NEW.GROUPE, 1);
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
        UPDATE GROUPE SET EFFECTIF = EFFECTIF + 1 WHERE ANNEE = :NEW.ANNEE AND NUMERO = :NEW.GROUPE;
        IF UPDATING THEN
        UPDATE GROUPE SET EFFECTIF = EFFECTIF - 1 WHERE ANNEE = :OLD.ANNEE AND NUMERO = :OLD.GROUPE;
        END IF;
    END;