Использование составных триггеров (compound triggers) Oracle

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

Дано: две таблицы orders и orders_detail.

CREATE TABLE TMP.orders (   ID          NUMBER,  -- № заказа   DATE_DOC    DATE,    -- дата заказа   AMOUNT      NUMBER,  -- сумма заказа   DISCOUNT    NUMBER   -- скидка от 0 до 100% ); 

CREATE TABLE TMP.orders_detail (   ID         NUMBER,   ID_ORDER   NUMBER, -- № заказа   ID_GOODS   NUMBER, -- идентификатор товара   PRICE      NUMBER, -- цена товара   QTY        NUMBER, -- количество   SUM_ORDER  NUMBER  -- стоимость заказа ); 

Перед нами стоят задачи

1. при обновлении поля orders.discount  пересчитать orders_detail.sum_order, а также обновить поле orders.amount по всему заказу;   

2. при изменении orders_detail.price  и(или) orders_detail.qty  пересчитать    orders_detail.sum_order, а также обновить поле orders.amount по всему заказу;

3. orders_detail.sum_order считается по формуле: orders_detail.sum_order = orders_detail.price * orders_detail.qty * (1- orders.discount/100).

При таких задачах возникает ошибка мутирования таблиц ORA-04091: table is mutating.

Эта ошибка встречается тогда, когда триггер уровня строк пытается вы­полнить чтение или запись в таблицу, для которой сработал триггер. Например, нам нужно пересчитать поле orders_detail.sum_order, а для этого нам нужно прочитать поле orders.discount(которое изменилось).

Начиная с Oracle Database 11g можно использовать compound(составные) триггеры. Они представляют собой четыре различных триггера, объединенных в один.

Вот пример синтаксиса такого триггера.

CREATE OR REPLACE TRIGGER compound_trigger      FOR UPDATE OF field_table ON table       COMPOUND TRIGGER          BEFORE STATEMENT IS      BEGIN        NULL;      END BEFORE STATEMENT;          BEFORE EACH ROW IS      BEGIN        NULL;      END BEFORE EACH ROW;          AFTER EACH ROW IS      BEGIN        NULL;      END AFTER EACH ROW;          AFTER STATEMENT IS      BEGIN        NULL;      END AFTER STATEMENT;    END compound_trigger; 

Он имеет 4 раздела:

Before Statement — выполняется до команды DML
Before Row — выполняется перед обработкой каждой строки

After Row — выполняется после обработки каждой строки
After Statement — выполняется после команды DML.

Есть несколько ограничений для таких триггеров

• Нельзя обращаться к псевдозаписям old, new в разделах  уровня before statement и after statement

• Изменять значения полей new можно только в секции before each row

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

Итак, для нашей задачи

Триггер для таблицы orders, здесь мы проверяем значение discount, записываем в переменную и после обновляем таблицу orders_detail.

CREATE OR REPLACE TRIGGER TMP.tr_orders   FOR UPDATE OF discount   ON tmp.orders   COMPOUND TRIGGER   ----------------------------   BEFORE STATEMENT IS   BEGIN     null;   END BEFORE STATEMENT;   ----------------------------   BEFORE EACH ROW IS   BEGIN      if :new.discount < 0 or :new.discount > 100 then       :new.discount := 0;     end if;      tmp.pkg_orders.g_ord.discount := :new.discount;     tmp.pkg_orders.g_ord.id       := :new.id;    END BEFORE EACH ROW;   ----------------------------   AFTER EACH ROW IS   BEGIN    null;   END AFTER EACH ROW;   ----------------------------   AFTER STATEMENT IS   BEGIN     update tmp.orders_detail set sum_order = price * qty  *(1-tmp.pkg_orders.g_ord.discount/100)     where id_order = tmp.pkg_orders.g_ord.id ;   END AFTER STATEMENT;   ----------------------------   END ;

Триггер для второй таблицы orders_detail, здесь заполняем discount и пересчитываем поле sum_order, а затем обновляем таблицу orders.

CREATE OR REPLACE TRIGGER TMP.tr_orders_detail   FOR UPDATE OR INSERT OR DELETE OF sum_order, price, qty   ON tmp.orders_detail   COMPOUND TRIGGER    t_ord   tmp.orders%rowtype;   --t_ord_d tmp.orders_detail%rowtype;    ----------------------------   BEFORE STATEMENT IS   BEGIN     null;   END BEFORE STATEMENT;   ----------------------------   BEFORE EACH ROW IS   BEGIN     if inserting or updating then        tmp.pkg_orders.get_discount(:new.id_order);        t_ord.discount := tmp.pkg_orders.g_ord.discount;        :new.sum_order := :new.price * :new.qty * (1-t_ord.discount/100);     end if;   END BEFORE EACH ROW;   ----------------------------   AFTER EACH ROW IS   BEGIN     if inserting or updating then       t_ord.id := :new.id_order;     end if;      if deleting then       t_ord.id := :old.id_order;     end if;   END AFTER EACH ROW;   ----------------------------   AFTER STATEMENT IS   BEGIN     update tmp.orders set amount = (select sum(sum_order)                                     from tmp.orders_detail                                     where id_order = t_ord.id)     where id = t_ord.id;   END AFTER STATEMENT;   ----------------------------   END ; 

Пакет для заполнения discount.

CREATE OR REPLACE PACKAGE TMP.pkg_orders IS   g_ord tmp.orders%rowtype;   ----------------------------------------------------------------   PROCEDURE get_discount(p_id_order number);   ---------------------------------------------------------------- END;   CREATE OR REPLACE PACKAGE BODY TMP.pkg_orders is   ----------------------------------------------------------------    PROCEDURE get_discount(p_id_order number)     is     l_discount number;    BEGIN      select discount into l_discount      from tmp.orders where id = p_id_order;       g_ord.discount := l_discount;     END;    ---------------------------------------------------------------- END; 

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