原文： 刘武 | ora-04091 表XX发生了变化 触发器/函数不能读 的错误处理
开发过程中碰到了 ora-04091 错误：表XX发生了错误，触发器/函数不能读
以下是模拟场景：
表PRODUCTS：
CREATE
TABLE
CKSP.PRODUCTS
(
ID NUMBER(1)
NOT
NULL
,
NAME
VARCHAR
2(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
varchar
2(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 .... 语句，而应使用变量来保存值
这也许只是众多情形之一，仅供参考.
