删除时外键发生变异表错误

Question

在我们的数据库中，我们有一个表，其中的记录通过 id 从大约 4 个其他表中引用。这些“子”表具有“主”表的外键，并且“删除时设置为空”。所有表都有一个变异表系统（即：与 plsql-table 一起封装，当从语句触发器调用过程时处理记录）。 然而，在删除主表中的记录后，子记录会给出“表正在发生变化”错误。我觉得这有点奇怪，因为外键似乎触发了一些隐式更新语句，该语句落在 plsql 表中。

我所追求的只是试图找出原因，我似乎无法挖掘出一些相关信息！ 当然，我们确实有一个解决方案，只需在主表的语句后触发器中将关联表中引用的 id 字段设置为 null，但我仍然想知道为什么会发生这种情况。

重现错误的代码：

CREATE TABLE master_table (ID NUMBER(5) NOT NULL);
CREATE TABLE child_table (ID NUMBER(5) NOT NULL, master_id NUMBER(5));

alter table master_table add constraint master_pk primary key (ID);

alter table child_table add constraint child_pk primary key (ID);

ALTER TABLE child_table
  add constraint on_delete_master foreign key (master_id)
  references master_table (ID) on delete set null;

CREATE OR REPLACE PACKAGE pkg_child
IS
PROCEDURE init_temp;
PROCEDURE add_temp(i_action IN VARCHAR2, 
                   i_master_old IN child_table.master_id%TYPE, 
                   i_master_new IN child_table.master_id%TYPE);
PROCEDURE process_temp;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_child IS
   TYPE temp_record IS RECORD(
      action        VARCHAR2(1),
      old_master_id child_table.master_id%TYPE,
      new_master_id child_table.master_id%TYPE);

   TYPE type_temp IS TABLE OF temp_record INDEX BY BINARY_INTEGER;

   tab_temp type_temp;

   PROCEDURE init_temp IS
   BEGIN
      tab_temp.delete;
   END;

   PROCEDURE add_temp(i_action     IN VARCHAR2,
                      i_master_old IN child_table.master_id%TYPE,
                      i_master_new IN child_table.master_id%TYPE) IS
      v_id BINARY_INTEGER;
   BEGIN
      v_id := nvl(tab_temp.last, 0) + 1;
      tab_temp(v_id).action := i_action;
      tab_temp(v_id).old_master_id := i_master_old;
      tab_temp(v_id).new_master_id := i_master_new;
   END;

   PROCEDURE process_temp IS
      v_id    BINARY_INTEGER;
      v_total NUMBER;
   BEGIN
      v_id := tab_temp.first;
      WHILE v_id IS NOT NULL LOOP
         IF tab_temp(v_id).action = 'U' THEN
            SELECT COUNT(1)
              INTO v_total
              FROM child_table;
         END IF;
         v_id := tab_temp.next(v_id);
      END LOOP;
   END;
END;
/
CREATE OR REPLACE TRIGGER child_table_bs
 BEFORE 
 INSERT OR UPDATE OR DELETE
 ON child_table
 REFERENCING OLD AS OLD NEW AS NEW
BEGIN
  pkg_child.init_temp;
END;
/
CREATE OR REPLACE TRIGGER child_table_ar
 AFTER 
 INSERT OR DELETE OR UPDATE
 ON child_table
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
DECLARE
   v_action VARCHAR2(1);
BEGIN
   IF inserting THEN
      v_action := 'I';
   ELSIF updating THEN
      v_action := 'U';
   ELSIF deleting THEN
      v_action := 'D';
   END IF;
   pkg_child.add_temp(v_action, :old.id, :new.id);
END;
/
CREATE OR REPLACE TRIGGER child_table_as
 AFTER 
 INSERT OR UPDATE OR DELETE
 ON child_table
 REFERENCING OLD AS OLD NEW AS NEW
BEGIN
 pkg_child.process_temp;
END;
/

INSERT ALL 
   INTO master_table (id) VALUES (1) 
   INTO master_table (id) VALUES (2) 
   INTO master_table (id) VALUES (3) 
   INTO master_table (id) VALUES (4)
SELECT * FROM dual;

INSERT ALL
   INTO child_table (id, master_id) VALUES (1, NULL) 
   INTO child_table (id, master_id) VALUES (2, 1) 
   INTO child_table (id, master_id) VALUES (3, 2) 
   INTO child_table (id, master_id) VALUES (4, NULL)
SELECT * FROM dual;

-- error on this delete: mutating tables
-- why?
DELETE FROM master_table
 WHERE id = 2;

清理代码：

DROP TRIGGER child_table_bs;
DROP TRIGGER child_table_ar;
DROP TRIGGER child_table_as;
DROP PACKAGE pkg_child;
DROP TABLE child_table;
DROP TABLE master_table;

谢谢

Answer 1

您有一个语句，即主表中的 DELETE 语句，它可以影响多行。
由于 CASCADE 约束，每个删除的行都将触发 CHILD 表的隐式/递归 UPDATE 语句。也就是说，理论上您可以对子表进行多次更新。

假设您执行了 DELETE FROM master_table WHERE id in (1, 2)

这将生成两个 UPDATE child_table 语句。其中每个触发器都会尝试执行 AFTER UPDATE 触发器，因此您将获得两次执行

SELECT COUNT(1)
INTO v_total
FROM child_table

。单个 DELETE 语句下的任何 SELECT 的结果在特定时间点应该是一致的。但是 SELECT 并不是在 DELETE 结束时发生，而是在删除期间执行多次，每次都可能产生不同的结果。 Oracle 可以计算出您想要/期望的结果，因此会抛出变异表错误。

在不了解业务需求的情况下，很难推荐解决方案。与 Oracle 一样，我们不知道您想做什么。这种情况可能可以通过在事务结束时执行的 ON-COMMIT MV 或 DBMS_JOB 来解决。