Oracle After Delete Trigger Как избежать изменения таблицы (ORA-04091)?

Допустим, у нас есть следующие структуры таблиц:

documents      docmentStatusHistory      status
+---------+   +--------------------+    +----------+
| docId   |   | docStatusHistoryId |    | statusId |
+---------+   +--------------------+    +----------+
| ...     |   | docId              |    | ...      |
+---------+   | statusId           |    +----------+
              | ...                |
              +--------------------+

Это может быть очевидно, но стоит упомянуть, что текущий статус документа — это последняя введенная история статусов.

Производительность системы медленно, но верно снижалась, и я предложил изменить приведенную выше структуру на:

documents           docmentStatusHistory      status
+--------------+   +--------------------+    +----------+
| docId        |   | docStatusHistoryId |    | statusId |
+--------------+   +--------------------+    +----------+
| currStatusId |   | docId              |    | ...      |
| ...          |   | statusId           |    +----------+
+--------------+   | ...                |
                   +--------------------+

Таким образом, у нас будет текущий статус документа именно там, где он должен быть.

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

В этом случае мне пришлось открыть исключение из моего правила, чтобы любой ценой избежать триггеров, просто потому, что у меня нет доступа к устаревшему коду приложений.

Я создал триггер, который обновляет текущий статус документа каждый раз, когда новый статус добавляется в историю статусов, и он работает как шарм.

Однако в неясной и редко используемой ситуации возникает необходимость УДАЛИТЬ последнюю историю статусов, а не просто добавлять новую. Итак, я создал следующий триггер:

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.

Я понимаю, ПОЧЕМУ я получаю эту ошибку, хотя я настроил триггер как триггер ПОСЛЕ.

Дело в том, что я не вижу способа обойти эту ошибку. Я некоторое время искал в сети и пока не нашел ничего полезного.

Со временем мы используем Oracle 9i.


oracle triggers ora-04091
person Paulo Santos    schedule 27.04.2011    source источник

Ответы (2)


arrow_upward
9
arrow_downward

Стандартный обходной путь для ошибки мутирующей таблицы — создать

  • Пакет с набором ключей (в данном случае docId). Временная таблица также будет работать
  • Триггер оператора before, который инициализирует коллекцию
  • Триггер уровня строки, который заполняет коллекцию каждым измененным идентификатором документа.
  • Триггер оператора after, который выполняет итерацию по коллекции и выполняет фактическое ОБНОВЛЕНИЕ

Так что-то вроде

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;
person Justin Cave    schedule 27.04.2011
comment
Мне пришлось внести некоторые изменения, чтобы заставить его работать, но принцип был тот же. Пришлось инициализировать сбор в пакете, иначе выдавало бы ошибку в trg_init_collection; удалите +1 из .count()+1 в trg_populate_collection, потому что это дало мне ошибку SUBSCRIPT_OUTSIDE_LIMIT. - person Paulo Santos; 27.04.2011
comment
Стандартный обходной путь появился раньше, чем Oracle реализовала Global Temporary Tables; Я помню, как использовал этот обходной путь в 7.3. - person Adam Musch; 28.04.2011
comment
Как мне поступить, если я хочу UPDATE, а не DELETE и добавить блокировку на определенные строки в таблице? - person Radu Gheorghiu; 16.10.2015
comment
@RaduGheorghiu - предположительно, ваш триггер уровня оператора after delete будет перебирать коллекцию обновленных элементов и реализовывать любую бизнес-логику, которую вы хотите (я полагаю, что вы хотите истечь предыдущую строку, если вы тот человек, о котором я думаю) . Таким образом, вы перебираете коллекцию, обновляя все строки, которые перекрываются с новыми строками. Я не уверен, что вы подразумеваете под добавлением блокировки определенных строк в этом контексте. - person Justin Cave; 16.10.2015
comment
@JustinCave Да, я думаю я человек, о котором вы думаете. Я сделал несколько небольших дополнений к вопросу, который вы закрыли, где я забыл упомянуть, что меня также интересовала блокировка некоторых строк. - person Radu Gheorghiu; 16.10.2015

arrow_upward
1
arrow_downward

кажется дубликатом этот вопрос

ознакомьтесь с Томом Кайтом по этому поводу

person HAL 9000    schedule 27.04.2011
comment
Нет.. Я уже пробовал. Проблема в том, что мне нужно получить последнее значение таблицы, из которой я удаляю. - person Paulo Santos; 27.04.2011
comment
Ответ Kyte не касается этой ситуации. - person Randy; 27.04.2011
comment
он делает решение этой ситуации IMO. Я также держу пари, что решение Джастина изначально является BTW Тома :) - person HAL 9000; 27.04.2011