How to fix the mutating trigger in oracle

问题:

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

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

my trigger code is as follows:

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;

I tried to use the

pragma autonomous_transaction;

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

pls help me to fix this issue.

回答1:

instead of this update statement

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

use

:new.instantaneousinterval=resval;

回答2:

A mutating table occurs when a statement causes a trigger to fire and that trigger references the table that caused the trigger. The best way to avoid such problems is to not use triggers, but I suspect the DBA didn’t take the time to do that. He could have done one of the following:

Changed the trigger to an after trigger.

Changed it from a row level trigger to a statement level trigger.

Convert to a Compound Trigger.

Modified the structure of the triggers to use a combination of row and statement level triggers.

Made the trigger autonomous with a commit in it.

Try this pragma autonomous_transaction; with Commit

回答3:

Since the trigger is updating the same table on which it is defined, why don't you update the two columns in the first update statement itself?

i.e, Instead of using an update like

UPDATE pseb.metermaster
SET    assettype = '<v_assettype>';

and relying on trigger to update the instantaneousinterval column, why don't you use an update statement like the following (code is not tested)

UPDATE pseb.metermaster
SET    assettype = '<v_assettype>',
       instantaneousinterval = (SELECT CASE
                                           WHEN assettypename LIKE 'DT' THEN 86400
                                           WHEN assettypename LIKE 'HT' THEN 3600
                                           WHEN assettypename LIKE 'FSB' THEN 86400
                                       END
                               FROM   pseb.meterassetinsttype
                               WHERE  assettypeid = '<v_assettype>');

In my opinion, using a trigger and autonomous_transaction in this case would be a wrong approach. To know why this is wrong, please search http://asktom.oracle.com/ for this error.