how to solve the error 'ORA-04091: table WKSP_APEXHENRY.TEST1 is mutating, trigger/function may-CodePudding

I create a trigger with oracle apex. And I created an App with oracle apex. The code is as follows:

CREATE TABLE test1
(
  test1data1 INT NOT NULL,
  test1data2 INT NOT NULL,
  test1key VARCHAR(20) NOT NULL,
  PRIMARY KEY (test1key)
);
insert into test
values(10,10,001);

CREATE OR REPLACE EDITIONABLE TRIGGER  "TEST1_T1" 
AFTER
update on "TEST1"
for each row
begin
update test1
set test1data1 = :new.test1data2-:old.test1data2;
end;
/
ALTER TRIGGER  "TEST1_T1" ENABLE
/

I use App Builder on APEX to create a website page. When I change the value of 'test1data2', it shows that

ORA-04091: table WKSP_APEXHENRY.TEST1 is mutating, trigger/function may not see it'.

How to solve this problem? Thank you.

[[screenshot of problem](https://i.stack.imgur.com/GV2Ov.png)](https://i.stack.imgur.com/5Oy4g.png)

I hope the error can disappear and the trigger can run smoothly.

CodePudding user response：

You are not permitted in a trigger to change the table the triggering statement is modifying. That's what it means by mutating.

Instead, change it to a BEFORE update trigger and inside just assign :new.test1data1. No update is required.

e.g.

CREATE OR REPLACE EDITIONABLE TRIGGER "TEST1_T1" BEFORE update on "TEST1" 
for each row 
begin 
  :new.test1data1 := :new.test1data2;
end;

CodePudding user response：

I created two tables 'test1' and 'test2'. And I created two triggers 'test1_t1' 'test1_t2'. The code is as follows:

CREATE OR REPLACE EDITIONABLE TRIGGER "TEST1_T1" BEFORE update on "TEST1" for each row begin update test2 set test2.test2data1 = :new.test1data2- :old.test1data2; end;

CREATE OR REPLACE EDITIONABLE TRIGGER "TEST1_T2" AFTER update on "TEST1" for each row begin update test2 set test2.test2data1 = :new.test1data2- :old.test1data2; end;

CREATE TABLE test1 ( test1data1 INT NOT NULL, test1data2 INT NOT NULL, test1key VARCHAR(20) NOT NULL, PRIMARY KEY (test1key) ); CREATE TABLE test2 ( test2data1 INT NOT NULL, test2data2 INT NOT NULL, test2key VARCHAR(20) NOT NULL, PRIMARY KEY (test2key) );

When I changed the value of 'test1.test1data2', the value of 'test2.test2data1' didn't change. How can I solve that problem? table'test1'table'test2' trigger1 trigger2