title: PLSQL: BEFORE INSERT TRIGGER (check value in column from other table before allowing insert)
body: <p>I've made a simple DVD store database. The DVD table has a column "status" which can be either 'FOR_RENT','FOR_SALE','RENTED',or 'SOLD'. I want to write a trigger to block any insertions into my RENTALS table if the status column in the DVD table is not set to 'FOR_RENT'.</p>
  
  <p>Much of the documents I've looked at generally don't show example using values from two different tables so I'm a bit flummaxed.</p>
  
  <p>This is what I believe has been my best attempt so far:</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER RENTAL_UNAVAILABLE
  BEFORE INSERT ON RENTAL;
  FOR EACH ROW
  WHEN (DVD.STATUS != 'FOR_RENT')
  DECLARE
  dvd_rented EXCEPTION;
  PRAGMA EXCEPTION_INIT( dvd_rented, -20001 );
  BEGIN
  RAISE dvd_rented;
  EXCEPTION
  WHEN dvd_rented THEN
  RAISE_APPLICATION_ERROR(-20001,'DVD has been rented');
  END;
  /
  </code></pre>
  
  <p>I'm getting this error:</p>
  
  <pre><code> ORA-00911: invalid character
  </code></pre>