0

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.

TiZaLjubavNisiRodjena
  • 117
  • 1
  • 7
  • Pretty sure sqlcode -1 relates to the DUP_VAL_ON_INDEX exception. Are you inserting a duplicate value into a column with a unique index? – TurtlesAllTheWayDown Nov 13 '17 at 15:32
  • @TurtlesAllTheWayDown Well yeah. But when it happens i still always "catch" it with "exception in others then" and I print it. – TiZaLjubavNisiRodjena Nov 13 '17 at 19:19
  • This is wrong "IF SQLCODE = -20111 THEN". Sqlcode always returns the exact error code. In your case the error sqlcode is -1 and u have specified as -20111. Please change that logic to catch the correct error code and the code should work fine. Also please note that If and else are perfectly fine to be used in the PLSQL exception block. – Vimal Bhaskar Nov 14 '17 at 09:55

0 Answers0