触发器问题--ora-04091错误，请教

[dotnba]

这是一个主从关系的两个表，但是当删除主表记录时，导致ora-04091。
原因是因为在删除子表的时候，导致主表的COPY变化，从而导致ora-04091错误，请问如何主表触发器应该怎么写？并能达到数据完整和一致。

--表EDOCS插入触发器
CREATE OR REPLACE TRIGGER trInsEDOCS
BEFORE INSERT ON EDOCS
FOR EACH ROW
BEGIN
--计数器加1
Update JKARCHVOL21 Set COPY = NVL(COPY,0) + 1
Where VOL_ID = :NEW.VOL_ID;
END trInsEDOCS;
--表EDOCS删除触发器
CREATE OR REPLACE TRIGGER trDelEDOCS
BEFORE DELETE ON EDOCS
FOR EACH ROW
BEGIN
--计数器减1
Update JKARCHVOL21 Set COPY = NVL(COPY,0) - 1
Where VOL_ID = :OLD.VOL_ID;
END trDelEDOCS;
--表JKARCHVOL21删除触发器
CREATE OR REPLACE TRIGGER trDelArch
BEFORE DELETE ON JKARCHVOL21
FOR EACH ROW
BEGIN
--保证数据完整性
Delete From Edocs Where VOL_ID = :OLD.VOL_ID;
END trDelArch;

[bobjeey]

1

[ern]

ORA-04091 table string.string is mutating, trigger/function may not see it


Cause: A trigger (or a user defined PL/SQL function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

Action: Rewrite the trigger (or function) so it does not read that table.

[dotnba]

大家是怎么样解决这样地问题地？

[welyngj]

CREATE OR REPLACE TRIGGER trDelArch
BEFORE DELETE ON JKARCHVOL21
FOR EACH ROW
BEGIN
--保证数据完整性
Delete From Edocs Where VOL_ID = :OLD.VOL_ID;
END trDelArch;


可以用级联删除来解决。

[ZHANGGANGG]

表EDOCS和表JKARCHVOL21做一个级联(on delete cascade)

[ZHANGGANGG]

你的表EDOCS和表JKARCHVOL21之间似乎并不是主从表的关系啊
你把触发器
CREATE OR REPLACE TRIGGER trDelArch
BEFORE DELETE ON JKARCHVOL21
FOR EACH ROW
BEGIN
--保证数据完整性
Delete From Edocs Where VOL_ID = :OLD.VOL_ID;
END trDelArch;
该成 after 类型的试一试