问Oracle 11g:插入前检查数据

EN

CREATE OR REPLACE TRIGGER insert_new_show
BEFORE INSERT ON show
FOR EACH ROW
DECLARE
   -- pragma autonomous_transaction;
   collisioni varchar(40);
   runtime INT;
   error1 EXCEPTION;
   error2 EXCEPTION;
BEGIN
   IF  :new.time < CURRENT_TIMESTAMP THEN
      RAISE error1;
   END IF;  

   SELECT runtime INTO runtime 
     FROM film 
    WHERE title = deref(:new.di_film).title;

   SELECT film.title INTO collisioni 
     FROM show JOIN film ON deref(show.di_film).title=film.title
    WHERE DEREF(room).nome = DEREF(:NEW.room).nome 
      AND ( (:new.time < show.time AND :new.time + runtime + 10 > show.time) 
            OR (:new.time > show.time 
                 AND :new.time < show.time + runtime + 10));

   IF NOT SQL%NOTFOUND THEN
      INSERT INTO show
       SELECT to_timestamp(:NEW.orario,'yyyy-mm-dd hh24:mi:ss')
            , :NEW.max_n_spot,:NEW.costo, REF(s), NULL, REF(f)
         FROM film f, room s
        WHERE f.title=deref(:NEW.di_film).title 
          AND s.nome=deref(:NEW.room).nome;     
   ELSE
      RAISE error2; 
   END IF;

EXCEPTION 
   WHEN error1 THEN
      RAISE_APPLICATION_ERROR(-20491,'Error');
   WHEN error2 THEN
      RAISE_APPLICATION_ERROR(-20491,'Error');

END;