我导入数据到userinfo_temp后触发触发器USERINFO_TEMP_ADD
更新USERINFO表 出现错误:
ORA-04091: 表 RONGHE.USERINFO 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "RONGHE.SELECT_USE_ITEMS_RECORD", line 8
ORA-06512: 在 "RONGHE.PR_USERINFO_NEW_ADD", line 3
ORA-06512: 在 "RONGHE.USERINFO_TEMP_ADD", line 2
ORA-04088: 触发器 'RONGHE.USERINFO_TEMP_ADD' 执行过程中出错触发器---------------------------------------------------
create or replace TRIGGER USERINFO_TEMP_ADD
AFTER INSERT ON USERINFO_TEMP
BEGIN
pr_userinfo_new_add;
END;
过程------------------------------------------------------
create or replace PROCEDURE PR_USERINFO_NEW_ADD AS
BEGIN MERGE INTO userinfo u
USING userinfo_temp ut
ON (u.name = ut.name and u.USE_ITEMS = ut.USE_ITEMS )
WHEN MATCHED THEN
UPDATE
SET u.sex = ut.sex, u.cred_name = ut.cred_name, u.cred_number = ut.cred_number,
u.MARITAL_STATUS = ut.MARITAL_STATUS,u.ANNUAL_INCOME=ut.ANNUAL_INCOME,u.CONTACT_STATUS = ut.CONTACT_STATUS,
u.USE_ITEMS_RECORD = select_use_items_record(ut.NAME,ut.USE_ITEMS,ut.USE_ITEMS)
WHERE u.cred_number = ut.cred_number
when not matched then
insert (u.NAME, u.SEX, u.CRED_NAME, u.CRED_NUMBER, u.MARITAL_STATUS, u.ANNUAL_INCOME, u.CONTACT_STATUS, u.USE_ITEMS)
values(ut.NAME, ut.SEX, ut.CRED_NAME, ut.CRED_NUMBER, ut.MARITAL_STATUS, ut.ANNUAL_INCOME, ut.CONTACT_STATUS, ut.USE_ITEMS)
where ut.name not in (select name FROM userinfo);
END PR_USERINFO_NEW_ADD;函数------------------------------------------------------
create or replace FUNCTION SELECT_USE_ITEMS_RECORD
( IN_NAME IN VARCHAR2, IN_USE_ITEMS IN VARCHAR2, IN_USE_ITEMS_RECORD IN VARCHAR2
) RETURN VARCHAR2 AS
userinfo_id INTEGER;
sel_use_items_record VARCHAR2(40);
instr_number INTEGER;
BEGIN
select id INTO userinfo_id FROM userinfo WHERE name = in_name AND use_items = in_use_items;
select use_items_record INTO sel_use_items_record FROM userinfo WHERE id= userinfo_id;
select instr(sel_use_items_record, in_use_items_record) INTO instr_number from dual;
if instr_number = 0 then
begin
sel_use_items_record := sel_use_items_record || '-' || in_use_items_record;
end;
end if;
RETURN sel_use_items_record;
END SELECT_USE_ITEMS_RECORD;
更新USERINFO表 出现错误:
ORA-04091: 表 RONGHE.USERINFO 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "RONGHE.SELECT_USE_ITEMS_RECORD", line 8
ORA-06512: 在 "RONGHE.PR_USERINFO_NEW_ADD", line 3
ORA-06512: 在 "RONGHE.USERINFO_TEMP_ADD", line 2
ORA-04088: 触发器 'RONGHE.USERINFO_TEMP_ADD' 执行过程中出错触发器---------------------------------------------------
create or replace TRIGGER USERINFO_TEMP_ADD
AFTER INSERT ON USERINFO_TEMP
BEGIN
pr_userinfo_new_add;
END;
过程------------------------------------------------------
create or replace PROCEDURE PR_USERINFO_NEW_ADD AS
BEGIN MERGE INTO userinfo u
USING userinfo_temp ut
ON (u.name = ut.name and u.USE_ITEMS = ut.USE_ITEMS )
WHEN MATCHED THEN
UPDATE
SET u.sex = ut.sex, u.cred_name = ut.cred_name, u.cred_number = ut.cred_number,
u.MARITAL_STATUS = ut.MARITAL_STATUS,u.ANNUAL_INCOME=ut.ANNUAL_INCOME,u.CONTACT_STATUS = ut.CONTACT_STATUS,
u.USE_ITEMS_RECORD = select_use_items_record(ut.NAME,ut.USE_ITEMS,ut.USE_ITEMS)
WHERE u.cred_number = ut.cred_number
when not matched then
insert (u.NAME, u.SEX, u.CRED_NAME, u.CRED_NUMBER, u.MARITAL_STATUS, u.ANNUAL_INCOME, u.CONTACT_STATUS, u.USE_ITEMS)
values(ut.NAME, ut.SEX, ut.CRED_NAME, ut.CRED_NUMBER, ut.MARITAL_STATUS, ut.ANNUAL_INCOME, ut.CONTACT_STATUS, ut.USE_ITEMS)
where ut.name not in (select name FROM userinfo);
END PR_USERINFO_NEW_ADD;函数------------------------------------------------------
create or replace FUNCTION SELECT_USE_ITEMS_RECORD
( IN_NAME IN VARCHAR2, IN_USE_ITEMS IN VARCHAR2, IN_USE_ITEMS_RECORD IN VARCHAR2
) RETURN VARCHAR2 AS
userinfo_id INTEGER;
sel_use_items_record VARCHAR2(40);
instr_number INTEGER;
BEGIN
select id INTO userinfo_id FROM userinfo WHERE name = in_name AND use_items = in_use_items;
select use_items_record INTO sel_use_items_record FROM userinfo WHERE id= userinfo_id;
select instr(sel_use_items_record, in_use_items_record) INTO instr_number from dual;
if instr_number = 0 then
begin
sel_use_items_record := sel_use_items_record || '-' || in_use_items_record;
end;
end if;
RETURN sel_use_items_record;
END SELECT_USE_ITEMS_RECORD;
解决方案 »
- 有没有类似BO的报表工具,C/S模式且免费的。
- 请教如何配置oracle客户端的tcps协议让他能够支持ssl链接?
- sqlplus,shell间参数传递问题。急~~~谢谢!
- ORACLE 9I 数据库启动不了
- 创建数据库的问题
- 怎样建立一个自动删除的触发器
- 请问谁有在windows 2003 server下装oracle 9i的经验?
- 关于oracle创建表格的问题
- PL/SQL Developer 的中文教程哪有?,请知道的告以下!
- ●●●●●一句SQL的写法.这是无法完成的任务吗,看看有没有高手来实现?●●
- pl/sql delete 多条数据时,报 ora-00936?
- oracle 8i的树的查询 求助
你google下"表 RONGHE.USERINFO 发生了变化, 触发器/函数不能读它"
这个错误提示就能得到好多文章