开发的同事报告说,某trigger不管满足不满足它的条件都会触发。听上去有点怪,于是过去看他重现。果然是这样。
他的过程是首先对表A创建一个for each row,insert before的trigger,然后对表进行大量insert操作的时候,必定会触发这个trigger而抛出异常,难道是trigger所on的表不能被他的包中的dml操作正常触发?
决定做个试验。
首先创建一个测试表:
SQL> create table tri (a varchar2(255),b varchar2(255));
Table created.
SQL>
插入数据:
SQL> insert into tri (select sys_guid(),1 from dual);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tri;
A B
---------------------------------------- ----------
C1B04FA32603004EE043AC10120A004E 1
SQL>
接下来创建trigger,在插入重复数据时抛异常:
SQL> create or replace trigger tri_test
2 before insert on tri
3 for each row
4 declare i number(2);
5 begin
6 select count(*) into i from tri where a=:new.a;
7 if i > 0 then
8 RAISE_APPLICATION_ERROR(-20600,'STOP');
9 end if;
10 end;
11 /
Trigger created.
SQL>
试试:
SQL> insert into tri (a,b) values (1,2);
1 row created.
SQL> insert into tri (a,b) values (2,3);
1 row created.
SQL> commit;
Commit complete.
SQL>
没有问题,再试:
SQL> insert into tri (a,b) values (1,4);
insert into tri (a,b) values (1,4)
*
ERROR at line 1:
ORA-20600: STOP
ORA-06512: at "QP.TRI_TEST", line 5
ORA-04088: error during execution of trigger 'QP.TRI_TEST'
SQL>
出问题了,说明能正常触发。
接下来重现同事的问题:
SQL> insert into tri (select sys_guid(),2 from dual);
insert into tri (select sys_guid(),2 from dual)
*
ERROR at line 1:
ORA-04091: table QP.TRI is mutating, trigger/function may not see it
ORA-06512: at "QP.TRI_TEST", line 3
ORA-04088: error during execution of trigger 'QP.TRI_TEST'
SQL>
原来是ora-04091
查文档:
ORA-04091: table string.string is mutating, trigger/function may not see it
Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
Action: Rewrite the trigger (or function) so it does not read that table.
当一个trigger在look at 或者modify一个表时,这个表是不能处于正在被modified的statement中被fire的。就是说,当一个表正在被一个语句所修改或者查询时,如果有个触发器正好on在这个表上了,就会报错。
来看我们的trigger
第二行有个on tri表,然后第六行有个select。。。。。from tri,因此,这个触发器会报错。这个设定保证了sql编写的严谨行。
文档建议重写trigger以便不要去read此表。
我们可以将结果集存入一个临时表中来替代这个trigger,具体方法就不叙述了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25915379/viewspace-731907/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25915379/viewspace-731907/