sql - Oracle 唯一约束 - 触发器检查新关系中的属性值

Question

您好，我在正确设置 sql 语法时遇到了问题。我想创建一个唯一约束来查看新添加的外键，查看新关联实体的某些属性以确定是否允许该关系。

CREATE or replace TRIGGER "New_Trigger"
AFTER INSERT OR UPDATE ON "Table_1" 
FOR EACH ROW
BEGIN
Select "Table_2"."number" 
(CASE "Table_2"."number" > 0
  THEN RAISE_APPLICATION_ERROR(-20000, 'this is not allowed');
END)
from "Table_1"
WHERE "Table_2"."ID" = :new.FK_Table_2_ID 
END;

编辑:APC 的回答非常全面，但让我觉得我做错了。

情况是我有一个具有不同权限级别的人员表，我想检查这些权限级别，例如用户“Bob”具有低权限，他试图成为需要高权限的部门主管，因此系统会阻止这种情况发生。

---

有一个后续问题提出了一个相关的场景，但使用了不同的数据模型。 Find it here .

Answer 1

因此，您要执行的规则是，如果 TABLE_2 中的某些列为零或更少，则 TABLE_1 只能引用 TABLE_2。嗯……先梳理一下触发逻辑再讨论规则。

触发器应该是这样的:

CREATE or replace TRIGGER "New_Trigger"
AFTER INSERT OR UPDATE ON "Table_1" 
FOR EACH ROW
declare
  n "Table_2"."number".type%;
BEGIN

    Select "Table_2"."number" 
    into n
    from "Table_2"
    WHERE "Table_2"."ID" = :new.FK_Table_2_ID; 

    if n > 0
    THEN RAISE_APPLICATION_ERROR(-20000, 'this is not allowed');
    end if;

END;

请注意，您的错误消息应包括一些有用的信息，例如 TABLE_1 主键的​​值，用于在表中插入或更新多行时。

---

你在这里试图做的是强制执行一种称为 ASSERTION 的约束。 ANSI 标准中指定了断言，但 Oracle 尚未实现它们。任何其他 RDBMS 都没有做到这一点。

断言是有问题的，因为它们是对称的。也就是说，该规则也需要在 TABLE_2 上执行。目前，您在 TABLE_1 中创建记录时检查规则。假设稍后某个用户更新了 TABLE_2.NUMBER，使其大于零:您的规则现在已被破坏，但您不会知道它已被破坏，直到有人对 TABLE_1 发出完全不相关的 UPDATE ，然后会失败。呸。

那么，怎么办？

如果规则实际上是

TABLE_1 can only reference TABLE_2 if TABLE_2.NUMBER is zero

然后您可以在没有触发器的情况下执行它。

1. 在 TABLE_2 上为 (ID, NUMBER) 添加 UNIQUE 约束；您需要额外的约束，因为 ID 仍然是 TABLE_2 的主键。
2. 在 TABLE_1 上添加一个名为 TABLE_2_NUMBER 的虚拟列。默认为零，并有一个检查约束以确保它始终为零。 (如果您使用的是 11g，则应考虑为此使用虚拟列。)
3. 更改 TABLE_1 的外键，以便(FK_Table_2_ID、TABLE_2_NUMBER)引用唯一约束而不是 TABLE_2 的主键。
4. 删除“New_Trigger”触发器；您不再需要它，因为外键会阻止任何人将 TABLE_2.NUMBER 更新为零以外的值。

但如果规则真的如我在顶部制定的那样，即

TABLE_1 can only reference TABLE_2 if TABLE_2.NUMBER is not greater than zero (i.e. negative values are okay)

那么您需要另一个触发器，这次是在 TABLE_2 上，以在规则的另一端强制执行它。

CREATE or replace TRIGGER "Assertion_Trigger"
BEFORE UPDATE of "number" ON "Table_2" 
FOR EACH ROW
declare
  x pls_integer;
BEGIN

    if :new."number"  > 0
    then
        begin
            Select 1 
            into x
            from "Table_1"
            WHERE "Table_1"."FK_Table_2_ID" = :new.ID
            and rownum = 1;

           RAISE_APPLICATION_ERROR(-20001, :new.ID
                 ||' has dependent records in Table_1');
        exception
           when no_data_found then 
               null; -- this is what we want
        end;

END;

如果 TABLE_2 中的记录引用了 TABLE_2.NUMBER，则此触发器不允许您将其更新为大于零的值。它仅在 UPDATE 语句触及 TABLE_2.NUMBER 时触发，以最大限度地减少执行查找对性能的影响。