CREATE TABLE reservation (
  reservation_id INTEGER,
  msg            INTEGER,
  CONSTRAINT reservation_pk PRIMARY KEY (reservation_id)
);

CREATE TABLE payment (
  payment_id      INTEGER,
  reservation_id  INTEGER,
  msg             VARCHAR2(15),
  CONSTRAINT reservation_id_fk FOREIGN KEY (reservation_id)
  REFERENCES reservation(reservation_id)
);

INSERT INTO reservation VALUES (1, NULL);
INSERT INTO reservation VALUES (2, NULL);
INSERT INTO reservation VALUES (3, NULL);
INSERT INTO reservation VALUES (4, NULL);

INSERT INTO payment VALUES (1, 1, 'unconfirmed');
INSERT INTO payment VALUES (2, 2, 'unconfirmed');
INSERT INTO payment VALUES (3, 3, 'unconfirmed');

COMMIT;

CREATE OR REPLACE TRIGGER status_trigger
BEFORE INSERT OR UPDATE
ON payment
FOR EACH ROW
DECLARE
    v_status VARCHAR(15);
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    SELECT p.msg
      INTO v_status
      FROM payment     p
      JOIN reservation r
        ON p.reservation_id = r.reservation_id
     WHERE p.reservation_id = :NEW.reservation_id;

    DBMS_OUTPUT.PUT_LINE(v_status);
END;
/

/*This line will run printing 'unconfirmed', because reservation_id 1 is already in the table*/
INSERT INTO payment VALUES (4, 1, 'confirmed');
/*This line will fail with NO_DATA_FOUND exception, because trigger tried to perfom a dirty read. */
INSERT INTO payment VALUES (5, 4, 'unconfirmed');