SQL 在 BEFORE INSERT 触发器中使用 IF EXISTS (SELECT ...) (Oracle)

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

提问by MademoiselleC

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

我的代码不起作用，Oracle 告诉我触发器已创建，但存在构建错误。显然我无法获得有关构建错误是什么的更准确信息......

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.

我之前真的没有做过很多SQL，所以我对语法不是很熟悉。我有一种预感，Oracle 不喜欢我的 IF EXISTS (SELECT ...) THEN 语句，我一直在谷歌搜索类似的例子，但我真的找不到任何适合我的情况的东西。

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 avtheitroad de chevauchement entre deux périodes surveillance pour un surveillant.');
      END IF;
  END;
  /
  

• “debut”是一个日期属性（它意味着开始）
• “fin”是另一个日期属性（它意味着结束）
• 如果这两行具有相同的“numInfirmier”属性，我想确保新行的日期不与表中的任何其他行重叠。
• 所以我选择所有与新行和重叠日期具有相同 numInfirmier 的行。

• 如果该选择中存在任何内容，我会引发错误。

  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 avtheitroad de chevauchement entre deux périodes surveillance pour un surveillant.');
      END IF;
  END;
  /
  

Any idea what's wrong?

知道出了什么问题吗？

回答by Justin Cave

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

首先，如果您使用 SQL*Plus，当您创建一个对象并被告知存在编译错误时，该命令show errors将显示错误。

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

如果你跑了show errors，你会被告知这IF EXISTS是无效的语法。你可以做类似的事情

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 VALUESthat is guaranteed to only insert a single row). If you do so, you'll get a mutating trigger error at runtime.

但是，一旦修复了编译错误，您最终会遇到运行时错误。在 上的行级触发器中surveillance，您通常不能查询surveillance（如果您所做的INSERT VALUES只是保证只插入单行，则可以查询）。如果你这样做，你会在运行时得到一个突变的触发器错误。

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.

如果您真的想要保留数据模型并且想要使用触发器来强制执行逻辑，那么您需要经典的三触发器解决方案（如果您使用的是 11.2 或更高版本，则需要一个包含三部分的复合触发器）。您将创建一个包含主键值集合的包。before 语句触发器将初始化集合。行级触发器将插入插入和/或更新到此集合中的行的主键。然后一个 after 语句触发器将迭代这个集合并实现你想要的任何检查。不过，这是很多动人的部分，这就是为什么我通常建议不要这样做。

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

另外，即使您让所有这些部分都正常工作，您的逻辑也不会在多用户环境中保护您。当您有多个用户同时访问系统时，完全有可能一个用户插入一行，第二个用户插入具有重叠范围的另一行，然后每个会话都会提交。在这种情况下，两组触发器都将允许更改，但您仍然会在表中留下违反您要求的数据。物化视图，因为它是在提交时而不是在插入时强制执行的，所以将在多用户环境中正常工作。如果您希望触发器在多用户环境中工作，则必须通过添加强制序列化的附加逻辑使它们进一步复杂化，从而阻止第二个会话的insert从运行到第一个会话提交或回滚。这增加了复杂性，降低了可扩展性，并且取决于它的实现方式，可能会导致支持噩梦。