ORA-04091:表正在变异,触发器/函数在执行Oracle触发器期间可能看不到它错误 [英] ORA-04091: table is mutating, trigger/function may not see it error during execution of oracle trigger
问题描述
我在下面有一个触发器,其中要在FIELD_NAME
字段中将值作为'Deactivation time of KPI in case of Downtime(Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION)'
插入到FIELD_TRACKING
表中.此字符串值中的括号部分来自KPI_DEFINITION
表的KPI_FREQ_TIME_UNIT
字段.因此,以下是我为此编写的触发器.触发器编译没有任何错误.但是,当我尝试更改KPI_DEFINITION
表中的DNTM_REAC_AFTER_HRS
字段时,我得到了错误ORA-04091: table RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION is mutating, trigger/function may not see it
ORA-04088: error during execution of trigger 'RATOR_MONITORING_CONFIGURATION.TRG_TRK_KPI_DEFINITION'
.
I have below trigger in which for FIELD_NAME
field i want to insert value into FIELD_TRACKING
table 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_UNIT
field of KPI_DEFINITION
table. 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_HRS
field from the KPI_DEFINITION
table then i am getting error ORA-04091: table RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION is mutating, trigger/function may not see it
ORA-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;
推荐答案
触发器无法读取表(从KPI_DEFINITION中选择KPI_FREQ_TIME_UNIT),此更改...您可以通过以下方式访问值::new.KPI_FREQ_TIME_UNIT. 更多信息: http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm
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
在其他情况下,您可以尝试在自主交易中进行操作:
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;
这篇关于ORA-04091:表正在变异,触发器/函数在执行Oracle触发器期间可能看不到它错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!