在Oracle中的BEFORE INSERT触发器中使用IF EXISTS（SELECT ...）

Question

在Oracle中的BEFORE INSERT触发器中使用IF EXISTS（SELECT ...）

sqloracleselecttriggersexists

4

我有一段代码不起作用，Oracle告诉我触发器创建时出现了构建错误。很明显，我无法获得更精确的构建错误信息...

我之前并没有接触过太多的SQL，所以对语法不是很熟悉。我猜想可能是我的 IF EXISTS (SELECT ...) THEN 语句让 Oracle 不满意。我在 Google 上搜索了类似的示例，但在我的情况下没有找到可行的解决方案。

那么关于这段代码：

"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;
/

有什么问题吗？

- MademoiselleC

如果您的客户端支持，可以使用show errors获取有关实际错误的更精确信息，或者使用select * from user_errors where type = 'TRIGGER' and name = 'CHEV_SURV'。这适用于任何存储的PL/SQL。您是正确的，您不能在select条件之外使用exists。但即使您将其编译，也会出现表变异错误 - 您无法查询触发器所针对的表。 - Alex Poole

哇塞，这是一个好东西要知道XD。 - MademoiselleC

“show error” 表示我只能在 SQL 指令中使用 “EXISTS”…… 什么？ - MademoiselleC

你正在尝试在PL/SQL上下文中使用它；这是一种不同的语言（虽然通常有重叠）。你只能在子查询中使用它，例如 select ... from ... where exists (select ... from ...). Justin的答案向你展示了在这种情况下应该如何检查，以及为什么它仍然无法工作 *8-) - Alex Poole

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Justin Cave · Accepted Answer

首先，如果您正在使用SQL*Plus，在创建对象时收到编译错误的提示时，命令show errors将向您显示错误。

如果您运行了show errors，则会告诉您IF EXISTS不是有效的语法。您可以尝试这样做：

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或更高版本，则使用包含三个部分的复合触发器)。你需要创建一个带有主键值集合的包。在语句之前的触发器将初始化集合。行级触发器将向此集合插入插入和/或更新的行的主键。然后，在语句之后的触发器将遍历此集合并实现任何检查。然而，这需要很多操作，所以我通常不建议这样做。即使你让所有这些部分都正常工作，你的逻辑也无法在多用户环境中保护你。当你有多个用户同时访问系统时，完全可能一个用户插入一行，第二个用户插入另一行与重叠的范围，然后每个会话都提交。在这种情况下，两组触发器都将允许更改，但你仍将在表中留下违反要求的数据。物化视图，因为它是在提交时强制执行而不是在插入时执行，所以在多用户环境中运行得非常好。如果你想让触发器在多用户环境中起作用，你必须添加额外的逻辑来强制串行化，阻止第二个会话的 insert 运行，直到第一个会话提交或回滚。这增加了复杂性，降低了可伸缩性，并且，根据实现方式，可能会导致支持上的噩梦。