M3106C BDA - TP2

/* Exercice n°1 */
  /* Question n°1 */
  CREATE OR REPLACE TRIGGER createControle BEFORE INSERT OR UPDATE OF TYPEC, DATEDEB, DATEFIN, NOTEMAX ON CONTROLE
  FOR EACH ROW
  BEGIN
    IF :NEW.DATEFIN IS NULL OR :NEW.TYPEC IN ('Test', 'Test rattrapage', 'Interrogation')
    THEN :NEW.DATEFIN := :NEW.DATEDEB;
    END IF;
    IF :NEW.NOTEMAX IS NULL
    THEN :NEW.NOTEMAX := 20;
    END IF;
  END;

  /* Question 2 */
  CREATE OR REPLACE TRIGGER calculNoteMaxDef BEFORE INSERT OR UPDATE OF NOTEDEF, NOTE, NOTEMAX ON NOTATION
  FOR EACH ROW
  DECLARE MAX := CONTROLE.NOTEMAX%TYPE;
  BEGIN
    SELECT NOTEMAX INTO MAX FROM CONTROLE WHERE :NEW.IDC := CONTROLE.IDC, :NEW.NOTEDEF := (:NEW.NOTE * 20)/MAX;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20001, 'Pas de controle ayant cet identifiant.')
  END;

/* Exercice n°2 */
  /* Question n°1 */
  CREATE OR REPLACE TRIGGER verifNoEmprunt BEFORE INSERT ON EMPRUNT
  FOR EACH ROW
  DECLARE TOTAL ABONNE.NBEMPRUNT%TYPE;
  BEGIN
    SELECT NBEMPRUNT INTO TOTAL FROM ABONNE WHERE IDA = :NEW.IDA;
    IF TOTAL >= 5
    THEN RAISE_APPLICATION_ERROR (-20001, 'Trop demprunts.');
    END IF;
  END;

  /* Question n°2 */
  CREATE OR REPLACE TRIGGER updateEmprunt AFTER INSERT OR REPLACE OF NBEMPRUNT ON ABONNE
  FOR EACH ROW
  DECLARE NB NUMBER(2);
  BEGIN
    IF DATERET IS NULL
    THEN NB := NB + 1;
    ELSE NB := NB - 1;
    END IF;
    UPDATE ABONNE SET NBEMPRUNT := NBEMPRUNT + NB WHERE IDA = :NEW.IDA;
  END;

  /* Question n°3 */
  CREATE OR REPLACE TRIGGER retourLivre AFTER UPDATE OF DATERET ON EMPRUNT
  FOR EACH ROW
  DECLARE
  CURSOR c_reserv IS SELECT * FROM RESERV WHERE CONTACT := 'NON' AND IDL IN (SELECT IDL FROM EXEMPLAIRE WHERE CODBAR = :=NEW.CODBAR) ORDER BY DATER;
  NOMA ABONNE.NOM%TYPE;
  PRENOMA ABONNE.PRENOM%TYPE;
  ADRESSEA ABONNE.ADRESSE%TYPE;
  TITREL LIVRE.TITRE%TYPE;
  CR c_reserv%ROWTYPE;
  BEGIN
    OPEN c_reserv;
    FETCH c_reserv INTO CR
    IF c_reserv%FOUND
    THEN SELECT NOM, PRENOM INTO NOMA, PRENOMA FROM ABONNE WHERE IDA = :CR.IDA;
         SELECT TITRE INTO TITREL FROM LIVRE WHERE IDL = :CR.IDL;
         AVERTIR(NOMA, PRENOMA, ADRESSEA, TITREL);
         UPDATE RESERV SET CONTACT := 'OUI' WHERE IDA = :CR.IDA AND IDL = :CR.IDL;
    ELSE CLOSE c_reserv;
    END IF;
  END;