Использование составных триггеров (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 триггера, не только для логирования, но и для взаимного обновления таблиц. Для более сложной логики можно использовать коллекции и обновлять данные транзакционно.