title: Oracle 11g: Check data before insert
body: <p>I have three tables, film, show and room. I want to insert new shows for a film in a given room and I thought I can do it with a trigger that ckecks if there are no time collisions with the exiting ones.</p>
  
  <p>I wrote this:</p>
  
  <pre><code>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 &lt; 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 &lt; show.time AND :new.time + runtime + 10 &gt; show.time) 
              OR (:new.time &gt; show.time 
                   AND :new.time &lt; 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;
  </code></pre>
  
  <p>If I don't use the "pragma autonomous_transaction", I get a <code>table s%s is mutating</code> error. But, of course with the transaction, the trigger can't see the <code>:new</code> values.
  I also thought to do the check with a CHECK CONSTRAINT, but I don't know if it works.
  Can you help me to find a solution, please?</p>