我做了一个触发器,使用insert 表 values('1','1','003','4','5');
可以插入值,但是使用带有select 子句的insert语句时,报错:xx is mutating, trigger/function may not see it
请高手指教!
表结构
SQL> desc cmi (cmi_temp与cmi表结构一致)
名称 是否为空?类型
------------------------------- -------- ----
CMI_COMMUNITY_NAME NOT NULL VARCHAR2(60)
CMI_DEPARTMENT_ACO NOT NULL VARCHAR2(1)
CMI_DEPARTMENT_RBR NOT NULL VARCHAR2(3)
CMI_MANAGER_NAME NOT NULL VARCHAR2(10)
CMI_MANAGER_EMP NOT NULL VARCHAR2(20)触发器代码:(编译正常)CREATE OR REPLACE TRIGGER cmi_trigger_before_insert_row
BEFORE INSERT
ON cc_community_manager_infor
FOR EACH ROW
DECLARE
e_insert_cmi_error EXCEPTION;
v_nError NUMBER(5);
v_sErrorMsg VARCHAR2(300);
v_nFound NUMBER;BEGIN
SELECT COUNT(*) INTO v_nFound FROM dual WHERE EXISTS (SELECT * FROM cmi WHERE cmi_manager_emp=:new.CMI_MANAGER_EMP);
IF v_nFound>0 THEN
v_nError:=3020;
v_sErrorMsg := '工号'||:new.CMI_MANAGER_EMP||'与现有工号重复!';
raise e_insert_cmi_error;
END IF; EXCEPTION
when e_insert_cmi_error then
RAISE_APPLICATION_ERROR(-20002,'插入cmi表失败,出错代码:'||v_nError||',提示:'||v_sErrorMsg);
WHEN others THEN
v_nError:=sqlcode; v_sErrorMsg := SUBSTR(sqlerrm, 1, 200);
RAISE_APPLICATION_ERROR(-20097,'CMI表新增记录失败:'||v_nError||':'||v_sErrorMsg);
END;
/执行
insert into cmi(CMI_COMMUNITY_NAME,CMI_DEPARTMENT_ACO,CMI_DEPARTMENT_RBR,CMI_MANAGER_NAME,CMI_MANAGER_EMP) values('1','1','003','4','5');正常。
但是执行:
insert into cmi(CMI_COMMUNITY_NAME,CMI_DEPARTMENT_ACO,CMI_DEPARTMENT_RBR,CMI_MANAGER_NAME,
CMI_MANAGER_EMP) select CMI_COMMUNITY_NAME,CMI_DEPARTMENT_ACO,CMI_DEPARTMENT_RBR,CMI_MANAGER_NAME,CMI_MANAGER_EMP from cmi_temp;报错:
ERROR 位于第 1 行:
ORA-20097: CMI表新增记录失败:-4091:ORA-04091: table
SPTV2.CC_COMMUNITY_MANAGER_INFOR is mutating, trigger/function may not see it
ORA-06512: at "SPTV2.CMI_TRIGGER_BEFORE_INSERT_ROW", line 20
ORA-04088: error during execution of trigger
'SPTV2.CMI_TRIGGER_BEFORE_INSERT_ROW'
可以插入值,但是使用带有select 子句的insert语句时,报错:xx is mutating, trigger/function may not see it
请高手指教!
表结构
SQL> desc cmi (cmi_temp与cmi表结构一致)
名称 是否为空?类型
------------------------------- -------- ----
CMI_COMMUNITY_NAME NOT NULL VARCHAR2(60)
CMI_DEPARTMENT_ACO NOT NULL VARCHAR2(1)
CMI_DEPARTMENT_RBR NOT NULL VARCHAR2(3)
CMI_MANAGER_NAME NOT NULL VARCHAR2(10)
CMI_MANAGER_EMP NOT NULL VARCHAR2(20)触发器代码:(编译正常)CREATE OR REPLACE TRIGGER cmi_trigger_before_insert_row
BEFORE INSERT
ON cc_community_manager_infor
FOR EACH ROW
DECLARE
e_insert_cmi_error EXCEPTION;
v_nError NUMBER(5);
v_sErrorMsg VARCHAR2(300);
v_nFound NUMBER;BEGIN
SELECT COUNT(*) INTO v_nFound FROM dual WHERE EXISTS (SELECT * FROM cmi WHERE cmi_manager_emp=:new.CMI_MANAGER_EMP);
IF v_nFound>0 THEN
v_nError:=3020;
v_sErrorMsg := '工号'||:new.CMI_MANAGER_EMP||'与现有工号重复!';
raise e_insert_cmi_error;
END IF; EXCEPTION
when e_insert_cmi_error then
RAISE_APPLICATION_ERROR(-20002,'插入cmi表失败,出错代码:'||v_nError||',提示:'||v_sErrorMsg);
WHEN others THEN
v_nError:=sqlcode; v_sErrorMsg := SUBSTR(sqlerrm, 1, 200);
RAISE_APPLICATION_ERROR(-20097,'CMI表新增记录失败:'||v_nError||':'||v_sErrorMsg);
END;
/执行
insert into cmi(CMI_COMMUNITY_NAME,CMI_DEPARTMENT_ACO,CMI_DEPARTMENT_RBR,CMI_MANAGER_NAME,CMI_MANAGER_EMP) values('1','1','003','4','5');正常。
但是执行:
insert into cmi(CMI_COMMUNITY_NAME,CMI_DEPARTMENT_ACO,CMI_DEPARTMENT_RBR,CMI_MANAGER_NAME,
CMI_MANAGER_EMP) select CMI_COMMUNITY_NAME,CMI_DEPARTMENT_ACO,CMI_DEPARTMENT_RBR,CMI_MANAGER_NAME,CMI_MANAGER_EMP from cmi_temp;报错:
ERROR 位于第 1 行:
ORA-20097: CMI表新增记录失败:-4091:ORA-04091: table
SPTV2.CC_COMMUNITY_MANAGER_INFOR is mutating, trigger/function may not see it
ORA-06512: at "SPTV2.CMI_TRIGGER_BEFORE_INSERT_ROW", line 20
ORA-04088: error during execution of trigger
'SPTV2.CMI_TRIGGER_BEFORE_INSERT_ROW'
解决方案 »
- win7旗舰版使用vs2010不能连接上plsql!!问题很急!!
- 在oracle中 关于选择后结果如何合并的问题(用union不能够解决问题) 请各位指教!
- 查询价格最高的图书,输出书名及作者,SQL怎么写?
- 紧急求助
- 大师们帮忙出谋画测, 4G的内存, 3.2G的双志强CPU, 机器上如何配置oralce9i , 性能会最好,速度会最快?
- oracle90数据库需要硬件的最底配置是什么啊?为什么总会出现问题,求助!!
- oracle能在XP下安装吗?
- 服务器掉电后oracle9i for win2000数据库不能启动???
- 怎样让 SQL Plus 在select * from table 时将该表所有的字段名完整的显示出来?
- 安装oracle8i
- 求ORACLE数据库的备份与恢复方法或命令,来着有分!!!
- 如何将无用的零删
insert into cmi(CMI_COMMUNITY_NAME,CMI_DEPARTMENT_ACO,CMI_DEPARTMENT_RBR,CMI_MANAGER_NAME,
CMI_MANAGER_EMP)
select t.CMI_COMMUNITY_NAME,t.CMI_DEPARTMENT_ACO,t.CMI_DEPARTMENT_RBR,t.CMI_MANAGER_NAME,t.CMI_MANAGER_EMP from cmi_temp t;
insert into cmi(CMI_COMMUNITY_NAME,CMI_DEPARTMENT_ACO,CMI_DEPARTMENT_RBR,CMI_MANAGER_NAME,
CMI_MANAGER_EMP)
select t.CMI_COMMUNITY_NAME,t.CMI_DEPARTMENT_ACO,t.CMI_DEPARTMENT_RBR,t.CMI_MANAGER_NA
ME,t.CMI_MANAGER_EMP from cmi_temp t;
第1行有错:
ORA-20097: CMI表新增记录失败:-4091:ORA-04091: table test.CC_COMMUNITY_MANAGER_INFOR is mutating,
trigger/function may not see it
ORA-06512: at "test.CMI_TRIGGER_BEFORE_INSERT_ROW", line 36
ORA-04088: error during execution of trigger 'test.CMI_TRIGGER_BEFORE_INSERT_ROW'
SELECT COUNT(*) INTO v_nFound FROM dual WHERE EXISTS (SELECT * FROM cmi WHERE cmi_manager_emp=:new.CMI_MANAGER_EMP);
insert into cmi(CMI_COMMUNITY_NAME,CMI_DEPARTMENT_ACO,CMI_DEPARTMENT_RBR,CMI_MANAGER_NAME,CMI_MANAGER_EMP) values('1','1','003','4','5');
是成功的。
但是加上select 子句后就不行了。