plsql - PL SQL trigger doesnt work -

hey guys have little wuestion here have written trigger fires on insert or update of table bestellung , want change things when inserting or updating file in table want change think u can see code.

i working apex right , when try insert order throws me error table right being changed , trigger maybe doesnt see changes...

i appreciate kind of thanks!!!

create or replace trigger bestellschluss_iuar after insert or update on bestellung each row declare  v_date2 date; v_hour number;  begin  select bestelldatum + 2 v_date2 bestellung bestellid = :new.bestellid; select extract(hour to_timestamp(sysdate))into v_hour dual;         if :new.zieldatum null     if v_hour > 17         update bestellung         set zieldatum = bestelldatum + 3         bestellid = :new.bestellid;     else         update bestellung -- bestellung means order         set zieldatum = bestelldatum + 2 --and means deliverydate = orderdate +2         bestellid = :new.bestellid;     end if; elsif v_date2 > :new.zieldatum         raise_application_error(-22501, 'bestellungen für dieses datum nicht möglich da zu kurze lieferzeit'); elsif (v_date2 = :new.zieldatum) , v_hour > 18         raise_application_error(-22502, 'bestellungen um diese uhrzeit für diesen tag nicht mehr möglich'); end if; end;​ / 

i use before insert or update trigger instead of after trigger here. in before trigger, can access , change values of affected row before inserted or updated, assigning :new.columnname. sufficient you, querying/updating data in row trigger has fired for.

this avoids 'table xyz mutating; trigger/function may not see it' error, because no longer attempting query or update table during trigger updates it.

i've had go @ rewriting trigger before trigger. please note haven't tested works, other creating suitable table , verifying trigger compiles:

create or replace trigger bestellschluss_iubr   before insert or update on bestellung   each row declare    v_date2 date;   v_hour number;  begin   v_date2 := :new.bestelldatum + 2;   v_hour := extract(hour systimestamp);    if :new.zieldatum null     if v_hour > 17       :new.zieldatum := :new.bestelldatum + 3;     else       :new.zieldatum := :new.bestelldatum + 2;     end if;   elsif v_date2 > :new.zieldatum     raise_application_error(-22501, 'bestellungen für dieses datum nicht möglich da zu kurze lieferzeit');   elsif (v_date2 = :new.zieldatum) , v_hour > 18     raise_application_error(-22502, 'bestellungen um diese uhrzeit für diesen tag nicht mehr möglich');   end if; end; /