CREATE OR REPLACE TRIGGER tr_diplbens
    INSTEAD OF INSERT OR UPDATE OR DELETE
    ON diplbens
    FOR EACH ROW
DECLARE
  --rc NUMBER := SQL%ROWCOUNT;
  fehlercheck1 NUMBER;
  hauptverantwortlich NUMBER := 1;
BEGIN
  --  0 Records ist unmöglich, weil  for each row trigger immer 1 record
  --IF rc = 0 THEN RAISE_APPLICATION_ERROR(-20001, 'No rows affected'); END IF;

  IF updating
  THEN

    UPDATE tdiplbens
    SET dib_isthauptverantwortlich = CASE :old.dib_isthauptverantwortlich
                                      WHEN 1 THEN 1
                                      ELSE :NEW.dib_isthauptverantwortlich
                                     END, -- man kann hauptverantwortlich nicht von 1 auf 0 setzen
        dib_stundenanzahl          = :NEW.dib_stundenanzahl,
        dib_diplomprojekt          = :NEW.dib_diplomprojekt,
        dib_benutzer               = :NEW.dib_benutzer
    WHERE dib_diplomprojekt = :old.dib_diplomprojekt AND dib_benutzer = :old.dib_benutzer;

    IF :old.dib_isthauptverantwortlich = 0 AND :NEW.dib_isthauptverantwortlich = 1
    THEN           -- neuer Hauptverantwortlicher wird gesetzt
      UPDATE tdiplbens
        SET DIB_ISTHAUPTVERANTWORTLICH = 0
      WHERE DIB_DIPLOMPROJEKT = :old.DIB_DIPLOMPROJEKT AND
            dib_benutzer IN (SELECT dib_benutzer
                               FROM tbenutzer inner join tdiplbens ON dib_benutzer = Ben_id
                              WHERE DIB_DIPLOMPROJEKT = :old.DIB_DIPLOMPROJEKT AND
                                    ben_Benutzerart = (SELECT ben_benutzerart FROM tbenutzer
                                                        WHERE ben_id = :old.dib_benutzer ) AND
                                    ben_id != :old.dib_benutzer
                            );
    END IF;

  END IF;



  IF inserting
  THEN
    INSERT INTO tdiplbens (dib_diplomprojekt, dib_benutzer, dib_isthauptverantwortlich, dib_stundenanzahl)
    VALUES(:NEW.dib_diplomprojekt, :NEW.dib_benutzer, :NEW.dib_isthauptverantwortlich, :NEW.dib_stundenanzahl);

    IF :NEW.dib_isthauptverantwortlich = 0
    THEN           -- neuer Hauptverantwortlicher wird gesetzt
      UPDATE tdiplbens
        SET DIB_ISTHAUPTVERANTWORTLICH = 0
      WHERE DIB_DIPLOMPROJEKT = :NEW.DIB_DIPLOMPROJEKT AND
            dib_benutzer IN (SELECT dib_benutzer
                               FROM tbenutzer inner join tdiplbens ON dib_benutzer = Ben_id
                              WHERE DIB_DIPLOMPROJEKT = :NEW.DIB_DIPLOMPROJEKT AND
                                    ben_Benutzerart = (SELECT ben_benutzerart FROM tbenutzer
                                                        WHERE ben_id = :NEW.dib_benutzer ) AND
                                    ben_id != :NEW.dib_benutzer
                            );
    END IF;
  END IF;

  IF deleting THEN

    IF :old.dib_isthauptverantwortlich = 1 THEN
      RAISE_APPLICATION_ERROR(-20002, 'Deleting a user with "hauptverantwortlichen" tag is not allowed');
    END IF;

    DELETE FROM tdiplbens
    WHERE dib_diplomprojekt = :old.dib_diplomprojekt AND dib_benutzer = :old.dib_benutzer;

  END IF;

END;
/

DROP PROCEDURE testTriggerDiplBens;

CREATE PROCEDURE testTriggerDiplBens AS
BEGIN
  INSERT INTO diplbens (dib_diplomprojekt, dib_benutzer, dib_isthauptverantwortlich, dib_stundenanzahl)
  VALUES (406, 464, 0, 10);
  COMMIT;

  DELETE FROM diplbens
  WHERE dib_diplomprojekt = 402 AND dib_benutzer = 457;
  COMMIT;

  UPDATE diplbens
  SET dib_isthauptverantwortlich = 1
  WHERE dib_diplomprojekt = 394 AND dib_benutzer = 443;
  COMMIT;

END;
/