异常的表table1正在变异，触发器/函数在Oracle中可能看不到它错误 [英] Unusual table table1 is mutating, trigger/function may not see it error in Oracle

问题描述

我有一个这样的触发器:(基本上是在更新表1中的列时，我会更新表2中的列)

I have a trigger like this: (Basically on update of a column in table1, I update a column in table 2)

CREATE OR REPLACE TRIGGER AAA   AFTER UPDATE 
   ON TABLE_1    REFERENCING NEW AS NEWROW OLD AS OLDROW
   FOR EACH ROW
WHEN (
NEWROW.DELETED ='Y' AND NEWROW.ID IN (41,43)
AND OLDROW.DELETED = 'N'
      )
DECLARE
id_1 number;
id_2 number;
id_3 number;
BEGIN

select id_1, id_2,id_3 into id_1,id_2,id_3  from  table_1 where id_1 = :NEWROW.id1 and id2 = some_other_row.id2;
if id_1 is  null
then

 update table2 set deleted = 'Y' ,   where table2.id_1 = id_1 and table2.id_2=id_2 and table2.id_3 = id_3;

   end if;

EXCEPTION
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END AAA;
/

当我更新table1时，我得到:

When I update table1 I get:

ORA-04091: table table1 is mutating, trigger/function may not see it 

我认为仅当您更新触发器要尝试在其上进行更新的表时，才会发生此错误.但是这里我要更新table1，触发器应该更新table2.那为什么是错误呢?

I thought this error happens only when you are updating the table on which the trigger is trying to update something. But here I am updating table1 and trigger is supposed to update table2. SO why is the error?

推荐答案

这是导致问题的SELECT语句.在触发器内部，您不能从同一表中进行选择.在您的示例中，您不需要/不能使用SELECT语句.您只需使用:newrow.id_1，:newrow.id_2和:newrow.id_3即可获取值.

It's the SELECT statement that is causing the problem here. Inside the trigger, you cannot SELECT from the same table. In your example, you don't need/can't use the SELECT statement. You can get the values by simply using :newrow.id_1, :newrow.id_2 and :newrow.id_3.

这篇关于异常的表table1正在变异，触发器/函数在Oracle中可能看不到它错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！