Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 33: контроль операций модификации данных
В остальном решения для Oracle и MySQL полностью идентичны.
Oracl |
і Решение 4.2.1.a (триггеры для таблицы subscriptions) |
| |
|
|
||
e |
|
|
||||
|
|
|
|
|
|
|
1 |
-- Реакция на добавление выдачи книги. |
|
|
|
||
2 |
CREATE TRIGGER "subscriptions control ins" |
|
|
|
||
3 |
AFTER INSERT |
|
|
|
|
|
4 |
ON "subscriptions" |
|
|
|
|
|
5 |
FOR EACH ROW |
|
|
|
|
|
6 |
BEGIN |
|
|
|
|
|
7 |
|
|
|
|
|
|
8 |
-- Блокировка выдач книг с датой выдачи в будущем. |
|
||||
9 |
IF |
new "sb start" > TRUNC(SYSDATE) |
|
|
|
|
10 |
THEN |
|
|
|
|
|
11 |
RAISE 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
19 |
RAISE 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
27 |
RAISE 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
45 |
RAISE 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
53 |
RAISE 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 Стр: 345/545
Пример 33: контроль операций модификации данных
Проверить работоспособность полученного решения можно с помощью следующих запросов (их логика и ожидаемая реакция триггеров рассмотрены в реше-
нии для MySQL).
Oracle |
Решение 4.2.1.a (проверка работоспособности) |
1--
2Деактивация триггера, формирующего значение автоинкрементируемого ПК:
3 |
ALTER TRIGGER |
"TRG_subscriptions_sb_id" DISABLE; |
4 |
|
|
5-- Добавление выдачи книги с датой активации в будущем:
6INSERT
7VALUES
8 |
|
|
9 |
INTO "subscriptions" 500, 1 ■ 1 , TO_DATE('2020-01-12', 'YYYY-MM- |
|
10 |
DD'), |
|
11 |
TO_DATE('2020-02-12', 'YYYY-MM-DD'), |
|
12 |
'N'); |
|
13 |
|
|
14 |
-- |
Активация триггера, |
15 |
формирующего значение |
автоинкрементируемого ПК: |
16 |
ALTER TRIGGER |
"TRG_subscriptions_sb_id" ENABLE; |
17 |
|
|
18 |
-- |
Добавление выдачи книги с датой |
19 |
активации |
в будущем |
20-- (без указания значения первичного ключа):
21INSERT
22 |
|
|
23 |
|
|
24 |
|
|
25 |
|
INTO "subscriptions" "sb_subscriber", "sb_book" "sb_start" |
26 |
|
"sb_finish", "sb_is_active" |
27 |
VALUES |
3 , |
28 |
|
3 , |
29 |
|
TO_DATE('2020-01-12', 'YYYY-MM-DD'), |
30 |
|
TO_DATE('2020-02-12', 'YYYY-MM-DD'), |
31 |
|
'N'); |
32 |
|
|
33-- Добавление выдачи книги с датой возврата в прошлом:
34INSERT
35 |
|
|
|
36 |
|
|
|
37 |
|
|
|
38 |
|
INTO "subscriptions" "sb_subscriber", "sb_book" "sb_start" |
|
39 |
|
"sb_finish", "sb_is_active" |
|
40 |
VALUES |
1, |
|
41 |
|
1 ■ |
|
42 |
|
TO_DATE('2000-01-12', 'YYYY-MM-DD'), |
|
43 |
|
TO_DATE('2000-02-12', |
'YYYY-MM-DD'), |
|
|
|
|
44 |
|
'N'); |
|
|
|
|
|
45 |
|
|
|
46 |
-- Добавление выдачи книги без нарушения условий задачи: |
||
47 |
INSERT |
|
|
48 |
|
|
|
49 |
|
|
|
50 |
|
|
|
51 |
|
INTO "subscriptions" "sb_subscriber", "sb_book" "sb_start" |
|
|
|
|
|
52 |
|
"sb_finish", "sb_is_active" |
|
|
|
|
|
53 |
VALUES |
1, |
|
54 |
|
1 ■ |
|
55 |
|
TO_DATE('2000-01-12', |
'YYYY-MM-DD'), |
|
|
|
|
56 |
|
TO_DATE('2020-02-12', |
'YYYY-MM-DD'), |
57 |
'N'); |
|
|
58 |
|
59--Обновление добавленной выдачи книги таким образом, чтобы дата
60--её активации оказалась в будущем:
UPDATE "subscriptions"
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 346/545
Пример 33: контроль операций модификации данных
SET "sb_start" = TO_DATE('2020-01-01', 'YYYY-MM-DD')
WHERE "sb id" = 104;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 347/545
Пример 33: контроль операций модификации данных
Oracle I |
Решение 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- |
'YYYY-MM-DD' ) |
66 |
WHERE |
"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- |
'YYYY-MM-DD' ) |
74 |
WHERE |
"sb id" = 104; |
|
75 |
|
|
|
76 |
-- Обновление добавленной выдачи книги без нарушения условий задачи: |
||
77 |
UPDATE |
"subscriptions" |
|
78 |
SET |
"sb start" = TO DATE('2005-01-01', 'YYYY-MM-DD'), |
|
79 |
|
"sb finish" = TO DATE('2010-01- |
'YYYY-MM-DD') |
80 |
WHERE |
"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 Стр: 348/545
Пример 33: контроль операций модификации данных
|
MySQL 1 |
Решение 4.2.1.b (неправильное решение) |
| |
1 |
DELIMITER $$ |
|
|
2 |
|
|
|
3 |
CREATE TRIGGER 'sbs cntrl 10 books ins WRONG' |
||
4 |
AFTER INSERT |
|
|
5 |
ON 'subscriptions' |
|
|
6 |
FOR EACH ROW |
|
|
7 |
|
BEGIN |
|
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 Стр: 349/545