i don't know if i'm clear enough question. have database transport company stores date , time of trips, i'm trying prevent reserving ticket hour before scheduled departure time or buying ticket after departure date , time. i've tried creating trigger reason not allow me create ticket if complies time requirements. gives me error
error @ line 1: ora-00036: maximum number of recursive sql levels (50) exceeded ora-00036: maximum number of recursive sql levels (50) exceeded here's code trigger:
create or replace trigger validhour before insert or update on ticket each row declare x number; begin select extract(day (departure - sysdate)) * 1440 + extract(hour (departure - sysdate)) * 60 + extract(minute (departure - sysdate)) x trip trip.tripid=:new.tripid; if :new.status = 'reserved' , x<= 59 raise_application_error(-20000,'you can reserve hour before departure'); elsif :new.status = 'purchased' , x<= 0 raise_application_error(-20000,'you can purchase before departure'); else insert ticket(name, lastname, status, reservationid, cardnumber, tripid, seatnum) values(:new.name, :new.lastname, :new.status, :new.reservationid, :new.cardnumber, :new.tripid, :new.seatnum); end if; end; / if creating trigger isn't correct way, how else this?
that's because insert row within trigger. if don't raise error, let go. insert go on since trigger fired because of insert in first place. don't have insert again same data.
by inserting row in ticket, fire trigger again, etc. leads stack exception.
this:
if :new.status = 'reserved' , x<= 59 raise_application_error(-20001, 'you can reserve hour before departure'); elsif :new.status = 'purchased' , x<= 0 raise_application_error(-20002, 'you can purchase before departure'); end if; is enough. (i corrected error numbers)
Comments
Post a Comment