ORA-04091: 表正在发生变化，在执行 oracle 触发器期间触发器/函数可能看不到它错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32716242/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-04091: table is mutating, trigger/function may not see it error during execution of oracle trigger
提问by Andrew
I have below trigger in which for FIELD_NAMEfield i want to insert value into FIELD_TRACKINGtable as 'Deactivation time of KPI in case of Downtime(Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION)'. The bracket part in this string value comes from KPI_FREQ_TIME_UNITfield of KPI_DEFINITIONtable. So below is the trigger i have wrritten for this. The trigger compile without any error. But when i try to change the DNTM_REAC_AFTER_HRSfield from the KPI_DEFINITIONtable then i am getting error ORA-04091: table RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION is mutating, trigger/function may not see itORA-04088: error during execution of trigger 'RATOR_MONITORING_CONFIGURATION.TRG_TRK_KPI_DEFINITION'.
我有下面的触发器，其中对于FIELD_NAME字段，我想将值FIELD_TRACKING作为'Deactivation time of KPI in case of Downtime(Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION)'. 该字符串值中的括号部分来自表的KPI_FREQ_TIME_UNIT字段KPI_DEFINITION。所以下面是我为此写的触发器。触发器编译没有任何错误。但是，当我尝试更改表中的DNTM_REAC_AFTER_HRS字段KPI_DEFINITION时，出现错误ORA-04091: table RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION is mutating, trigger/function may not see itORA-04088: error during execution of trigger 'RATOR_MONITORING_CONFIGURATION.TRG_TRK_KPI_DEFINITION'。
create or replace TRIGGER RATOR_MONITORING_CONFIGURATION."TRG_TRK_KPI_DEFINITION" AFTER UPDATE ON RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION
FOR EACH ROW
IF NOT :old.DNTM_REAC_AFTER_HRS=:new.DNTM_REAC_AFTER_HRS THEN
INSERT INTO RATOR_MONITORING_CONFIGURATION.FIELD_TRACKING (FIELD_TRACKING_ID,TABLE_NAME,TABLE_ID, FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,USER_ID, TIMESTAMP, FIELD_TRACKING_COMMENTS)
VALUES (FIELD_TRACKING_SEQ.NEXTVAL,'KPI_DEFINITION',:new.KPI_DEF_ID,'Deactivation time of KPI in case of Downtime'|| '(' || to_char((Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION)) || ')',to_char(:old.DNTM_REAC_AFTER_HRS),to_char( :new.DNTM_REAC_AFTER_HRS),:new.LAST_UPDATED_BY,:new.LAST_UPDATED_DATE, decode(:new.KPI_ACTIVE_DOWNTIME,'N','This KPI has been reactivated on end of a downtime.',''));
END IF;
END;
采纳答案by kinjelom
Trigger cannot read the table (Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION), that changes... you can access the value in this way: :new.KPI_FREQ_TIME_UNIT. More info: http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm
触发器无法读取表（从 KPI_DEFINITION 中选择 KPI_FREQ_TIME_UNIT），这会发生变化……您可以通过以下方式访问该值：:new.KPI_FREQ_TIME_UNIT。更多信息：http: //www.dba-oracle.com/t_avoiding_mutating_table_error.htm
In other cases you can try to do it in autonomous transaction:
在其他情况下，您可以尝试在自治事务中进行：
create or replace TRIGGER RATOR_MONITORING_CONFIGURATION."TRG_TRK_KPI_DEFINITION"
AFTER UPDATE ON RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- ...
COMMIT; -- don't forget it!!!
END;
回答by Ahmed MANSOUR
Since you need informations from the table for which you created your trigger for(Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION), you can get the KPI_FREQ_TIME_UNIT from the reference :NEWas it represents the new row (:NEW.KPI_FREQ_TIME_UNIT).
由于您需要来自为其创建触发器的表中的信息（从 KPI_DEFINITION 中选择 KPI_FREQ_TIME_UNIT），您可以从参考:NEW 中获取 KPI_FREQ_TIME_UNIT，因为它代表新行（:NEW.KPI_FREQ_TIME_UNIT）。
