В процессе эксплуатации автоматизированной системы учета операций с ценными бумагами возникла необходимость создания таблицы для хранения детальной информации (истории сделок). Для её заполнения был разработан механизм, реализуемый набором триггеров БД и серверных пакетных процедур, вызываемых из триггеров. Но при тестировании данного механизма возникла следующая ситуация: при срабатывании триггера вызываемая им серверная процедура запрашивает данные из таблицы, на которой создан триггер. В результате возникает ошибка мутации:

ORA-04091: table <имя_таблицы> is mutating, trigger/function may not see it

(описание этого явления выходит за рамки данной статьи). Для решения проблемы были использованы следующие методы:

• использование триггеров различных типов (FOR EACH ROW и STATEMENT) и глобальных переменных пакета;
• применение заданий серверных процессов (JOBs).

При вставке или изменении записи из таблицы документов выполняется процедура инициализации глобальной переменной пакета значением столбца внутреннего идентификатора записи сделки. Это происходит при срабатывании следующего триггера (FOR EACH ROW trigger):

CREATE OR REPLACE TRIGGER DB_DOC_T1 
after insert or update on DB_DOC
       FOR EACH ROW
begin
 if (inserting or updating) then
    DP_STNDS.Set_Id_Deal(:new.id_deal);
 else
    DP_STNDS.Set_Id_Deal(:old.id_deal);
 end if;
end;

Пакетная процедура DP_STNDS.Set_Id_Deal и функция DP_STNDS.Get_Id_Deal чтения инициализированного значения пакетной переменной определяются следующим образом:

…
PROCEDURE Set_Id_deal(p_id_deal in db_doc.id_deal%type) is
begin
   dp_id_deal:= p_id_deal;
end;

FUNCTION Get_Id_Deal_No
         RETURN dp_id_deal     db_doc.id_deal%type  is
begin
  return dp_id_deal ;
end;
…

Переменная dp_id_deal является глобальной переменной пакета DP_STNDS и объявляется в теле пакета:

CREATE OR REPLACE PACKAGE BODY DP_STNDS AS
-- Переменные пакета
dp_id_deal         db_doc.id_deal%type;
…
END  PACKAGE BODY DP_STNDS;

После инициализации этой переменной происходит выполнение процедуры генерации истории сделки. Это обеспечивается срабатыванием следующего триггера (STATEMENT trigger):

CREATE OR REPLACE TRIGGER DB_DOC_T2
      after insert or update on DB_DOC
declare
     v_id_deal       DB_DOC.id_deal%type;
begin
     v_id_deal:=DP_STNDS.Get_Id_Deal;
     -- Генерация истории сделки
     DC_DEAL_HIST.Gen_Deal_Hist(v_id_deal); 
end;

Процедура DC_DEAL_HIST.Gen_Deal_Hist обращается к таблице документов DB_DOC, но эффекта мутации не возникает, т. к. триггер DB_DOC_T2 является триггером типа STATEMENT (операторный).

ОГРАНИЧЕНИЯ.

Операторы DML, изменяющие записи таблицы (UPDATE), должны применяться к одной, а не к нескольким записям. Например, вместо оператора

UPDATE DB_DOC SET amount = 1000 WHERE id_doc in (1,2);

необходимо использовать операторы

UPDATE DB_DOC SET amount = 1000 WHERE id_doc = 1;
UPDATE DB_DOC SET amount = 1000 WHERE id_doc  = 2;

В первом случае триггер DB_DOC_T2 срабатывает только один раз, во втором – для каждой обновляемой записи (соответственно, для второго случая расчет истории сделки происходит также для каждой обновляемой записи, в отличие от первого случая).

Рассмотрим альтернативный вариант механизма для генерации истории сделок. Триггер DB_DOC_T1 будет выглядеть следующим образом:

CREATE OR REPLACE TRIGGER DB_DOC_T1 
after insert or update on DB_DOC
       FOR EACH ROW
begin
      DC_DEAL_HIST_SUPP.Create_DP_Job_Deals_Rec(:new.id_deal);
end;

Процедура DC_DEAL_HIST_SUPP.Create_DP_Job_Deals_Rec формирует в регистрационной таблице DP_JOB_DEALS запись, содержащую значение внутреннего идентификатора записи сделки - :new.id_deal. Таким образом, данная таблице постоянно пополняется актуальной информацией, необходимой для генерации истории сделок. Для выполнения процедуры генерации истории сделки DC_DEAL_HIST.Gen_Deal используется задание серверного процесса – job. Ниже приводится PL/SQL блок определения задания:

VARIABLE jobno number;
begin
 DBMS_JOB.SUBMIT(:jobno, ‘dc_deal_hist_supp.gen_deals_by_job;', 
                 SYSDATE,'SYSDATE + 1/86400');
 commit;
end;
/

Задание, выполняясь периодически с интервалом в 1 сек, запускает пакетную процедуру DC_DEAL_HIST_SUPP.GEN_DEALS_BY_JOB, которая последовательно обрабатывает все записи регистрационной таблицы DP_JOB_DEALS и выполняет генерацию истории для каждой сделки (процедура DC_DEAL_HIST.Gen_Deal). По завершении работы процедура DC_DEAL_HIST_SUPP.GEN_DEALS_BY_JOB очищает регистрационную таблицу.

Данная технология была неоднократно реализована в нескольких промышленных системах. Использование приемов, описанных в данной статъе, позволяет:

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