Oracle After Delete Trigger ...如何避免突变表(ORA-04091)? [英] Oracle After Delete Trigger... How to avoid Mutating Table (ORA-04091)?
问题描述
假设我们具有以下表结构:
Let's say we have the following table structures:
documents docmentStatusHistory status
+---------+ +--------------------+ +----------+
| docId | | docStatusHistoryId | | statusId |
+---------+ +--------------------+ +----------+
| ... | | docId | | ... |
+---------+ | statusId | +----------+
| ... |
+--------------------+
很明显,但是值得一提的是,文档的当前状态是最后输入的状态历史记录.
It may be obvious, but it's worth mentioning, that the current status of a document is the last Status History entered.
系统运行缓慢但肯定会降低性能,我建议将上述结构更改为:
The system was slowly but surely degrading in performance and I suggested changing the above structure to:
documents docmentStatusHistory status
+--------------+ +--------------------+ +----------+
| docId | | docStatusHistoryId | | statusId |
+--------------+ +--------------------+ +----------+
| currStatusId | | docId | | ... |
| ... | | statusId | +----------+
+--------------+ | ... |
+--------------------+
这样,我们就可以将文档的当前状态保留在应有的位置.
This way we'd have the current status of a document right where it should be.
由于遗留应用程序的构建方式,我无法更改遗留应用程序上的代码以更新文档表上的当前状态.
Because the way the legacy applications were built I could not change the code on legacy applications to update the current status on the document table.
在这种情况下,我不得不为自己的规则打开一个例外,以不惜一切代价避免触发器,这仅仅是因为我无权访问旧版应用程序代码.
In this case I had to open an exception to my rule to avoid triggers at all costs, simply because I don't have access to the legacy applications code.
我创建了一个触发器,每次在状态历史记录中添加新状态时都会更新文档的当前状态,
I created a trigger that updates the current status of a document every time a new status is added to the status history, and it works like a charm.
但是,在晦涩难懂的情况下,需要删除最新的状态历史记录,而不是简单地添加一个新的状态历史记录.因此,我创建了以下触发器:
However, in an obscure and rarely used situation there is a need to DELETE the last status history, instead of simply adding a new one. So, I created the following trigger:
create or replace trigger trgD_History
after delete on documentStatusHistory
for each row
currentStatusId number;
begin
select statusId
into currentStatusId
from documentStatusHistory
where docStatusHistoryId = (select max(docStatusHistoryId)
from documentStatusHistory
where docId = :old.docId);
update documentos
set currStatusId = currentStatusId
where docId = :old.docId;
end;
多数民众赞成在哪里我臭名昭著的错误ORA-04091
.
And thats where I got the infamous error ORA-04091
.
即使我将触发器配置为 AFTER 触发器,我仍然理解为什么出现此错误.
I understand WHY I'm getting this error, even though I configured the trigger as an AFTER trigger.
问题是我看不到解决此错误的方法.我已经在网上搜索了一段时间,但到目前为止找不到任何有用的信息.
The thing is that I can't see a way around this error. I have searched the net for a while and couldn't find anything helpful so far.
及时,我们正在使用Oracle 9i.
In time, we're using Oracle 9i.
推荐答案
突变表错误的标准解决方法是创建
The standard workaround to a mutating table error is to create
- 一个带有键集合的包(在这种情况下,即docId).临时表也可以工作
- 用于初始化集合的before语句触发器
- 行级触发器,该触发器使用每个已更改的docId填充集合
- 一个after语句触发器,该触发器遍历集合并执行实际的UPDATE
类似
CREATE OR REPLACE PACKAGE pkg_document_status
AS
TYPE typ_changed_docids IS TABLE OF documentos.docId%type;
changed_docids typ_changed_docids := new typ_changed_docids ();
<<other methods>>
END;
CREATE OR REPLACE TRIGGER trg_init_collection
BEFORE DELETE ON documentStatusHistory
BEGIN
pkg_document_status.changed_docids.delete();
END;
CREATE OR REPLACE TRIGGER trg_populate_collection
BEFORE DELETE ON documentStatusHistory
FOR EACH ROW
BEGIN
pkg_document_status.changed_docids.extend();
pkg_document_status.changed_docids( pkg_document_status.changed_docids.count() ) := :old.docId;
END;
CREATE OR REPLACE TRIGGER trg_use_collection
AFTER DELETE ON documentStatusHistory
BEGIN
FOR i IN 1 .. pkg_document_status.changed_docids.count()
LOOP
<<fix the current status for pkg_document_status.changed_docids(i) >>
END LOOP;
pkg_document_status.changed_docids.delete();
END;
这篇关于Oracle After Delete Trigger ...如何避免突变表(ORA-04091)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!