"ORA-14450: попытка получить доступ к транзакционной временной таблице, которая уже используется" в составном триггере

Question

У меня есть таблица, которая может содержать много записей для одной учетной записи: разные суммы.

ACCOUNTID | AMOUNT
id1       | 1
id1       | 2
id2       | 3
id2       | 4

Каждый раз, когда запись в этой таблице вставляется / обновляется / удаляется, нам нужно оценить общую сумму, чтобы узнать, следует ли нам инициировать событие или нет (вставляя данные в другую таблицу). Сумма рассчитывается на основе суммы записей (на счет), представленных в этой таблице.

Расчет суммы должен использовать новые значения записей, но нам также нужны старые значения, чтобы проверить некоторые условия (например, старое значение было X - новое значение Y: если [X<=threshold и 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. Используйте составной триггер

2. http://www.oracle-base.com/articles/9i/mutating-table-exceptions.php

3. 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: попытка получить доступ к транзакционной временной таблице, которая уже используется".

Проблема заключается в том, что приложение использует распределенные транзакции, а в документации оракула упоминается, что: "При использовании глобальных временных таблиц (GTT) в сочетании с распределенными транзакциями или транзакциями XA могут сообщаться различные внутренние ошибки...

Временные таблицы не поддерживаются ни в одной распределенной и, следовательно, в XA, скоординированной транзакции. Самый безопасный вариант - не использовать временные таблицы в распределенных транзакциях или транзакциях XA, поскольку их использование в этом контексте официально не поддерживается....

Глобальная временная таблица может быть безопасно использована, если в базе данных используется только одна транзакция ветвления, но если существуют петлевые ссылки на базу данных или транзакции XA, включающие несколько ветвей, могут возникнуть проблемы, включая повреждение блоков, согласно ошибке 5344322. "

Стоит отметить, что я не могу избежать транзакций XA или создания DML на одной и той же таблице, которая является предметом триггера (исправление модели данных не является возможным решением). Я попытался использовать вместо глобальной временной таблицы переменную триггера - коллекцию (таблицу объектов), но я не уверен в этом подходе. Насколько это безопасно в отношении распределенных транзакций?

Какие другие решения подойдут в этом случае для исправления первой проблемы: "ORA-04091: имя таблицы меняется, триггер / функция может ее не увидеть", или второй: "ORA-14450: попытка получить доступ к транзакционной температуре" таблица уже используется?

Answer 1

Вы должны тщательно проверить, что ваш код не использует автономные транзакции для доступа к данным временных таблиц:

SQL> create global temporary table t (x int) on commit delete rows
  2  /

SQL> insert into t values(1)
  2  /

SQL> declare
  2   pragma autonomous_transaction;
  3  begin
  4  insert into t values(1);
  5  commit;
  6  end;
  7  /
declare
*
error in line 1:
ORA-14450: attempt to access a transactional temp table already in use 
ORA-06512: error in line 4 

Answer 2

Если вы делаете DELETE FROM <temp-table-name> в BEFORE STATEMENT а также AFTER STATEMENT это не имеет значения, если ваш GTT определяется с ON COMMIT PRESERVE ROWS или же ON COMMIT DELETE ROWS,

В вашем триггере вы можете определить переменную RECORD/TABLE. Эта переменная, которую вы можете инициализировать в BEFORE STATEMENT заблокировать и зациклить его в BEFORE STATEMENT блок.

Было бы что-то вроде этого:

CREATE OR REPLACE TRIGGER TRIGGER-NAME
FOR TRIGGER-action ON TABLE-NAME
COMPOUND TRIGGER

TYPE GTT_RECORD_TYPE IS RECORD (ID NUMBER, price NUMBER, affected_row ROWID);
TYPE GTT_TABLE_TYPE IS TABLE OF GTT_RECORD_TYPE;
GTT_TABLE GTT_TABLE_TYPE;

-------------------
BEFORE STATEMENT IS
BEGIN
    GTT_TABLE := GTT_TABLE_TYPE(); -- init the table variable   
END BEFORE STATEMENT;
-------------------
AFTER EACH ROW IS
BEGIN
    GTT_TABLE.EXTEND;
    GTT_TABLE(GTT_TABLE.LAST) := GTT_RECORD_TYPE(:OLD.ID, :OLD.PRICE, :OLD.ROWID);
END AFTER EACH ROW;
--------------------
AFTER STATEMENT IS
BEGIN
    FOR i IN GTT_TABLE.FIRST..GTT_TABLE.LAST LOOP
        -- do something with values
    END LOOP;
END AFTER STATEMENT;
END TRIGGER-NAME;
/