我做了一个触发器,使用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'
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 子句后就不行了。