title: ORACLE: compare two vales PL/SQL
body: <p>Hi i have the following script</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER DOUBLE_BOOKINGS
  BEFORE INSERT OR UPDATE ON BOOKING_SESSION
  FOR EACH ROW
  DECLARE
      session_date TIMESTAMP;
      session_room NUMBER;
  BEGIN
      SELECT start_session
          INTO session_date --- EXISTING SESSION
          FROM booking_session
      WHERE bk_room = :NEW.bk_room;
      IF session_date = :NEW.start_session THEN
      RAISE_APPLICATION_ERROR
      (-1000, 'Room is already booked');
      END IF;
  END;
  /
  </code></pre>
  
  <p>The script is suppose to match any new bookings with the already existing bookings for the same room and same date (to stop double bookings)</p>
  
  <p>However it isn't working correctly.</p>
  
  <pre><code>SQL&gt; INSERT INTO BOOKING_SESSION VALUES (45,TO_TIMESTAMP('18/03/2012 10:00', '
  INSERT INTO BOOKING_SESSION VALUES (45,TO_TIMESTAMP('18/03/2012 10:00', 'DD/MM
              *
  ERROR at line 1:
  ORA-04098: trigger 'U1146815.ROOM_BOOKED' is invalid and failed re-validation
  </code></pre>
  
  <p>The <code>start_session</code> is the database column name where i store the date and time of bookings and is a timestamp. the <code>booking_session</code> is the name of where the bookings are saved and the <code>bk_room</code> constains the number of the room.</p>
  
  <p>So before a booking is commited i am trying to match the <code>new</code> booking date with old booking dates for the same room and then show an error message.</p>
  
  <p>Update</p>
  
  <p>new error, seems is returning too many rows how can i do one at a time.</p>
  
  <pre><code>ERROR at line 1:
  ORA-01422: exact fetch returns more than requested number of rows
  ORA-06512: at "U1146815.DOUBLE_BOOKINGS", line 5
  ORA-04088: error during execution of trigger 'U1146815.DOUBLE_BOOKINGS'
  </code></pre>