开发过程中碰到了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 .... 语句,而应使用变量来保存值
这也许只是众多情形之一,仅供参考.