trigger

CREATE OR REPLACE TRIGGER zugang_trigger
BEFORE INSERT OR UPDATE ON Standortzugriff
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
  DECLARE
  not_authorized_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(not_authorized_exception, -20101);
    var1 INTEGER;
  var2 INTEGER;
  BEGIN
  SELECT sstufe INTO var1 FROM Standort WHERE Standort.Raumnr = :NEW.st_raum;
  SELECT sfreigabe INTO var2 FROM Mitarbeiter WHERE Mitarbeiter.Persnr = :NEW.st_persnr;
  IF (var2 < var1) THEN
    RAISE not_authorized_exception;
    END IF;
    EXCEPTION
      WHEN not_authorized_exception THEN
           RAISE_application_error(-20101, 'Mitarbeiter hat keinen Zugang zu diesem Raum');
  END IF;
  END;
  /