Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Июль 2002   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : про мутирующие таблицы


Информационный Канал Subscribe.Ru

Выпуск 10

Про мутирующие таблицы

Уважаемые подписчики! Этот выпуск посвящен дискуссии, которую провел Том Кайт с подписчиками группы comp.databases.oracle.misc в 1999 году. Думаю, обсуждение это стоит того, чтобы его воспроизвести и прокомментировать. Я выбросил подробности переписки, оставив только вопросы, комментарии и ответы.

Вопрос:

Мне всегда хотелось, чтобы кто-нибудь объяснил мне, в чем суть понятия мутирующих таблиц. Я поискал информацию о мутирующих таблицах в нескольких руководствах, но само понятие для меня остается весьма смутным. Нельзя ли привести пример? Как избегать такой ситуации?

Ответ Тома Кайта:

Я воспроизведу старое сообщение на эту тему (к сожалению, в службе deja news его, повидимому, нет). Далее следует мой ответ на аналогичный вопрос. Я приведу как вопрос, так и ответ - имеет смысл сначала перечитать вопрос, чтобы понять, о чем идет речь. Я объясняю, почему возникает ошибка мутирующей таблицы (для вашей же пользы) и как ее избежать...

Исходный вопрос, заданный в ноябре 1997 года

У меня есть строчный триггер before insert, прекрасно работающий при выполнении оператора INSERT INTO table VALUES(x, x, x), но если выполнить INSERT INTO table (SELECT x, x, x FROM other_table), он выдает сообщение об ошибке "ORA-04091: table is mutating, trigger/function may not see it."

Что вызывает такое различие?

Подробнее:

У меня есть 2 таблицы:

CREATE TABLE member
(group_id    NUMBER  NOT NULL,
 unit_id     NUMBER  NOT NULL,
 carr        NUMBER  NOT NULL,
 CONSTRAINT pk_member PRIMARY KEY (group_id, unit_id) );

CREATE TABLE carr_change
(group_id    NUMBER  NOT NULL,
 old_carr    NUMBER  NOT NULL,
 new_carr    NUMBER  NOT NULL);

Мне необходим триггер, чтобы при вставке строк в member, проверялся предыдущий элемент (unit) группы. Если у него другое значение атрибута carr, чем у вставляемого, мне необходимо вставить строку в таблицу carr_change для пакетной обработки ночью. Таким образом, если имеется запись (1, 1, 7) в таблице member, и вставляется строка (1, 2, 6), необходимо записать строку (1, 7, 6) в таблицу carr_change.

Вот какой триггер я написал:

CREATE OR REPLACE TRIGGER before_member_trig
   BEFORE INSERT ON member
   FOR EACH ROW

DECLARE
   l_prev_carr  member.carr%TYPE;

BEGIN
   SELECT carr
      INTO l_prev_carr
      FROM member
      WHERE group_id = :new.group_id
        AND unit_id = (:new.unit_id - 1) );

   IF :new.carr != l_prev_carr
   THEN
      INSERT INTO carr_change (group_id, old_carr, new_carr)
         VALUES (:new.group_id, l_prev_carr, :new.carr);
   END IF;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      NULL;     -- this is fine, first unit for a group

   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
/

Этот триггер хорошо работает для вставок с конструкцией VALUES, но не срабатывает при вставке с подзапросом. Ошбика мутирующей таблицы, ORA -04091. Нет ли идей, почему он работает иначе и как это исправить?

Ответ:

Это сделано для защиты вас от ваших собственных действий.

Поскольку таблица при многострочной вставке, фактически, мутирует (изменяется), можно получить разные результаты при выполнении точно такого же оператора insert, в зависимости от порядка строк в подзапросе (например: оператор insert при стоимостной оптимизации может дать другой результат, чем при запроса оптимизации на основе правил).

Например, путь таблица была пуста. М ы выполняем вставку значений

(1, 1, 11 );
(1, 2, 12 );
(1, 3, 13 );

Если подзапрос вернет значения в таком порядке, триггер вызовет вставку двух строк в таблицу carr_change.

Если же строки из подзапроса вернутся (помните, выполнется тот же запрос, просто изменяется порядок вставки строк - им вы не можете управлять) в таком порядке: 

(1, 3, 13 );
(1, 2, 12 );
(1, 1, 11 );

ни одна строка вставлена не будет, в отличие от предыдущего варианта (причем мы не можем это проконтролировать).

Поэтому можно вставить одну строку и использовать триггер BEFORE INSERT/FOR EACH ROW, читающий таблицу (при этом не будет двусмысленности, результат вставки еще невидим, поэтому невозможны и конфликты вроде показанного выше, когда при разных выполнениях одного и того же оператора получаются разные результаты). При вставке одной строки таблица еще не начала "мутировать", когда срабатывает триггер before insert/for each row (но она будет уже мутирующей для триггера after insert/for each row, даже при вставке одной строки).

Ну, и как же это обойти? Использовать триггер after. Например, я переписал ваш триггер следующим образом:

create or replace package member_pkg
as
    type    rArray is table of rowid index by binary_integer;
    rids    rArray;
    cnt     number;
end;
/

create or replace trigger member_bi
before insert on member
begin
    member_pkg.cnt := 0;
end;
/

create or replace trigger member_aifer
after insert on member
for each row
begin
    member_pkg.cnt := member_pkg.cnt+1;
    member_pkg.rids( member_pkg.cnt ) := :new.rowid;
end;
/

CREATE OR REPLACE TRIGGER member_ai
after INSERT ON member
DECLARE
   l_prev_carr  member.carr%TYPE;
   l_rec        member%rowtype;
begin
    for i in 1 .. member_pkg.cnt loop
    BEGIN
        select * into l_rec from member where rowid = member_pkg.rids(i);
        SELECT carr
          INTO l_prev_carr
          FROM member
        WHERE group_id = l_rec.group_id
           AND unit_id = (l_rec.unit_id - 1 );
        IF l_rec.carr != l_prev_carr THEN
           INSERT INTO carr_change (group_id, old_carr, new_carr)
           VALUES (l_rec.group_id, l_prev_carr, l_rec.carr);
        END IF;
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
          dbms_output.put_line('no data');
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
   END;
   end loop;
end;
/

Так, что действия

insert into member
select 1, 1, 11 from dual union all
select 1, 2, 12 from dual union all
select 1, 3, 13 from dual; 

select * from carr_change; 

rollback; 

insert into member
select 1, 3, 13 from dual union all
select 1, 2, 12 from dual union all
select 1, 1, 11 from dual ; 

select * from carr_change;

теперь дают согласованный (тот же) ответ, даже если строки вставляются в "обратном" порядке (если бы мы не считали таблицу "мутирующей", два представленных выше оператора insert дали бы разные результаты при использовании однострочного триггера - один вставил бы 2 строки в таблицу carr_change, а второй - 0 строк.)

Поскольку в триггере необходим только доступ к новым (:new) значениям, в строчном триггере можно просто сохранить идентификаторы строк. Если бы необходимы были и старые значения, пришлось бы сохранять их в отдельных массивах.

Комменатрий В.К.

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

Весь вопрос в том, где сохранять затронутые строки и необходимые значения. Почему-то слушатели моих курсов упорно отказываются использовать продемонстрированное Томом решение на базе PL/SQL-таблиц. И все норовят использовать таблицы базы данных, путь даже временные...

В следующем выпуске

Я ухожу в отпуск. Рассылка возобновит выход в конце августа. Вас ждет много интересного. Следите за новостями на сайте OpenXS Initiative и шлите мне свои вопросы и пожелания по дальнейшей работе рассылки.

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное