Trigger vs Change Notification

На днях, общаясь с заказчиком, я столкнулся с одной очень распространненой задачей: в существующем приложении (реализовано на PL/SQL+APEX), при изменении статуса документа необходимо отправлять письмо-нотификацию по электронной почте.Заказчик намеревался написать для этого очередной триггер и "повесить" его на табличку с документами.

У меня сразу же возникли два возражения:
1) Отправка нотификации это внешний сервис, то есть НЕ является составной частью жизненного цикла документа.
2) Отработка триггера будет замедлять работу приложения сервисной операцией.

Для решения этой задачи есть более подходящий, на мой взгляд, способ - использовать технологию Database Change Notification.
Эта технология позволяет подписаться на сообщение об изменение данных, и получив это уведомление, выполнить некоторую обработку. Возможен вариант обработки как на сервере (в виде PL/SQL-процедуры), либо на клиенте - в виде callback-процедуры на клиенте.

Самое главное отличие технологии Change Notification от триггеров состоит в том, что callback-процедура обработки сообщения об изменении выполняется в асинхронном режиме к изменению - проще говоря выполняется в отдельном потоке (специализированном однократном job-е), и значит не замедляет работу приложения.

Собственно как это делается.
На первом шаге пользователю нужно дать две привилегии:
1) на сервисный системный пакет dbms_cq_notification -
grant execute on dbms_cq_notification to rscott;

2) собственно права на получение нотификации об изменениях -
grant change notification to rscott;

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

--процедура обработки в виде статического метода объектного типа:
  static procedure StateChangeHandler(ntfnds in cq_notification$_descriptor) is
    v_xNumRows        number;
    v_xOperationType  number;
    v_xRowIdStr       varchar2(2000 char);
    v_xRowId          rowid;
    v_xDocument       TDocumentOfRegistration;
  begin
    if (ntfnds.event_type = dbms_cq_notification.event_querychange) then
      v_xOperationType := ntfnds.query_desc_array(1).table_desc_array(1).Opflags;

      --реагируем только на операции UPDATE
      if v_xOperationType = dbms_cq_notification.UpdateOp then
      
        if (bitand(v_xOperationType, dbms_cq_notification.all_rows) = 0) then
          --определяем количество строк которые затронули изменения в родительской транзакции
          v_xNumRows := ntfnds.query_desc_array(1).table_desc_array(1).numrows;
          
          for k in 1..v_xNumRows 
          loop 
            --извлекаем rowid строк, которые изменились
            v_xRowIdStr := ntfnds.query_desc_array(1).table_desc_array(1).row_desc_array(k).row_id;
            v_xRowId    := chartorowid(v_xRowIdStr);
            
            --читаем документ:
            v_xDocument := new TDocumentOfRegistration(v_xRowId); 
              
            --отправляем письмо по email об изменении:
            v_xDocument.sendStateChangeLetter();

            v_xDocument.destroy;
          end loop;
        end if;
      end if;
    end if;

    commit;
  end;

Наконец, регистрируем наш обработчик:

declare
  v_xRegInfo          cq_notification$_reg_info;
  v_xNotificationQoS  simple_integer    := dbms_cq_notification.qos_query +
                                           dbms_cq_notification.qos_rowids;
  v_xCallback         varchar2(64 char) := 'rscott.TDocumentOfRegistration.StateChangeHandler';
  v_xCursor           sys_refcursor;
  v_xRegId            number;
begin
  v_xRegInfo := new cq_notification$_reg_info(callback          => v_xCallback,
                                              qosflags          => v_xNotificationQoS,
--нотификация посылается для всех операций
                                              operations_filter => 0, 
--регистрация существует вечно, до тех пока явно не будет удалена
                                              timeout           => 0, 
--нотификация посылается немедленно после фиксации транзакции
                                              transaction_lag   => 0);
  v_xRegId := dbms_cq_notification.new_reg_start(v_xRegInfo);

  open v_xCursor for 
    select 
      Id 
    from 
      documents 
    where 
      State = 'ACTIVE';

  close v_xCursor;

  dbms_cq_notification.reg_end;
end;
/

Как Вы видите, наш обработчик будет вызываться только если изменение затронуло документы находящиеся в состоянии 'ACTIVE'.

В заключение, небольшой FAQ.
1) Вопрос: требуется ли дополнительная настройка для работы Database Change Notification ?
Ответ: Необходимо лишь установить в ненулевое значение параметр job_queue_processes.

2) Вопрос: Если транзакция, в которой генерировались изменения, была незавершена (например был явный rollback) ?
Ответ: В этом случае никакого сообщения отправлено НЕ будет - нотификация отправляется только после фиксации транзакции.

3) Вопрос: Я искал в документации описание этой технологии, но никак не могу найти.
Ответ: Описание технологии Database Change Notification "запрятано" в Oracle Database Advanced Developet Guide

4) Вопрос: Правильно ли я понял, что подписка происходит на изменение результатов запроса, а не всей таблицы в целом.
Ответ: Именно так! Хотя при регистрации можете указать, что Вас интересует изменение всей таблицы в целом.