CREATE OR REPLACE TRIGGER "USER"."TRG_U_TABLE_FIELD" BEFORE
UPDATE OF "FIELD" ON "TABLE" FOR EACH ROW
IF :NEW.COL1='A'
THEN
SELECT COUNT(PK) INTO N_COUNT
FROM TABLE
WHERE COL3=:NEW.COL3;
IF N_COUNT >1 THEN
dbms_output.put_line('业务异常提示!');
END IF;
END IF;
UPDATE OF "FIELD" ON "TABLE" FOR EACH ROW
IF :NEW.COL1='A'
THEN
SELECT COUNT(PK) INTO N_COUNT
FROM TABLE
WHERE COL3=:NEW.COL3;
IF N_COUNT >1 THEN
dbms_output.put_line('业务异常提示!');
END IF;
END IF;
关键的问题是 ORA-04091 USER.TABLE 发生了变化,触发器/函数不能读
谢谢,是否可以创建一个小表,然后尝试一下在修改本表的触发器中读取本表的数据
?
before 数据插入还未进行,你就用select移动了记录指针,再回来插入什么呢,所以要报错,改 before 为 after 问题100%解决!
再说,我也出现过这种错误!
修改为 AFTER 后依然如故
UPDATE OF "COL1" ON "TEST" FOR EACH ROW
DECLARE
N_COUNT NUMBER(2);
ERR EXCEPTION;BEGINIF :NEW.COL1='A' THEN
SELECT COUNT(PK) INTO N_COUNT FROM TEST WHERE COL3=:NEW.COL3;
IF N_COUNT >1 THEN
RAISE ERR;
END IF;
END IF;EXCEPTION
WHEN ERR THEN
RAISE_APPLICATION_ERROR(-20001,' EXISTS !');
END;以上在9i调试通过!