Using IF EXISTS (SELECT ...) in an INSERT trigger (Oracle)

Question

The code I have does not work, Oracle reports that the trigger was created with build errors. Apparently, I cannot get more accurate information about what the assembly error is ...

I really haven’t done much SQL, so I am not familiar with the syntax. I have a hunch that this is my EX EXISTS (SELECT ...) THEN statement that Oracle does not like, I was googling for similar examples, but I could not find anything that worked in my situation.

So, about the code:

• “debut” is a date attribute (this means the start)
• "fin" is another date attribute (this means the end)
• I want to make sure that the new line dates do not intersect with any other row in the table if these two rows have the same numInfirmier attribute.
• So, I select all rows that have the same numInfirmier as the newline and overlapping dates.

• AND IF anything EXISTS in this choice, I am causing an error.

  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 > :NEW.debut AND surveillance.debut < :NEW.fin)
          OR (surveillance.fin > :NEW.debut AND surveillance.fin < :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;
  /
  

Any idea what is wrong?

Answer 1

Firstly, if you use SQL * Plus when creating an object and are informed that there are compilation errors, the command show errorswill show you errors.

If you started show errors, you would be told that the IF EXISTSsyntax is invalid. You could do something like

SELECT COUNT(*)
  INTO l_cnt
  FROM <<rest of query>>

IF( l_cnt > 0 )
THEN
  RAISE_APPLICATION_ERROR ...
END IF;

, , . surveillance surveillance ( , , , INSERT VALUES, ). , .

, , , , , , , .

, , , , . , , . .

, ( , 11.2 ). . before . , / . after after , . , , .

, , . , , , , , . , , . , , , . , , , , , insert , . , , , .