Эффективное программирование на 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.
Как и ожидалось, столбец заполнен корректно. Заметьте также корректный порядок триггеров, который подтверждает, что выражение работает.
Порядок триггеров позволяет применить преимущества модульного кода, позволяя также гарантировать, что они выполнятся в правильной последовательности.