ora-04091 表XX发生了变化 触发器/函数不能读 的错误处理

开发过程中碰到了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 .... 语句，而应使用变量来保存值

这也许只是众多情形之一，仅供参考.