建触发器引发的update失败问题！

Question

信息修改失败,原因[cn.sh.ideal.action.RepositoryAction.update()]:SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in config/T_REPOSITORY_SqlMap.xml. --- The error occurred while applying a parameter map. --- Check the T_REPOSITORY.updCheckRepository. --- Check the statement (update procedure failed). --- Cause: java.sql.SQLException: ORA-04091: table FAQ.T_REPOSITORY is mutating, trigger/function may not see it ORA-06512: at "FAQ.REP_INDEX_LUCENE_U_TRIG", line 3 ORA-04088: error during execution of trigger 'FAQ.REP_INDEX_LUCENE_U_TRIG' ORA-06512: at "FAQ.JAVA_UPD_CHECK_REPOSITORY_PRO", line 100 ORA-06512: at line 1触发器如下：CREATE OR REPLACE TRIGGER rep_index_lucene_u_trig AFTER update ON FAQ.T_REPOSITORY
FOR EACH ROW
BEGIN
    delete from t_repository_index where pk_auto_id=:old.PK_AUTO_ID;
    insert into t_repository_index(pk_auto_id,uk_file_id,title,content,type,level_id,post_user_name,update_time,OPE_CODE,annex,key_word)
       SELECT pk_auto_id,uk_file_id,title,content,retype,level_id,post_user_name,update_time,OPE_CODE,annex,key_word 
       from V_REPOSITORY_INDEX_LUCENE where pk_auto_id=:new.pk_auto_id;
END;

Answer 1

网上搜索来的
=======================
ORA-04091与 table mutating
近日解决了一个trigger中报ORA-04091错误的问题，补了关于Oracle table mutating的一课：mutating table 是指一个当前正在被update,delete,insert语句修改的表，如果在一个行级别的trigger中读取或修改一个mutating table，则往往会遇到ORA-04091错误.例如，如果在trigger中使用了select或dml 语句访问trigger所在的表，则就会收到这个错误。然而，Oracle8i和9i文档中都没有解释清楚before和after 类型的 row trigger 在对待两种不同的insert语句(insert into ... values ... 与 insert into ... select ...)时的差别：1、对于after 类型的 for each row 级别的triggers，不论哪种insert语句触发了trigger，都不允许在 trigger 中访问本trigger所依赖的table的，测试如下：
SQL> create table t1 ( c1 number,c2 varchar2(10));
Table created
SQL> create or replace trigger tri_t1
2 after insert on t1 for each row
3 declare
4 cvar varchar2(10);
5 begin
6 select 'Y' into cvar from t1 WHERE ROWNUM=1; --这里访问了trigger 本表
7 end;
8 /
Trigger createdSQL> insert into t1 values (1,'a');
ORA-04091: table TEST.T1 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TRI_T1", line 4
ORA-04088: error during execution of trigger 'TES.TRI_T1'SQL> insert into t1 select '1','a' from dual;
ORA-04091: table TEST.T1 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TRI_T1", line 4
ORA-04088: error during execution of trigger 'TEST.TRI_T1'2、对于before 类型的 for each row 级别的triggers，如果使用 insert into ... values 语句触发此trigger ，则在trigger 中访问本table没有问题；
但如果使用 insert into select .. from 语句触发此trigger ，则在trigger 中访问本table就报ora-04091错误；
只有在Oracle 7标准的开发文档中有这样的说明：
From the Application Developers Guide 
"There is an exception to this restriction;
For single row INSERTs, constraining tables are mutating for
AFTER row triggers, but not for BEFORE row triggers. 
INSERT statements that involve more than 1 row are not considered
single row inserts." 
"INSERT INTO <table_name> SELECT ..." are not considered single row 
inserts, even if they only result in 1 row being inserted.测试如下：
SQL> drop trigger tri_t1;
Trigger droppedSQL> insert into t1 values (1,'a'); --先插入一条数据，避免ORA-01403: no data found 错误。
1 row inserted
SQL> commit;SQL> create or replace trigger tri_t1
2 before insert on t1 for each row
3 declare
4 cvar varchar2(10);
5 begin
6 select 'Y' into cvar from t1 WHERE ROWNUM=1;
7 end;
8 /
Trigger createdSQL> insert into t1 values (2,'b'); -- insert into ... values 没有问题
1 row insertedSQL> insert into t1 select '3','c' from dual; -- insert into ... select .. from 报错
ORA-04091: table TEST.T1 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TRI_T1", line 4
ORA-04088: error during execution of trigger 'TEST.TRI_T1'我们的开发人员因为不知道这个特别情况，近日在修改一个存储过程时候，将原来的insert values 写法改成了insert select 写法，而trigger 又是before类型的，导致出现了ORA-04091错误，搞得分析了好久也没有头绪。其实，在metalink中有一篇note说到了：
文档 ID: 注释:132569.1 
主题: ORA-4091 on BEFORE ROW TRIGGER with INSERT statement 
类型: PROBLEM 
状态: PUBLISHED 
内容类型: TEXT/X-HTML 
创建日期: 16-JAN-2001 
上次修订日期: 09-AUG-2004 Problem Description
-------------------You want to do an insert into a table that has a BEFORE row Trigger.When you hard code the values into the INSERT statement, the trigger works fine.For example:INSERT 
INTO content (cont_name,cont_seg,cat_seq)
VALUES('blah',100,200);1 row created.However, your trigger errors with ERROR ORA-4091 with 
INSERT INTO...select statement:INSERT
INTO content (cont_name,cont_seq,cat_seq) (select....from category);ERROR at line 1:
ORA-4091: table <schema>.CONTENT is mutating, trigger/function may not see it
ORA-6512: at "<schema>.INS_CONTENT", line 4
ORA-4088: error during execution of trigger '<schema>.INS_CONTENT'
TRIGGER:CREATE OR REPLACE trigger INS_CONTENT
BEFORE INSERT on CONTENT 
FOR EACH ROW
DECLARE 
max_sort number;
BEGIN
SELECT max(cont_sort) INTO max_sort FROM CONTENT;
IF max_sort IS NOT NULL AND max_sort!= 99999 THEN
IF :new.cont_sort IS NULL THEN
:new.cont_sort := max_sort +1;
END IF;
END IF;
SELECT SEQ_CONT_SEQ.nextval INTO :new.CONT_SEQ from dual; 
END;
Explanation
-----------Error: ORA 4091
Text: table %s.%s is mutating, trigger/function may not see it
-------------------------------------------------------------------------------
Cause: A trigger (or a user defined PL/SQL 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.Explanation:
You cannot look at or modify the table that is mutating.Note:
From the Application Developers Guide 
"There is an exception to this restriction;
For single row INSERTs, constraining tables are mutating for
AFTER row triggers, but not for BEFORE row triggers. 
INSERT statements that involve more than 1 row are not considered
single row inserts." "INSERT INTO <table_name> SELECT ..." are not considered single row 
inserts, even if they only result in 1 row being inserted.
RELATED DOCUMENTS
-----------------Oracle Application Developer's Guide (A68003-01) 
Chapter 'Using Database Triggers', page 13-22)

