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

Question

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'.

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.

This is what I believe has been my best attempt so far:

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;
/

I'm getting this error:

 ORA-00911: invalid character

Solution

Try this - I have not complied the code, but should be good. In case you see any compilation issues let me know and post schema on sqlfiddle.com

CREATE OR REPLACE TRIGGER rental_unavailable
   BEFORE INSERT
   ON rental
   FOR EACH ROW
DECLARE
   dvd_rented   EXCEPTION;
   PRAGMA EXCEPTION_INIT (dvd_rented, -20001);
   n_count      NUMBER (1);
BEGIN
   SELECT COUNT (*)
     INTO n_count
     FROM dvd
    WHERE dvd_id = :NEW.dvd_id AND dvd.status = 'FOR_RENT' AND ROWNUM < 2;

   IF n_count > 0
   THEN
      RAISE dvd_rented;
   END IF;
EXCEPTION
   WHEN dvd_rented
   THEN
      raise_application_error (-20001, 'DVD has been rented');
END;

Comments

[Ben]

Maybe you could explain to the OP why this works? There's also no need at all for the exception handler; the point is to raise the exception.

[Sandeep]

OP was trying WHEN (DVD.STATUS != 'FOR_RENT') which is not the corrected way as Trigger cannot refer Other Tables in this way. To make that condition I have inserted COUNT check. For EXCEPTION block I know there is no need, but maybe he wanted to handle all error messages from EXCEPTION block like 'DVD has been rented'