oracle插表报-1错误,异常表table1被突变，触发/功能可能无法看到它错误中的Oracle...

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的获取：

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

我以为只是这个错误发生在你正在更新其上触发试图更新的东西表。但在这里，我正在更新table1，触发器应该更新table2。 SO为什么是错误？

2013-05-28

Victor

+1

您是指AFTER UPDATE _OR_ DELETE？你的变量'id3'不正确，我不明白你为什么要检查'id_1'是否为空。你能解释一下你的表格结构和_exactly_你想在这里实现什么。我认为你所忽略的触发因素还有很多？也许你可以创建一个最小的_working_例子来演示你的问题？ –

+1

我想我会将来自table_1的选择与更新结合为一个单独的语句，但是我相信这是从table_1中选择的引起变异表错误的选项。 –