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中选择的引起变异表错误的选项。 –