title: PL/SQL: NO DATA FOUND while updating another table based on conditions
body: <p>So I have a column on my PAYMENT table that is called Status.. It has a foreign key of another table called reservation with Reservation_ID. The Reservation Table also has a status column and it will only get updated when there is a value in the status column of payment table. So If my status field in payment table has the value "Confirmed", the value for that particular Reservation_ID is supposed to turn to 1.. Otherwise 22. This is how I made the trigger: </p>
  
  <pre><code>CREATE OR REPLACE TRIGGER stats BEFORE INSERT OR DELETE OR UPDATE ON PAYMENT FOR EACH ROW
  DECLARE
  V_STATUS VARCHAR2(20);
  BEGIN
  SELECT Status INTO V_STATUS FROM PAYMENT INNER JOIN RESERVATION ON PAYMENT.Reservation_ID=RESERVATION.Reservation_ID WHERE PAYMENT.Reservation_ID=:NEW.Reservation_ID; 
  IF INSERTING AND V_STATUS='CONFIRMED' THEN
  UPDATE RESERVATION SET status=1 WHERE Reservation_ID=:new.Reservation_ID;
  ELSIF UPDATING AND V_STATUS='CONFIRMED'  THEN
  UPDATE RESERVATION SET status=1 WHERE Reservation_ID=:new.Reservation_ID;
  ELSE
  UPDATE RESERVATION SET status=22 WHERE Reservation_ID=:new.Reservation_ID;
  END IF;
  END;
  </code></pre>
  
  <p>So the trigger basically gets compiled but when I try inserting values inside Payment Table, I get the following error:</p>
  
  <pre><code>Error report -
  ORA-01403: no data found
  ORA-06512: at "ME.STATS", line 4
  ORA-04088: error during execution of trigger 'ME.STATS'
  </code></pre>
  
  <p>create statments for both tables: </p>
  
  <pre><code>CREATE TABLE RESERVATION(RESERVATION_id NUMBER(10) NOT NULL, MEMBER_ID NUMBER(10) CONSTRAINT RE_MEM_fk REFERENCES MEMBER(MEMBER_ID) ON DELETE SET NULL,status NUMBER(10) CONSTRAINT RES_status_fk REFERENCES STATUS(RESERVATION_status_id) ON DELETE SET NULL, CONSTRAINT PK_BOOK PRIMARY KEY(RESERVATION_id)); 
  CREATE TABLE PAYMENT(Payment_ID NUMBER(10) NOT NULL ,RESERVATION_id NUMBER(10) CONSTRAINT Pay_RES_fk REFERENCES RESERVATION(RESERVATION_id) ON DELETE SET NULL, TicketPrice NUMBER(10), ExtraFaciliFees Number(10),TOTAL_AMOUNT Number(10), PromotionalCode VARCHAR2(10), CONSTRAINT PK_PAY PRIMARY KEY(Payment_ID));
  </code></pre>