我有一个表,其中可以保存一个帐户的多个记录:不同的金额。
ACCOUNTID | AMOUNT
id1 | 1
id1 | 2
id2 | 3
id2 | 4
每次插入/更新/删除该表中的记录时,我们都需要评估总金额,以便知道是否应该触发事件(通过将数据插入另一个表)。该金额是根据此表中的记录总和(每个帐户)计算的。
金额的计算应该使用记录的新值,但我们还需要旧值来检查某些条件(例如,旧值是X-新值是Y:如果[X<=Threshold and Y>Threshold],则通过将一条记录插入另一个表来触发事件)。
因此,为了计算和触发事件,我们在该表上创建了一个触发器。大概是这样的:
CREATE OR REPLACE TRIGGER <trigger_name>
AFTER INSERT OR UPDATE OR DELETE OF MOUNT ON <table_name>
FOR EACH ROW
DECLARE
BEGIN
1. SELECT SUM(AMOUNT) INTO varSumAmounts FROM <table_name> WHERE accountid = :NEW.accountid;
2. varAmount := stored_procedure(varSumAmounts);
END <trigger_name>;
问题是语句1.抛出以下错误:“ORA-04091:表发生变化,触发器/函数可能看不到它”。
我们尝试了以下操作,但没有成功(相同的异常/错误),以选择rowID不同于当前rowID的所有记录:
(SELECT SUM(AMOUNT)
INTO varSumAmounts
FROM <table_name>
WHERE accountId = :NEW.accountid
AND rowid <> :NEW.rowid;)
以便将数量计算为当前行之外的所有行的数量之和+当前行的数量(我们在触发器的上下文中具有)。
我们寻找了其他解决方案,找到了一些,但我不知道哪种方案更好,每种方案的缺点是什么(尽管它们在某种程度上是相似的)
1.使用复合触发器
1.http://www.oracle-base.com/articles/9i/mutating-table-exceptions.php
1.http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936
为了避免基于解决方案1和2的“表正在发生变化”错误,我结合使用了复合触发器和全局临时表。
现在我们有了一个复合触发器,它使用一些全局临时表来存储来自:old和:new伪记录的相关数据。基本上,我们会做以下几件事:
CREATE OR REPLACE TRIGGER trigger-name
FOR trigger-action ON table-name
COMPOUND TRIGGER
-------------------
BEFORE STATEMENT IS
BEGIN
-- Delete data from global temporary table (GTT) for which source is this trigger
-- (we use same global temporary tables for multiple triggers).
END BEFORE STATEMENT;
-------------------
AFTER EACH ROW IS
BEGIN
-- Here we have access to :OLD and :NEW objects.
-- :NEW and :OLD objects are defined only inside ROW STATEMENTS.
-- Save relevant data regarding :NEW and :OLD into GTT table to use it later.
END AFTER EACH ROW;
--------------------
AFTER STATEMENT IS
BEGIN
-- In this block DML operations can be made on table-name(the same table on which
--the trigger is created) safely.
-- Table is mutating error will no longer appear because this block is not for EACH ROW specific.
-- But we can't access :OLD and :NEW objects. This is the reason why in 'AFTER EACH ROW' we saved them in GTT.
-- Because previously we saved :OLD and :NEW data, now we can continue with our business logic.
-- if (oldAmount<=threshold && newAmount>threshold) then
-- trigger event by inserting record into another table
END AFTER STATEMENT;
END trigger-name;
/
使用的全局临时表是使用选项‘ON COMMIT DELETE ROWS’创建的,这样我可以确保该表中的数据将在事务结束时被清除。然而,出现了此错误:‘ORA-14450:尝试访问已在使用中的事务临时表’。
问题是应用程序使用分布式事务,在Oracle文档中提到:“在将全局临时表(GTT)与分布式事务或XA事务结合使用时,可能会报告各种内部错误。
在任何分布式XA协调事务中都不支持临时表。最安全的选择是不在分布式或XA事务中使用临时表,因为在这种上下文中使用临时表是不受官方支持的。..。
如果数据库中只有单个分支事务在使用全局临时表,则可以安全地使用全局临时表,但如果存在环回数据库链接或涉及多个分支的XA事务,则可能会出现问题,包括错误5344322所示的块损坏。“
值得一提的是,我无法避免XA事务或在触发器所在的同一个表上进行DML(修复数据模型不是一个可行的解决方案)。我曾尝试使用触发器变量来代替全局临时表--集合(对象表),但我不确定是否采用这种方法。分布式事务是否安全?
在这种情况下,有哪些其他解决方案适合解决最初的问题:“ORA-04091:表名变了,触发器/函数可能看不到它”,或者第二个解决方案:“ORA-14450:尝试访问已在使用的事务性临时表”?
2条答案
按热度按时间ar5n3qh51#
您应该仔细检查您的代码没有使用自主事务来访问临时表数据:
ajsxfq5m2#
如果您在
BEFORE STATEMENT
和AFTER STATEMENT
中执行DELETE FROM <temp-table-name>
,则无论您的gtt是用ON COMMIT PRESERVE ROWS
还是ON COMMIT DELETE ROWS
定义的,都应该无关紧要。在触发器中,您可以定义记录/表变量。这个变量可以在
BEFORE STATEMENT
块中初始化,并在BEFORE STATEMENT
块中循环。大概是这样的: