Эффективное программирование на PL/SQL - Составные триггеры

Составные триггеры

Рассмотрим гостиничную базу данных: журналы комнат гостиницы хранятся в таблице BOOKINGS. Нужно также записывать изменения этой таблице для её контроля - что-то наподобие аудита, но с одной особенностью: нужно делать это транзакционно. Триггеры подходят для этого лучше всего.

Тем самым нужен небольшой триггер события after-update для строки, который записывает старые и новые значения в таблицу BOOKINGS_HIST, а также того, кто внёс изменение. Пока всё хорошо.

Есть, однако, небольшая проблемка. Триггер after-update-row срабатывает для каждой строки, а некоторые записи изменяются в массовом порядке, изменяя сотни строк за одну транзакцию. Отдельные срабатывания триггера after-update-row для каждой из строк и выполнение каждый раз вставки записи в таблицу bookings_hist делают производительность не оптимальной.

Лучше было бы сложить вместе эти вставки в таблицу bookings_hist и выполнить их кучей. Это можно осуществить через сложную серию триггеров. Суть в том, что надо поместить значения, предназначенные для таблицы bookings_hist, в коллекцию в триггере на каждую стоку, а затем загрузить данные из коллекции в таблицу bookings_hist с помощью триггера after-update для предложения, который срабатывает только один раз. Так как фактически вставка происходит только один раз, процесс выполняется быстрее, чем вставка каждой отдельной строки.

Но это два различных триггера с различными кодами. Есть только один способ передать переменную с коллекцией из одного триггера в другой - создать пакет с переменной-коллекцией, такой как массив или PL/SQL-таблица, в спецификации пакета, заполнить её в строчном триггере after-update и считать в триггере after на предложение - а это непростая задача. Не проще было бы вместо этого поместить все триггеры в одном коде?

В Oracle Database 11 g можно использовать compound(составные) триггеры . Составные триггеры представляют собой четыре различных триггера, объявленных как один. Например, составной UPDATE-триггер имеет before для предложения, before для строки, after для предложения и after для строки, одновременно присутствующие в одном составном триггере. Вот часть кода, описывающая, как можно передать переменные будто бы внутри одного монолитного PL/SQL-кода.

Рассмотрим пример. Номера строк добавлены, чтобы было проще его объяснять.

 1 create or replace trigger tr_bookings_track
 2 for update of booking_dt
 3 on bookings
 4 compound trigger
 5 type ty_bookings_hist is table of bookings_hist%rowtype
 6 index by pls_integer;
 7 coll_bookings_hist ty_bookings_hist;
 8 ctr pls_integer := 0;
 9 before statement is
 10 begin
 11 dbms_output.put_line('In before statement');
 12 end before statement;
 13 before each row is
 14 begin
 15 dbms_output.put_line('In before each row');
 16 end before each row;
 17 after each row is
 18 begin
 19 ctr := ctr + 1;
 20 dbms_output.put_line('In after each row. booking_id='//:new.booking_id);
 21 coll_bookings_hist(ctr).booking_id := :new.booking_id;
 22 coll_bookings_hist(ctr).mod_dt := sysdate;
 23 coll_bookings_hist(ctr).mod_user := user;
 24 coll_bookings_hist(ctr).old_booking_dt := :old.booking_dt;
 25 coll_bookings_hist(ctr).new_booking_dt := :new.booking_dt;
 26 end after each row;
 27 after statement is
 28 begin
 29 dbms_output.put_line('In after statement');
 30 forall counter in 1..coll_bookings_hist.count()
 31 insert into bookings_hist
 32 values coll_bookings_hist(counter);
 33 end after statement;
 34 end tr_bookings_track;

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

update bookings
set booking_dt = sysdate
where booking_id between 100 and 103;

Вот результат:

In before statement
In before each row
In after each row. booking_id=100
In before each row
In after each row. booking_id=101
In before each row
In after each row. booking_id=102
In before each row
In after each row. booking_id=103
In after statement 

Заметьте, как выполняется составной триггер. Он имеет четыре секции:

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

Как видите, этот код един, но каждая секция выполняется в разное время.

В предыдущем примере я поместил предложения dbms_output в различных местах, чтобы показать, как каждая секция выполняется в этих точках. Я изменил четыре строки с booking_ids 100, 101, 102 и 103, и видно, что триггеры before- и after для предложения сработали каждый по одному разу, а триггеры для строки (before и after) по одному разу на строку. (В предыдущем примере триггеры before для предложения и строки не нужны, но я их написал для иллюстрации функциональности).

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

BOOKING_ID MOD_DT    MOD_USER                       OLD_BOOKI NEW_BOOKI
---------- --------- ------------------------------ --------- ---------
       100 27-SEP-07 ARUP                           28-AUG-07 27-SEP-07
       101 27-SEP-07 ARUP                           06-AUG-07 27-SEP-07
       102 27-SEP-07 ARUP                           04-SEP-07 27-SEP-07
       103 27-SEP-07 ARUP                           15-JUN-07 27-SEP-07 

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