Эффективное программирование на PL/SQL - Последовательность выполнения триггеров

ОГЛАВЛЕНИЕ

Последовательность выполнения триггеров

Начиная с Oracle8 появилась возможность описать несколько триггеров одинакового типа на одной таблице - например два строчных after-триггера при вставке в одну таблицу. Тип триггеров определяет порядок выполнения: перед предложением, перед строкой, после предложения и после строки. Однако, если есть два строчных after-триггера, T1 и T2, то какой из них сработает первым?

Выполнение триггеров одинакового типа произвольно или по крайне мере не гарантированно следует шаблону. Является ли это проблемой? Давайте рассмотрим пример таблицы PAYMENTS, показанный ниже:

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 PAY_ID                                             NUMBER(10)
 CREDIT_CARD_NO                                     VARCHAR2(16)
 AMOUNT                                             NUMBER(13,2)
 PAY_MODE                                           VARCHAR2(1)
 RISK_RATING                                        VARCHAR2(6)
 FOLLOW_UP                                          VARCHAR2(1)

Необходимо вычислить рейтинг риска в зависимости от типа платежей и суммы и сохранить его в столбце RISK_RATING. Следующий простой строчный триггер before-update легко справляется с этой задачей:

before update
on payments
for each row
begin
        dbms_output.put_line ('This is tr_pay_risk_rating');
        if (:new.amount) < 1000 then
                :new.risk_rating := 'LOW';
        elsif (:new.amount < 10000) then
                if (:new.pay_mode ='K') then
                        :new.risk_rating := 'MEDIUM';
                else
                        :new.risk_rating := 'HIGH';
                end if;
        else
                :new.risk_rating := 'HIGH';
        end if;
end;
/

Теперь кто-нибудь добавляет ещё одно требование: некоторые значения, зависящие от столбцов RISK_RATING, PAY_MODE, и др. должны быть в столбце FOLLOW_UP помечены признаком повышения. Триггер необходимо модифицировать, но лучше не трогать существующий код, а создать новый триггер такого же типа (строчный before-update), как показано ниже. (Я поместил в код dbms_output, чтобы показать, как триггеры будут срабатывать).

create or replace trigger tr_pay_follow_up
before update
on payments
for each row
begin
        dbms_output.put_line ('This is tr_pay_follow_up');
        if (
                (:new.risk_rating = 'HIGH' and :new.pay_mode = 'C')
                or (:new.risk_rating = 'MEDIUM' and :new.pay_mode = 'K')
                or (substr(:new.credit_card_no,1,5) = '23456')
        ) then
                :new.follow_up := 'Y';
        else
                :new.follow_up := 'N';
        end if;
end;
/

Теперь если выполнить обновление таблицы:

SQL> get upd_pay
  1  update payments set
  2     credit_card_no = '1234567890123456',
  3     amount = 100000,
  4*    pay_mode = 'K'

SQL> @upd_pay

This is tr_pay_follow_up

This is tr_pay_risk_rating

1 row updated.

SQL> select * from payments;

    PAY_ID CREDIT_CARD_NO       AMOUNT P RISK_R F
---------- ---------------- ---------- - ------ -
         1 1234567890123456     100000 C HIGH   N

Что же случилось? Столбец risk_rating имеет значение HIGH, а столбец pay_mode - значение "C", которые означают, что столбец FOLLOW_UP должен быть "Y", а не "N". Почему? Чтобы ответить на этот вопрос, посмотрите, в каком порядке сработали триггеры: tr_pay_follow_up сработал раньше, чем tr_pay_risk_rating. Последний установил значение столбца как высокий рейтинг. Поэтому, когда первый сработал, он нашёл null (или "N") в столбце risk_rating и поэтому посчитал, что условие удовлетворяется.

В этом случае порядок выполнения триггеров очень важен. Если tr_pay_risk_rating не сработает раньше второго, корректные значения не будут установлены, а то, что установится, будет неверной реализацией требований. Единственной простой возможностью была бы замена всей логики на один код и выполнение её в нужном порядке.

В Oracle Database 11 g можно в скрипте создания триггера указать выражение, которое устанавливает порядок триггеров. Вот верхняя часть триггера, включающая это выражение:

create or replace trigger tr_pay_follow_up
before update
on payments
for each row
follows tr_pay_risk_rating
begin
... and so on...

Это выражение (FOLLOWS <название_триггера>) заставляет триггер срабатывать после указанного триггера. Протестируем это, выполнив скрипт обновления, показанный ранее.

SQL> @upd_pay
This is tr_pay_risk_rating
This is tr_pay_follow_up

1 row updated.

SQL> select * from payments;
 
    PAY_ID CREDIT_CARD_NO       AMOUNT P RISK_R F
---------- ---------------- ---------- - ------ -
         1 1234567890123456     100000 C HIGH   

1 row selected.

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