Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 33: контроль операций модификации данных
Выполним вставку данных, удовлетворяющих всем условиям задачи:
MS SQL Решение 4.2.1.a (проверка работоспособности)
1 |
INSERT INTO [subscriptions] |
|
2 |
|
([sb_subscriber], |
3 |
|
[sb_book], |
4 |
|
[sb_start], |
5 |
|
[sb_finish], |
6 |
|
[sb_is_active]) |
7 |
VALUES |
(4, |
8 |
|
4, |
9 |
|
'2001-01-12', |
10 |
|
'2021-02-12', |
11 |
|
'N') |
Полученные сообщения:
•В первом варианте решения: никаких сообщений от триггера нет.
•Во втором варианте решения:
o Сообщение об ошибке: отсутствует. o Информационное сообщение:
Subscriptions with the following activation/deactivation dates were inserted successfully: 2001-01-12/2021-02-12
Выполним обновление данных с нарушением одного из условия задачи:
MS SQL Решение 4.2.1.a (проверка работоспособности)
1 |
UPDATE |
[subscriptions] |
2 |
SET |
[sb_finish] = '2005-01-01' |
3 |
WHERE |
[sb_start] > '2011-01-01' |
Триггер во втором варианте решения не реагирует на операцию обновления, а от триггера в первом варианте решения поступит следующее сообщение об ошибке:
The following subscriptions' deactivation dates are less than activation dates: 2 (act: 2011-01-12, deact: 2005-01-01), 3 (act: 2012-05-17, deact: 2005-01-01), 42 (act: 2012-06-11, deact: 2005-01-01), 57 (act: 2012-06-11, deact: 2005-01- 01), 61 (act: 2014-08-03, deact: 2005-01-01), 62 (act: 2014-08-03, deact: 2005- 01-01), 86 (act: 2014-08-03, deact: 2005-01-01), 91 (act: 2015-10-07, deact: 2005-01-01), 95 (act: 2015-10-07, deact: 2005-01-01), 99 (act: 2015-10-08, deact: 2005-01-01), 100 (act: 2011-01-12, deact: 2005-01-01)
Выполним обновление данных с соблюдением всех условий задачи:
MS SQL 
Решение 4.2.1.a (проверка работоспособности)
1 |
UPDATE |
[subscriptions] |
2 |
SET |
[sb_finish] = '2002-01-01' |
3 |
WHERE |
[sb_start] = '2001-01-12'; |
Триггер во втором варианте решения не реагирует на операцию обновления, а от триггера в первом варианте решения не поступит никаких сообщений.
Итак, решение данной задачи для MS SQL Server получено и проверено. Переходим к решению для Oracle.
Поскольку Oracle не поддерживает псевдотаблицы deleted и inserted, мы реализуем ту же логику, что и в решении для MySQL, используя триггеры уровня записи.
Таким образом, отличие в решении для Oracle от решения для MySQL будет только в способе отмена операции (с одновременным выводом сообщения об ошибке): в Oracle для таких задач удобно использовать функцию RAISE_APPLICATION_ERROR.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 325/545
Пример 33: контроль операций модификации данных
В остальном решения для Oracle и MySQL полностью идентичны.
Oracle Решение 4.2.1.a (триггеры для таблицы subscriptions)
1-- Реакция на добавление выдачи книги.
2CREATE TRIGGER "subscriptions_control_ins"
3AFTER INSERT
4ON "subscriptions"
5FOR EACH ROW
6BEGIN
7
8-- Блокировка выдач книг с датой выдачи в будущем.
9IF :new."sb_start" > TRUNC(SYSDATE)
10THEN
11RAISE_APPLICATION_ERROR(-20001, 'Date ' || :new."sb_start" ||
12 |
|
' for subscription |
' || :new."sb_id" || |
13 |
|
' activation is in |
the future.'); |
14 |
|
END IF; |
|
15 |
|
|
|
|
|
|
|
16-- Блокировка выдач книг с датой возврата в прошлом.
17IF :new."sb_finish" < TRUNC(SYSDATE)
18THEN
19RAISE_APPLICATION_ERROR(-20002, 'Date ' || :new."sb_finish" ||
20 |
|
' for subscription ' || :new."sb_id" || |
21 |
|
' deactivation is in the past.'); |
22 |
|
END IF; |
23 |
|
|
24-- Блокировка выдач книг с датой возврата меньшей, чем дата выдачи.
25IF :new."sb_finish" < :new."sb_start"
26THEN
27RAISE_APPLICATION_ERROR(-20003, 'Date ' || :new."sb_finish" ||
28 |
|
' |
for |
subscription |
' |
|| |
:new."sb_id" || |
29 |
|
' |
deactivation is less than the date |
||||
30 |
|
|
for |
its activation |
(' |
|| |
|
31 |
|
:new."sb_start" || |
').'); |
||||
32END IF;
33END;
34
35-- Реакция на обновление выдачи книги.
36CREATE TRIGGER "subscriptions_control_upd"
37AFTER UPDATE
38ON "subscriptions"
39FOR EACH ROW
40BEGIN
41
42-- Блокировка выдач книг с датой выдачи в будущем.
43IF :new."sb_start" > TRUNC(SYSDATE)
44THEN
45RAISE_APPLICATION_ERROR(-20001, 'Date ' || :new."sb_start" ||
46 |
|
' for subscription |
' || :new."sb_id" || |
47 |
|
' activation is in |
the future.'); |
48 |
|
END IF; |
|
49 |
|
|
|
|
|
|
|
50-- Блокировка выдач книг с датой возврата меньшей, чем дата выдачи.
51IF :new."sb_finish" < :new."sb_start"
52THEN
53RAISE_APPLICATION_ERROR(-20003, 'Date ' || :new."sb_finish" ||
54 |
|
' for subscription ' || :new."sb_id" || |
55 |
|
' deactivation is less than the date |
56 |
|
for its activation (' || |
57 |
|
:new."sb_start" || ').'); |
58 |
|
END IF; |
59 |
|
|
60 |
|
END; |
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 326/545
Пример 33: контроль операций модификации данных
Проверить работоспособность полученного решения можно с помощью следующих запросов (их логика и ожидаемая реакция триггеров рассмотрены в реше-
нии для MySQL).
Oracle |
Решение 4.2.1.a (проверка работоспособности) |
1-- Деактивация триггера, формирующего значение автоинкрементируемого ПК:
2ALTER TRIGGER "TRG_subscriptions_sb_id" DISABLE;
3
4-- Добавление выдачи книги с датой активации в будущем:
5INSERT INTO "subscriptions"
6 |
|
VALUES |
(500, |
7 |
|
|
1, |
8 |
|
|
1, |
9 |
|
|
TO_DATE('2020-01-12', 'YYYY-MM-DD'), |
10 |
|
|
TO_DATE('2020-02-12', 'YYYY-MM-DD'), |
11 |
|
|
'N'); |
12 |
|
|
|
13-- Активация триггера, формирующего значение автоинкрементируемого ПК:
14ALTER TRIGGER "TRG_subscriptions_sb_id" ENABLE;
15
16-- Добавление выдачи книги с датой активации в будущем
17-- (без указания значения первичного ключа):
18INSERT INTO "subscriptions"
19 |
|
|
("sb_subscriber", |
20 |
|
|
"sb_book", |
21 |
|
|
"sb_start", |
22 |
|
|
"sb_finish", |
23 |
|
|
"sb_is_active") |
24 |
|
VALUES |
(3, |
25 |
|
|
3, |
26 |
|
|
TO_DATE('2020-01-12', 'YYYY-MM-DD'), |
27 |
|
|
TO_DATE('2020-02-12', 'YYYY-MM-DD'), |
28 |
|
|
'N'); |
29 |
|
|
|
30-- Добавление выдачи книги с датой возврата в прошлом:
31INSERT INTO "subscriptions"
32 |
|
|
("sb_subscriber", |
33 |
|
|
"sb_book", |
34 |
|
|
"sb_start", |
35 |
|
|
"sb_finish", |
36 |
|
|
"sb_is_active") |
37 |
|
VALUES |
(1, |
38 |
|
|
1, |
39 |
|
|
TO_DATE('2000-01-12', 'YYYY-MM-DD'), |
40 |
|
|
TO_DATE('2000-02-12', 'YYYY-MM-DD'), |
41 |
|
|
'N'); |
42 |
|
|
|
43-- Добавление выдачи книги без нарушения условий задачи:
44INSERT INTO "subscriptions"
45 |
|
|
("sb_subscriber", |
46 |
|
|
"sb_book", |
47 |
|
|
"sb_start", |
|
|
|
|
48 |
|
|
"sb_finish", |
49 |
|
|
"sb_is_active") |
50 |
|
VALUES |
(1, |
51 |
|
|
1, |
52 |
|
|
TO_DATE('2000-01-12', 'YYYY-MM-DD'), |
53 |
|
|
TO_DATE('2020-02-12', 'YYYY-MM-DD'), |
54 |
|
|
'N'); |
55 |
|
|
|
56-- Обновление добавленной выдачи книги таким образом, чтобы дата
57-- её активации оказалась в будущем:
58UPDATE "subscriptions"
59 |
|
SET |
"sb_start" = TO_DATE('2020-01-01', 'YYYY-MM-DD') |
60 |
|
WHERE |
"sb_id" = 104; |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 327/545
Пример 33: контроль операций модификации данных
Oracle |
Решение 4.2.1.a (проверка работоспособности) (продолжение) |
61-- Обновление добавленной выдачи книги таким образом, чтобы
62-- дата её активации оказалась позже даты возврата:
63UPDATE "subscriptions"
64 |
|
SET |
"sb_start" = TO_DATE('2010-01-01', 'YYYY-MM-DD'), |
65"sb_finish" = TO_DATE('2005-01-01', 'YYYY-MM-DD')
66WHERE "sb_id" = 104;
67
68-- Обновление добавленной выдачи книги таким образом, чтобы
69-- дата её возврата была в прошлом (для операции обновления
70-- такое разрешено):
71UPDATE "subscriptions"
72 SET |
"sb_start" = TO_DATE('2005-01-01', 'YYYY-MM-DD'), |
73"sb_finish" = TO_DATE('2006-01-01', 'YYYY-MM-DD')
74WHERE "sb_id" = 104;
75
76-- Обновление добавленной выдачи книги без нарушения условий задачи:
77UPDATE "subscriptions"
78 SET |
"sb_start" = TO_DATE('2005-01-01', 'YYYY-MM-DD'), |
79"sb_finish" = TO_DATE('2010-01-01', 'YYYY-MM-DD')
80WHERE "sb_id" = 104;
На этом решение данной задачи завершено.
Решение 4.2.1.b{315}.
На примере этой (достаточно простой) задачи продемонстрируем типичное неправильное решение, которое часто первым приходит в голову. Оно состоит в том, чтобы в AFTER-триггере проверить, существуют ли читатели, для которых нарушается условие задачи (выборкой по всем читателям) и, если да, «откатить транзакцию». На достаточно объёмной базе данных такое решение может приводить к очень заметному падению производительности.
Правильное же решение состоит в том, чтобы в BEFORE-триггере производить проверку выполнения условия задачи только для того читателя (тех читателей
— в MS SQL Server), для которого сейчас выполняется операция вставки или обновления записи в таблице subscriptions.
Итак, для всех трёх СУБД представим неправильное и правильное решение
исравним скорость их работы на базе данных «Большая библиотека».
Внеправильном решении для MySQL создадим INSERT- и UPDATE-триггеры с полностью идентичным кодом, в котором будем формировать список читателей, для которых было нарушено условие задачи (недопустимость выдачи более десяти книг).
При крайней неоптимальности с точки зрения производительности у этого решения всё же есть один плюс: оно будет реагировать в том числе и на все нарушения условия задачи, которые были совершены до создания триггера. Однако, если такое поведение нас не устраивает, этот плюс превращается в минус, и представленное решение становится ещё хуже, чем мы думали.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 328/545
Пример 33: контроль операций модификации данных
MySQL Решение 4.2.1.b (неправильное решение)
1 DELIMITER $$
2
3CREATE TRIGGER `sbs_cntrl_10_books_ins_WRONG`
4AFTER INSERT
5ON `subscriptions`
6FOR EACH ROW
7BEGIN
8 |
|
|
|
9 |
|
SET @msg = IFNULL((SELECT GROUP_CONCAT( |
|
10 |
|
CONCAT('(id=', `s_id`, ', ', `s_name`, |
|
11 |
|
', books=', `s_books`, ')') SEPARATOR ', ') |
|
12 |
|
AS `list` |
|
13 |
|
FROM (SELECT |
`s_id`, |
14 |
|
|
`s_name`, |
15 |
|
|
COUNT(`sb_book`) AS `s_books` |
16 |
|
FROM |
`subscribers` |
17 |
|
|
JOIN `subscriptions` |
18 |
|
|
ON `s_id` = `sb_subscriber` |
19 |
|
WHERE |
`sb_is_active` = 'Y' |
20 |
|
GROUP |
BY `sb_subscriber` |
21 |
|
HAVING |
`s_books` > 10) AS `prepared_data`), |
22 |
|
''); |
|
23 |
|
|
|
24IF (LENGTH(@msg) > 0)
25THEN
26SET @msg = CONCAT('The following readers have more books
27 |
than allowed (10 allowed): ', @msg); |
28SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @msg, MYSQL_ERRNO = 1001;
29END IF;
30
31END;
32$$
33
34CREATE TRIGGER `sbs_cntrl_10_books_upd_WRONG`
35AFTER UPDATE
36ON `subscriptions`
37FOR EACH ROW
38BEGIN
39 |
|
|
|
40 |
|
SET @msg = IFNULL((SELECT GROUP_CONCAT( |
|
41 |
|
CONCAT('(id=', `s_id`, ', ', `s_name`, |
|
42 |
|
', books=', `s_books`, ')') SEPARATOR ', ') |
|
43 |
|
AS `list` |
|
44 |
|
FROM (SELECT |
`s_id`, |
45 |
|
|
`s_name`, |
46 |
|
|
COUNT(`sb_book`) AS `s_books` |
47 |
|
FROM |
`subscribers` |
48 |
|
|
JOIN `subscriptions` |
49 |
|
|
ON `s_id` = `sb_subscriber` |
50 |
|
WHERE |
`sb_is_active` = 'Y' |
51 |
|
GROUP |
BY `sb_subscriber` |
52 |
|
HAVING |
`s_books` > 10) AS `prepared_data`), |
53 |
|
''); |
|
54 |
|
|
|
|
|
|
|
55IF (LENGTH(@msg) > 0)
56THEN
57SET @msg = CONCAT('The following readers have more books
58 |
|
than allowed (10 allowed): ', @msg); |
59SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @msg, MYSQL_ERRNO = 1001;
60END IF;
61
62END;
63$$
64
65 DELIMITER ;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 329/545