"Exception when others then" ignores if clause?

348 views Asked by At

Hey guys I have this trigger:

create or replace TRIGGER T_MINDESTLAGERMENGE 
BEFORE INSERT OR UPDATE ON ET_LISTE
for each row
declare 
lagermenge number := 0;
minlagermenge number := 0;
menge_et number :=0;
BEGIN
  select count(*) into menge_et from lager where lager.a_nr = :new.et_nr;
  if menge_et = 0 then
      raise_application_error(-20222, 'Artikel nicht gefunden. Es wurde nachbestellt');
  end if;


  select lager.lagermenge into lagermenge from lager where :new.et_nr = lager.a_nr;
  select lager.mindestlagermenge into minlagermenge from lager where :new.et_nr = lager.a_nr;

  if (lagermenge - :new.et_menge) < 0 then
      raise_application_error(-20111, 'Nachbestellung IST NICHT MÖGLICH! bei art:' || :new.et_nr);
  elsif (lagermenge - :new.et_menge) < minlagermenge  then
      DBMS_OUTPUT.PUT_LINE('trigger works');
      raise_application_error(-20222, 'minmenge unterschritten. Es wurde nachbestellt');

  end if;
END;

And I have this procedure(s): https://pastebin.com/rM6cA6zc I have put the procedures in one file in pastebin so it could be more readable

So here is the problem. As you can see I throw an Exception in trigger and catch it in the procude "ermittle einzelteile" in "exception in others then". But here is the thing: As you can see before I print the exception i print "trigger works" just to be sure it was executed but it won't be handled in the procure.

I always get this result:

trigger works
ORA-20222: minmenge unterschritten. Es wurde nachbestellt
ORA-065
Artikel E300 wurde bereits nachbestellt
bin in excepion when others then-1
trigger works
pauftrag failed. error:ORA-20222: minmenge unterschritten. Es wurde nachbestellt
ORA-065

So it actually somehow ignores my ifs and elses and throws my trigger exception as an unhandled exception ("pauftrag failed. error:ORA-20222: minmenge unterschritten. Es wurde nachbestellt");

"pauftrag failed. error:....." should only appear if there is really an unknown exception or the exception -20111 which is the exception i throw.

0

There are 0 answers