oracle-databaseplsqltriggersora-00911

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


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;