开发过程中碰到了ora-04091错误：表XX发生了错误，触发器/函数不能读
以下是模拟场景：
表PRODUCTS：
CREATE TABLE CKSP.PRODUCTS
(
ID NUMBER(1) NOT NULL,
NAME VARCHAR2(100) NOT NULL,
PRICE1 NUMBER(1) NOT NULL,
PRICE2 NUMBER(1) NOT NULL
)
TABLESPACE USERS
NOLOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
并声明触发器TRG_PRODUCTS_INSERT:
CREATE OR REPLACE TRIGGER TRG_PRODUCTS_INSERT
BEFORE INSERT
ON PRODUCTS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
declare
varCount number;
begin
select count(id) into varCount from products where name = :new.name;
if varCount > 0 then
varCount:= varCount +1;
:new.name :=varCount||'_'||:new.name;
end if;
end;
若执行以下SQL则会引发ORA-04091错误
begin
insert into products values(1,'p1',1,2);
insert into products
select 2 as id ,name ,price1,price2 from products where id= 1;
end;
解决方法：修改SQL 如下
declare
varName varchar2(100);
varPrice1 number;
varPrice2 number;
begin
insert into products values(1,'p1',1,2);
select name,price1,price2 into varName,varPrice1,varPrice2 from products where id =1;
insert into products values(2,varName,varprice1,varPrice2);
end;
在此情形中不能使用INSERT INTO XXX SELECT .... 语句，而应使用变量来保存值
这也许只是众多情形之一，仅供参考.
Tags: ora-04091, oracle