title: Using IF EXISTS (SELECT ...) in a BEFORE INSERT trigger (Oracle)
body: <p>The code I have doesn't work, Oracle tells me the trigger has been created with build errors. Apparently I can't get any more precise information about what the build error is...</p>
  
  <p>I really haven't done a lot of SQL before so I'm not that familiar with the syntax. I have a hunch it's my IF EXISTS (SELECT ...) THEN statement that Oracle doesn't like, I've been Googling for similar examples but I couldn't really find anything that worked in my situation.</p>
  
  <p>So about the code:</p>
  
  <ul>
  <li>"debut" is a date attribute (it means start)</li>
  <li>"fin" is another date attribute (it means end)</li>
  <li>I want to make sure the NEW line's dates don't overlap with any other line's in the table if these 2 lines have the same "numInfirmier" attribute.</li>
  <li>So I SELECT all the lines that have the same numInfirmier as the NEW line and overlapping dates.</li>
  <li><p>And IF anything EXISTS in that select, I raise an error.</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER chev_surv
  BEFORE INSERT OR UPDATE ON surveillance
  FOR EACH ROW
  BEGIN
      IF EXISTS (
          SELECT * FROM surveillance
          WHERE surveillance.numInfirmier = :NEW.numInfirmier
          AND ((surveillance.debut &gt; :NEW.debut AND surveillance.debut &lt; :NEW.fin)
          OR (surveillance.fin &gt; :NEW.debut AND surveillance.fin &lt; :NEW.fin))
      ) THEN
          RAISE_APPLICATION_ERROR(-20001,
          'Il ne doit pas y avoir de chevauchement entre deux périodes surveillance pour un surveillant.');
      END IF;
  END;
  /
  </code></pre></li>
  </ul>
  
  <p>Any idea what's wrong?</p>