ORA-04091: table xxx is mutating, trigger/function may not see it

最新推荐文章于 2021-05-25 23:38:29 发布
weixin_34226706
最新推荐文章于 2021-05-25 23:38:29 发布
阅读量114
点赞数
文章标签：数据库
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as tbcs
 
SQL> 
SQL> 
SQL> drop trigger tbcs.TRG_CJW_TEST;
 
drop trigger tbcs.TRG_CJW_TEST
 
ORA-04080: trigger 'TRG_CJW_TEST' does not exist
SQL> drop table tbcs.cjw_test;
 
drop table tbcs.cjw_test
 
ORA-00942: table or view does not exist
SQL> create table cjw_test
  2  (
  3  itemid1    varchar2(10),
  4  itemid2    varchar2(10),
  5  itemid3    varchar2(10)
  6  );
 
Table created
SQL> insert into cjw_test values('1','1','1');
 
1 row inserted
SQL> commit;
 
Commit complete

/*
对于after 类型的 for each row 级别的triggers，不论哪种insert语句触发了trigger，
都不允许在 trigger 中访问本trigger所依赖的table的
*/

SQL> create or replace trigger trg_cjw_test
  2    after insert on tbcs.cjw_test
  3    for each row
  4  declare
  5    i   number:=0;
  6  begin
  7    select count(1) into i from tbcs.cjw_test;
  8  end trg_cjw_test;
  9  /
 
Trigger created
 
SQL> insert into cjw_test values('1','1','1');
 
insert into cjw_test values('1','1','1')
 
ORA-04091: table TBCS.CJW_TEST is mutating, trigger/function may not see it
ORA-06512: at "TBCS.TRG_CJW_TEST", line 4
ORA-04088: error during execution of trigger 'TBCS.TRG_CJW_TEST'
 
SQL> 
SQL> 
/*
对于before 类型的 for each row 级别的triggers，如果使用 insert into ... values 语句触发此trigger ，
则在trigger 中访问本table没有问题；但如果使用 insert into select .. from 语句触发此trigger ，
则在trigger 中访问本table就报ora-04091错误；
*/

SQL> create or replace trigger trg_cjw_test
  2    before insert on tbcs.cjw_test
  3    for each row
  4  declare
  5    i   number:=0;
  6  begin
  7    select count(1) into i from tbcs.cjw_test;
  8  end trg_cjw_test;
  9  /
 
Trigger created
 
SQL> insert into cjw_test values('1','1','1');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> 
SQL> create or replace trigger trg_cjw_test
  2    before insert on tbcs.cjw_test
  3    for each row
  4  declare
  5    i   number:=0;
  6  begin
  7    insert into tbcs.cjw_test
  8    select '2','2','2' from dual;
  9  end trg_cjw_test;
 10  /
 
Trigger created
 
SQL> insert into cjw_test values('1','1','1');
 
insert into cjw_test values('1','1','1')
 
ORA-04091: table TBCS.CJW_TEST is mutating, trigger/function may not see it
ORA-06512: at "TBCS.TRG_CJW_TEST", line 4
ORA-04088: error during execution of trigger 'TBCS.TRG_CJW_TEST'
ORA-06512: at "TBCS.TRG_CJW_TEST", line 4
ORA-04088: error during execution of trigger 'TBCS.TRG_CJW_TEST'
 
SQL> 

 SQL> 
/*
上面实验中使用before，我们在仅插入一条数据的情况下，并没有报错，现在试一下插入多条数据的情况
*/
SQL> create or replace trigger trg_cjw_test
  2    before insert on tbcs.cjw_test
  3    for each row
  4  declare
  5    i   number:=0;
  6  begin
  7    select count(1) into i from tbcs.cjw_test;
  8  end trg_cjw_test;
  9  /
 
Trigger created
 
SQL> 
SQL> create table cjw_test_bak as select * from cjw_test;
 
Table created
SQL> insert into cjw_test_bak values('2','1','1');
 
1 row inserted
SQL> insert into cjw_test_bak values('3','1','1');
 
1 row inserted
SQL> insert into cjw_test_bak values('4','1','1');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> insert into cjw_test select * from cjw_test_bak;
 
insert into cjw_test select * from cjw_test_bak
 
ORA-04091: table TBCS.CJW_TEST is mutating, trigger/function may not see it
ORA-06512: at "TBCS.TRG_CJW_TEST", line 4
ORA-04088: error during execution of trigger 'TBCS.TRG_CJW_TEST'
 
SQL>