Using IF EXISTS (SELECT ...) in a BEFORE INSERT trigger (Oracle)

Question

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...

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.

So about the code:

• "debut" is a date attribute (it means start)
• "fin" is another date attribute (it means end)
• 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.
• So I SELECT all the lines that have the same numInfirmier as the NEW line and overlapping dates.

• And IF anything EXISTS in that select, I raise 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's wrong?

Answer 1

First off, if you're using SQL*Plus, when you create an object and are told that there are compilation errors, the command show errors will show you the errors.

If you ran show errors, you'd be told that IF EXISTS is not valid syntax. You could do something like

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

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

Once you fix the compilation error, however, you'll end up with runtime errors. In a row-level trigger on surveillance, you cannot generally query surveillance (you can if all you are doing is an INSERT VALUES that is guaranteed to only insert a single row). If you do so, you'll get a mutating trigger error at runtime.

From a data model perspective, when you find yourself designing a table in which the valid data for a particular row depends on data stored in other rows of the same table, you've generally violated normalization principles and you're generally better served fixing the underlying data model.

If you're really determined to keep the data model, I'd prefer to create a materialized view that refreshes on commit which has data only for rows that violate your criteria. You can then put constraints on that materialized view that throw errors at commit time when your criteria are violated. This will require materialized view logs on your table.

If you really want to keep the data model and you want to enforce the logic with triggers, you'd need the classic three trigger solution (or a compound trigger with three parts if you're using 11.2 or later). You'd create a package with a collection of primary key values. A before statement trigger would initialize the collection. A row-level trigger would insert the primary keys of the rows that were inserted and/or updated into this collection. And then an after statement trigger would iterate over this collection and implement whatever checks you'd like. That's a lot of moving pieces, though, which is why I generally advise against it.

Plus, even if you get all these pieces working, your logic won't protect you in a multi-user environment. When you have multiple users hitting the system at the same time, it is completely possible that one user will insert a row, the second user will insert another row with an overlapping range, and then each session will commit. In that case, both sets of triggers will allow the change but you'll still be left with data in the table that violates your requirements. The materialized view, since it is enforced at commit time rather than at the time of the insert, will work properly in a multi-user environment. If you want the triggers to work in a multi-user environment, you'd have to further complicate them by adding additional logic that enforces serialization that would block the second session's insert from running until the first session either committed or rolled back. That adds complexity, reduces scalability, and depending on how it is implemented, can make for a support nightmare.