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 的答案非常全面，但是让我认为我的做法是错误的。

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

---

有一个后续问题提出了相关场景，但具有不同的数据模型。 在这里找到它< /a>.

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 只能引用 TABLE_2，如果
TABLE_2.NUMBER 为零

，那么您可以在没有触发器的情况下强制执行它。

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 只能引用 TABLE_2，如果
TABLE_2.NUMBER 不大于零（即负值也可以）

那么您需要另一个触发器（这次是在 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.NUMBER 被 TABLE_2 中的记录引用，则此触发器将不允许您将 TABLE_2.NUMBER 更新为大于零的值。仅当 UPDATE 语句触及 TABLE_2.NUMBER 时才会触发，以尽量减少执行查找对性能的影响。

Answer 2

不要使用触发器创建唯一约束或外键约束。 Oracle 对唯一键和外键有声明性支持，例如：

在列上添加唯一约束：

ALTER TABLE "Table_1" ADD (
  CONSTRAINT table_1_uk UNIQUE (column_name)
);

添加外键关系：

ALTER TABLE "ChildTable" ADD (
  CONSTRAINT my_fk FOREIGN KEY (parent_id)
    REFERENCES "ParentTable" (id)
);

我不清楚您到底想用触发器实现什么目标 - 这有点混乱SQL 和 PL/SQL 混合在一起是行不通的，并且似乎引用了 "Table_2" 上的一列，而该列实际上并未被查询。

一个好的经验法则是，如果您的触发器正在查询触发器所在的同一个表，那么它可能是错误的。

我不确定，但是您是否在追求某种有条件的外键关系？即“仅允许父行满足条件 x 的子行”？如果是这样，则问题出在数据模型中，应该在那里修复。如果您对您想要实现的目标提供更多解释，我们应该能够为您提供帮助。