sql - 通过执行触发器自动插入其他表中的最大值

Question

我创建了两个示例表 test1 和 test2。

test1 有三列：test_id (PK)、test_count、test_desc。
test2 有两列：test_count (PK)、test_desc。

从 test2 表中，我想检索列test_count的最大值，并且我希望在每次插入语句执行期间将该值插入到 test1 表中，如下所示：

insert into test1 (test_id, test_desc) values (1,'blahblahblah');

该值应由以下触发器执行自动插入：

CREATE OR REPLACE TRIGGER maxTestCount
after insert on test1
FOR EACH ROW 
DECLARE
  varMaxCount number(10);
BEGIN 
  Select max(test_count) INTO varMaxCount
  From test2;
  Insert Into test1 (test_count) values (varMaxCount);
END;

由于以下错误，无法执行：

SQL 错误： ORA-04091：表 MMDMANAGER.TEST1 正在变异，触发器/函数可能看不到它
ORA-06512：在“MMDMANAGER.MAXTESTCOUNT”，第 6 行
ORA-04088：执行触发器 'MMDMANAGER.MAXTESTCOUNT' 04091 期间出错。 00000 - “表 %s.%s 正在发生变化，触发器/函数可能看不到它”
*原因： 触发器（或此语句中引用的用户定义的 plsql 函数
）试图查看（或修改）一个表正在
被触发它的语句修改。
*操作： 重写触发器（或函数），使其不读取该表。

你能给我建议如何避免这个错误吗？

标签： sqloracleplsql

Answer 1

您永远不能尝试修改触发器正在处理的记录，但是您可以为伪记录分配值，触发器将毫无问题地使用这些记录。但这在触发事件是Before而不是在 时有效after。

CREATE OR REPLACE TRIGGER maxTestCount
before insert on test1
FOR EACH ROW 
DECLARE
  varMaxCount number(10);

BEGIN 

  Select max(test_count) INTO varMaxCount
  From test2;

    :new.test_count := varMaxCount;
END;
/

请参阅 DBFIDDLE 链接中的完整解决方案https://dbfiddle.uk/?rdbms=oracle_18&fiddle=abb1c455a0be4b83ad898a2e334db153

测试结果如下：