Answer 2

问题出在，你首先delete了数据，导致表变异，你的触发器无法看到表。尝试方法：
1.尝试一下不要使用行级触发器，使用语句级触发器。
2. 如果1不行，那么新建一个包变量（用来做全局变量），把:new.pk_auto_id存入，然后insert时候，直接是用where pk_auto_id=package.变量来试一下。有问题站内信。

Answer 3

但是这个TRIGGER并没有查询这个MUTATING TABLE “FAQ.T_REPOSITORY”呀

Answer 4

我把删除那句sql去掉了，还是报一样的错误啊！

Answer 5

从中可以看出貌似是一个java存储过程的审计什么的除了问题，这个问题引起了FAQ.REP_INDEX_LUCENE_U_TRIG出错，并不是楼主给出的这个触发器。
我才测试楼主的update触发了楼主贴出代码的触发器，这个触发器的更新语句又出发了其他的触发器，最后再FAQ.REP_INDEX_LUCENE_U_TRIG中的FAQ.JAVA_UPD_CHECK_REPOSITORY_PRO里产生了错误。
请楼主再查一查！

Answer 6

加入楼主V_REPOSITORY_INDEX_LUCENE 里面的表字段跟 FAQ.T_REPOSITORY是一模一样的话：触发器应该如下写才对：
CREATE OR REPLACE TRIGGER rep_index_lucene_u_trig AFTER update ON FAQ.T_REPOSITORY
FOR EACH ROW
BEGIN
    delete from t_repository_index where pk_auto_id=:old.PK_AUTO_ID;    
    insert into t_repository_index(pk_auto_id,uk_file_id,title,content,type,level_id,post_user_name,update_time,OPE_CODE,annex,key_word)
    values(
           :new.PK_AUTO_ID, :new.UK_FILE_ID,
           :new.TITLE, :new.CONTENT,
           :new.TYPE, :new.LEVEL_ID,
           nvl((select C.REAL_NAME from T_SYS_USER c where c.user_id=:new.FK_POST_USER_ID and rownum=1),'管理员'),
           nvl(:new.UPDATE_TIME,sysdate),
           :new.STATUS, 
           (select replace(wmsys.wm_concat(B.ANNEX_PATH),',','|') from T_REPOSITORY_ANNEX B where B.FK_REPOSITORY_ID=:new.Pk_Auto_Id),
           :new.Key_Word           
           );
    END;  因为触发器会索引所依赖的表，因此你不能用select form  T_REPOSITORY 来查询，但是我看到你原来触发器的查询条件是where pk_auto_id=:new.pk_auto_id，这样的话你可以用:new.colum来取值啊， 这样对本表的数据不久搞定了吗？楼主可以去试试我给的sql脚本，是不是你想要的效果？

Answer 7

其中 nvl((select C.REAL_NAME from T_SYS_USER c where c.user_id=:new.FK_POST_USER_ID and rownum=1),'管理员'),
以及 (select replace(wmsys.wm_concat(B.ANNEX_PATH),',','|') from T_REPOSITORY_ANNEX B where B.FK_REPOSITORY_ID=:new.Pk_Auto_Id),这个列是牵涉到取别的关联表的数据。
如果楼主有的话可以参考。我没有测试过，只是打个比方，因为没有环境啊！