如何在Oracle中修复变异触发器 [英] How to fix the mutating trigger in oracle

问题描述

我写了触发器来更新同一张表中的列值.例如，在资产类型列更新后，我在metermaster表上编写了一个触发器，在触发器中，我试图更新同一metermaster表中的瞬时间隔列.它会抛出这样的错误

I wrote the trigger for updating the column value in the same table. For Ex I wrote a trigger on metermaster table after update of assettype column , with in the trigger i am trying to update the instantaneousinterval column in the same metermaster table. Its throws the error like this

错误:ORA-04091:表PSEB.METERMASTER正在变异，触发器/功能 可能看不到.

ERROR: ORA-04091: table PSEB.METERMASTER is mutating, trigger/function may not see it.

我的触发代码如下:

CREATE OR REPLACE TRIGGER PSEB.spiupdate
AFTER
update of assettype
ON pseb.metermaster
referencing new as new old as old
    for each row
DECLARE
vassettype number;
resval number(10);
vassettypename varchar2(50);
vmeterid number;
begin
   select :new.assettype,:new.meterid INTO vassettype,vmeterid from dual;
   select assettypename into vassettypename from pseb.METERASSETINSTTYPE where ASSETTYPEID=vassettype;
    select case when assettypename  like 'DT' then 86400 when assettypename  like 'HT' then 3600 when assettypename  like 'FSB' then 86400 end  into resval from pseb.meterassetinsttype where assettypename =vassettypename; 
    update pseb.metermaster set instantaneousinterval=resval where meterid=vmeterid;
end;

我尝试使用

pragma autonomous_transaction;

但是它给出了死锁条件. 错误:ORA-00060:等待资源时检测到死锁 ORA-06512:

but it gives the deadlock condition. ERROR: ORA-00060: deadlock detected while waiting for resource ORA-06512:

请帮助我解决此问题.

推荐答案

代替此更新语句

update pseb.metermaster set instantaneousinterval=resval where meterid=vmeterid;

使用

:new.instantaneousinterval=resval;

这篇关于如何在Oracle中修复变异触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！