Databases
StackOverflow2013
Postsdbo
POHow to fix Oracle trigger causing recursion issue
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to fix Oracle trigger causing recursion issue
    primarykey
    data
    text
    <p>I don't know if I'm clear enough with my question. I have a database for a transport company that stores the date and time of all trips, I'm trying to prevent someone from reserving a ticket an hour before the scheduled departure time or buying a ticket after the departure date and time. I've tried creating a trigger for this but for some reason it does not allow me to create a ticket if it complies with the time requirements. It gives me an error </p> <pre><code>ERROR at line 1: ORA-00036: maximum number of recursive SQL levels (50) exceeded ORA-00036: maximum number of recursive SQL levels (50) exceeded </code></pre> <p>Here's the code for my trigger:</p> <pre><code>CREATE OR REPLACE TRIGGER validHour BEFORE INSERT OR UPDATE ON ticket FOR EACH ROW DECLARE x number; BEGIN select EXTRACT(DAY FROM (departure - sysdate)) * 1440 + EXTRACT(HOUR FROM (departure - sysdate)) * 60 + EXTRACT(MINUTE FROM (departure - sysdate)) into x from trip where trip.tripid=:new.tripid; IF :new.status = 'Reserved' AND x&lt;= 59 THEN raise_application_error(-20000,'You can only reserve an hour before departure'); ELSIF :new.status = 'Purchased' AND x&lt;= 0 THEN raise_application_error(-20000,'You can only purchase before departure'); ELSE INSERT INTO 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; / </code></pre> <p>If creating a trigger for this isn't the correct way, how else could I do this?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USEdna Ramirez
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COShould your before insert trigger be calling insert?
      singulars
      1. POHow to fix Oracle trigger causing recursion issue
      1. USmconlin
    2. CO`select (departure - sysdate) * 60 * 24 from trip` will give you an amount of minutes between these dates, no need to `extract`
      singulars
      1. POHow to fix Oracle trigger causing recursion issue
      1. USbe here now
    3. CO@beherenow I didn't know that! I struggled so much getting this value and I wish I knew it was as easy as this. Thanks for the info!
      singulars
      1. POHow to fix Oracle trigger causing recursion issue
      1. USEdna Ramirez
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload