oracle - PLSQL: BEFORE INSERT TRIGGER (check value in column from other table before allowing insert) -

i've made simple dvd store database. dvd table has column "status" can either 'for_rent','for_sale','rented',or 'sold'. want write trigger block insertions rentals table if status column in dvd table not set 'for_rent'.

much of documents i've looked @ don't show example using values 2 different tables i'm bit flummaxed.

this believe has been best attempt far:

create or replace trigger rental_unavailable before insert on rental; 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 raise_application_error(-20001,'dvd has been rented'); end; / 

i'm getting error:

 ora-00911: invalid character 

try - have not complied code, should good. in case see compilation issues let me know , post schema on sqlfiddle.com

create or replace trigger rental_unavailable    before insert    on rental    each row declare    dvd_rented   exception;    pragma exception_init (dvd_rented, -20001);    n_count      number (1); begin    select count (*)      n_count      dvd     dvd_id = :new.dvd_id , dvd.status = 'for_rent' , rownum < 2;     if n_count > 0          raise dvd_rented;    end if; exception    when dvd_rented          raise_application_error (-20001, 'dvd has been rented'); end;