CREATE OR REPLACE TRIGGER PruefeArbeiten
BEFORE
INSERT ON Arbeiten
FOR EACH ROW
DECLARE
  vThema Arbeiten.Thema%TYPE;
  vArbeitsid Arbeitstyp.ArbeitsID%TYPE;
  vDatum Arbeiten.Pruefungsdatum%TYPE;
  vMaxAnzahlMitglieder NUMERIC;
  vGruppennr NUMERIC;
  vAktAnzahlGruppenmitglieder NUMERIC;
  vArbeitExistiert NUMERIC;
  vArbeitstyp NUMERIC;
  vAktDatum DATE;
BEGIN
  vThema := :NEW.thema;
  vArbeitsID := :NEW.arbeitsid;
  vDatum := :NEW.pruefungsdatum;

  -- 1. Fall: Pruefen, ob das Thema bereits existiert mit anderem Pruefungsdatum und -art
  SELECT COUNT(*) INTO vArbeitExistiert FROM Arbeiten
  WHERE thema = vThema
  AND
    -- Unterschiedliche Arbeitsid und gleiches Pruefungsdatum
    (arbeitsid != vArbeitsid AND pruefungsdatum = vDatum)
    OR
    -- Gleiche Arbeitsid und unterschiedliches Pruefungsdatum
    (arbeitsid = vArbeitsid AND pruefungsdatum != vDatum);

  IF vArbeitExistiert >= 1 THEN
      raise_application_error(-20001, 'Thema darf nicht genutzt werden.');
  END IF;


  -- 2. Fall: Ein Student moechte sich zu einer bereits existierenden Arbeit hinzufuegen
  SELECT COUNT(*) INTO vArbeitExistiert FROM Arbeiten WHERE arbeitsid = vArbeitsID AND thema = vThema AND pruefungsdatum = vDatum;

  IF vArbeitExistiert >= 1 THEN
      -- Die Arbeit gibt es bereits. Muss zu einer Gruppe hinzugefuegt werden.

      -- Gruppe ermitteln
      SELECT Gruppennr INTO vGruppennr FROM Arbeiten WHERE arbeitsid = vArbeitsID AND thema = vThema AND pruefungsdatum = vDatum;
      -- Aktuelle Anzahl Mitglieder der Gruppe ermitteln
      SELECT COUNT(*) INTO vAktAnzahlGruppenmitglieder FROM Gruppe WHERE Gruppennr = vGruppennr;

      -- Arbeitstypen: 1 = Projektarbeit / 2 = Bachelorarbeit / 3 = Masterarbeit
      CASE vArbeitsID
        WHEN 1 THEN
          -- Projektarbeit, max. drei Mitglieder
          vMaxAnzahlMitglieder := 3;
        WHEN 2 THEN
          vMaxAnzahlMitglieder := 2;
        WHEN 3 THEN
          vMaxAnzahlMitglieder := 2;
      END CASE;

      IF vAktAnzahlGruppenmitglieder >= vMaxAnzahlMitglieder THEN
        -- Der Student darf der Gruppe nicht mehr beitreten
        raise_application_error(-20001, 'Maximale Anzahl an Gruppenmitgliedern erreicht.');
      END IF;
  END IF;

  -- Note darf erst eingetragen werden, wenn das aktuelle Dateum >= Pruefungsdatum ist
  IF :NEW.Note != NULL THEN
    SELECT CURRENT_DATE INTO vAktDatum FROM dual;
    IF vAktDatum < :NEW.Pruefungsdatum THEN
      raise_application_error(-20001, 'Pruefungsdatum noch nicht erreicht.');
    END IF;
  END IF;

END;