Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 31: обновление кэширующих таблиц и полей
Oracle Решение 4.1.1.a (запросы для проверки работоспособности)
1 ALTER TRIGGER "TRG_subscriptions_sb_id" DISABLE; 2
3-- Добавление выдачи книги читателю с идентификатором 2
4-- (ранее он никогда не был в библиотеке):
5INSERT INTO "subscriptions"
6 |
|
|
("sb_id", |
7 |
|
|
"sb_subscriber", |
8 |
|
|
"sb_book", |
9 |
|
|
"sb_start", |
10 |
|
|
"sb_finish", |
11 |
|
|
"sb_is_active") |
12 |
|
VALUES |
(200, |
13 |
|
|
2, |
14 |
|
|
1, |
15 |
|
|
TO_DATE('2019-01-12', 'YYYY-MM-DD'), |
16 |
|
|
TO_DATE('2019-02-12', 'YYYY-MM-DD'), |
17 |
|
|
'N'); |
18 |
|
|
|
19-- Изменение идентификатора читателя в только что
20-- добавленной выдаче с 2 на 1:
21UPDATE "subscriptions"
22 |
|
SET |
"sb_subscriber" = 1 |
23 |
|
WHERE |
"sb_id" = 200; |
24 |
|
|
|
25-- Ещё одна выдача книги Петрову П.П.
26-- (идентификатор читателя = 2):
27INSERT INTO "subscriptions"
28 |
|
|
("sb_id", |
29 |
|
|
"sb_subscriber", |
30 |
|
|
"sb_book", |
31 |
|
|
"sb_start", |
32 |
|
|
"sb_finish", |
33 |
|
|
"sb_is_active") |
34 |
|
VALUES |
(201, |
35 |
|
|
2, |
36 |
|
|
1, |
37 |
|
|
TO_DATE('2020-01-12', 'YYYY-MM-DD'), |
38 |
|
|
TO_DATE('2020-02-12', 'YYYY-MM-DD'), |
39 |
|
|
'N'); |
40 |
|
|
|
41-- Изменение значения даты ранее откорректированной
42-- выдачи книги (которую переписали с Петрова П.П.
43-- на Иванова И.И.):
44UPDATE "subscriptions"
45 |
|
SET |
"sb_start" = TO_DATE('2018-01-12', 'YYYY-MM-DD') |
46 |
|
WHERE |
"sb_id" = 200; |
47 |
|
|
|
48-- Удаление этой откорректированной выдачи:
49DELETE FROM "subscriptions"
50WHERE "sb_id" = 200;
51
52-- Удаление единственной выдачи Петрову П.П.:
53DELETE FROM "subscriptions"
54WHERE "sb_id" = 201;
55
56 ALTER TRIGGER "TRG_subscriptions_sb_id" ENABLE;
На этом решение данной задачи завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 280/545
Пример 31: обновление кэширующих таблиц и полей
Решение 4.1.1.b{272}.
Во многом решение данной задачи похоже на решение{216} задачи 3.1.2.a{215}, которое рекомендуется повторить перед тем, как продолжить чтение.
Как обычно, начнём с MySQL. Создадим агрегирующую таблицу:
MySQL Решение 4.1.1.b (создание агрегирующей таблицы)
1CREATE TABLE `averages`
2(
3`books_taken` DOUBLE NOT NULL,
4`days_to_read` DOUBLE NOT NULL,
5`books_returned` DOUBLE NOT NULL
6)
Проинициализируем данные в созданной таблице:
MySQL Решение 4.1.1.b (очистка таблицы и инициализация данных)
1-- Очистка таблицы:
2TRUNCATE TABLE `averages`;
3
4-- Инициализация данных:
5INSERT INTO `averages`
6 |
|
|
(`books_taken`, |
|
|
7 |
|
|
`days_to_read`, |
|
|
8 |
|
|
`books_returned`) |
|
|
9 |
|
SELECT |
( `active_count` / `subscribers_count` ) |
AS `books_taken`, |
|
10 |
|
|
( `days_sum` / `inactive_count` ) |
AS `days_to_read`, |
|
11 |
|
|
( `inactive_count` / `subscribers_count` ) AS `books_returned` |
||
12 |
|
FROM |
(SELECT |
COUNT(`s_id`) AS `subscribers_count` |
|
13 |
|
|
FROM |
`subscribers`) AS `tmp_subscribers_count`, |
|
14 |
|
|
(SELECT |
COUNT(`sb_id`) AS `active_count` |
|
15 |
|
|
FROM |
`subscriptions` |
|
16WHERE `sb_is_active` = 'Y') AS `tmp_active_count`,
17(SELECT COUNT(`sb_id`) AS `inactive_count`
18 FROM `subscriptions`
19WHERE `sb_is_active` = 'N') AS `tmp_inactive_count`,
20(SELECT SUM(DATEDIFF(`sb_finish`, `sb_start`)) AS `days_sum`
21 |
|
FROM |
`subscriptions` |
22 |
|
WHERE |
`sb_is_active` = 'N') AS `tmp_days_sum`; |
|
|
|
|
Напишем триггеры, модифицирующие данные в агрегирующей таблице. Агрегация происходит на основе информации, представленной в таблицах subscribers и subscriptions, потому придётся создавать триггеры для обеих этих таблиц.
Чтобы не усложнять решение, мы будем использовать один и тот же код для всех пяти триггеров (на таблице subscribers должны быть только INSERT- и DE- LETE-триггеры, т.к. обновление этой таблицы не влияет на результаты вычислений, а на таблице subscriptions должны быть все три триггера: INSERT, UPDATE, DE-
LETE).
Важно! В MySQL триггеры не активируются каскадными операциями, потому изменения в таблице subscriptions, вызванные удалением книг, останутся «незаметными» для триггеров на этой таблице. В задании 4.1.1.TSK.E{291} вам предлагается доработать данное решение, устранив эту проблему.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 281/545
Пример 31: обновление кэширующих таблиц и полей
MySQL 
Решение 4.1.1.b (триггеры для таблицы subscribers)
1-- Удаление старых версий триггеров
2-- (удобно в процессе разработки и отладки):
3DROP TRIGGER `upd_avgs_on_subscribers_ins`;
4DROP TRIGGER `upd_avgs_on_subscribers_del`;
5
6-- Переключение разделителя завершения запроса,
7-- т.к. сейчас запросом будет создание триггера,
8-- внутри которого есть свои, классические запросы:
9DELIMITER $$
10
11-- Создание триггера, реагирующего на добавление читателей:
12CREATE TRIGGER `upd_avgs_on_subscribers_ins`
13AFTER INSERT
14ON `subscribers`
15FOR EACH ROW
16BEGIN
17UPDATE `averages`,
18(SELECT COUNT(`s_id`) AS `subscribers_count`
19 |
|
|
FROM |
`subscribers`) AS `tmp_subscribers_count`, |
|
20 |
|
|
(SELECT |
COUNT(`sb_id`) AS `active_count` |
|
21 |
|
|
FROM |
`subscriptions` |
|
22 |
|
|
WHERE |
`sb_is_active` = 'Y') AS |
`tmp_active_count`, |
23 |
|
|
(SELECT |
COUNT(`sb_id`) AS `inactive_count` |
|
24 |
|
|
FROM |
`subscriptions` |
|
25 |
|
|
WHERE |
`sb_is_active` = 'N') AS |
`tmp_inactive_count`, |
26 |
|
|
(SELECT |
SUM(DATEDIFF(`sb_finish`, `sb_start`)) AS `days_sum` |
|
27 |
|
|
FROM |
`subscriptions` |
|
28 |
|
|
WHERE |
`sb_is_active` = 'N') AS |
`tmp_days_sum` |
29 |
|
SET |
`books_taken` = `active_count` / |
`subscribers_count`, |
|
30`days_to_read` = `days_sum` / `inactive_count`,
31`books_returned` = `inactive_count` / `subscribers_count`;
32END;
33$$
34
35-- Создание триггера, реагирующего на удаление читателей:
36CREATE TRIGGER `upd_avgs_on_subscribers_del`
37AFTER DELETE
38ON `subscribers`
39FOR EACH ROW
40BEGIN
41UPDATE `averages`,
42(SELECT COUNT(`s_id`) AS `subscribers_count`
43 |
|
|
FROM |
`subscribers`) AS `tmp_subscribers_count`, |
44 |
|
|
(SELECT |
COUNT(`sb_id`) AS `active_count` |
45 |
|
|
FROM |
`subscriptions` |
46 |
|
|
WHERE |
`sb_is_active` = 'Y') AS `tmp_active_count`, |
47 |
|
|
(SELECT |
COUNT(`sb_id`) AS `inactive_count` |
48 |
|
|
FROM |
`subscriptions` |
49 |
|
|
WHERE |
`sb_is_active` = 'N') AS `tmp_inactive_count`, |
50 |
|
|
(SELECT |
SUM(DATEDIFF(`sb_finish`, `sb_start`)) AS `days_sum` |
51 |
|
|
FROM |
`subscriptions` |
52 |
|
|
WHERE |
`sb_is_active` = 'N') AS `tmp_days_sum` |
53 |
|
SET |
`books_taken` = `active_count` / `subscribers_count`, |
|
45 |
|
|
`days_to_read` = `days_sum` / `inactive_count`, |
|
|
|
|
|
|
55`books_returned` = `inactive_count` / `subscribers_count`;
56END;
57$$
58
59-- Восстановление разделителя завершения запросов:
60DELIMITER ;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 282/545
Пример 31: обновление кэширующих таблиц и полей
Создадим триггеры на таблице subscriptions.
MySQL Решение 4.1.1.b (триггеры для таблицы subscriptions)
1-- Удаление старых версий триггеров
2-- (удобно в процессе разработки и отладки):
3DROP TRIGGER `upd_avgs_on_subscriptions_ins`;
4DROP TRIGGER `upd_avgs_on_subscriptions_upd`;
5DROP TRIGGER `upd_avgs_on_subscriptions_del`;
6
7-- Переключение разделителя завершения запроса,
8-- т.к. сейчас запросом будет создание триггера,
9-- внутри которого есть свои, классические запросы:
10DELIMITER $$
11
12-- Создание триггера, реагирующего на добавление выдачи книги:
13CREATE TRIGGER `upd_avgs_on_subscriptions_ins`
14AFTER INSERT
15ON `subscriptions`
16FOR EACH ROW
17BEGIN
18UPDATE `averages`,
19(SELECT COUNT(`s_id`) AS `subscribers_count`
20 |
|
|
FROM |
`subscribers`) AS `tmp_subscribers_count`, |
|
21 |
|
|
(SELECT |
COUNT(`sb_id`) AS `active_count` |
|
22 |
|
|
FROM |
`subscriptions` |
|
23 |
|
|
WHERE |
`sb_is_active` = 'Y') AS |
`tmp_active_count`, |
24 |
|
|
(SELECT |
COUNT(`sb_id`) AS `inactive_count` |
|
25 |
|
|
FROM |
`subscriptions` |
|
26 |
|
|
WHERE |
`sb_is_active` = 'N') AS |
`tmp_inactive_count`, |
27 |
|
|
(SELECT |
SUM(DATEDIFF(`sb_finish`, `sb_start`)) AS `days_sum` |
|
28 |
|
|
FROM |
`subscriptions` |
|
29 |
|
|
WHERE |
`sb_is_active` = 'N') AS |
`tmp_days_sum` |
30 |
|
SET |
`books_taken` = `active_count` / |
`subscribers_count`, |
|
31`days_to_read` = `days_sum` / `inactive_count`,
32`books_returned` = `inactive_count` / `subscribers_count`;
33END;
34$$
35
36-- Создание триггера, реагирующего на обновление выдачи книги:
37CREATE TRIGGER `upd_avgs_on_subscriptions_upd`
38AFTER UPDATE
39ON `subscriptions`
40FOR EACH ROW
41BEGIN
42UPDATE `averages`,
43(SELECT COUNT(`s_id`) AS `subscribers_count`
44 |
|
|
FROM |
`subscribers`) AS `tmp_subscribers_count`, |
|
45 |
|
|
(SELECT |
COUNT(`sb_id`) AS `active_count` |
|
46 |
|
|
FROM |
`subscriptions` |
|
47 |
|
|
WHERE |
`sb_is_active` = 'Y') AS |
`tmp_active_count`, |
48 |
|
|
(SELECT |
COUNT(`sb_id`) AS `inactive_count` |
|
49 |
|
|
FROM |
`subscriptions` |
|
50 |
|
|
WHERE |
`sb_is_active` = 'N') AS |
`tmp_inactive_count`, |
51 |
|
|
(SELECT |
SUM(DATEDIFF(`sb_finish`, `sb_start`)) AS `days_sum` |
|
52 |
|
|
FROM |
`subscriptions` |
|
53 |
|
|
WHERE |
`sb_is_active` = 'N') AS |
`tmp_days_sum` |
45 |
|
SET |
`books_taken` = `active_count` / |
`subscribers_count`, |
|
55`days_to_read` = `days_sum` / `inactive_count`,
56`books_returned` = `inactive_count` / `subscribers_count`;
57END;
58$$
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 283/545
Пример 31: обновление кэширующих таблиц и полей
MySQL |
Решение 4.1.1.b (триггеры для таблицы subscriptions) (продолжение) |
59-- Создание триггера, реагирующего на удаление выдачи книги:
60CREATE TRIGGER `upd_avgs_on_subscriptions_del`
61AFTER DELETE
62ON `subscriptions`
63FOR EACH ROW
64BEGIN
65UPDATE `averages`,
66(SELECT COUNT(`s_id`) AS `subscribers_count`
67 |
|
|
FROM |
`subscribers`) AS `tmp_subscribers_count`, |
|
68 |
|
|
(SELECT |
COUNT(`sb_id`) AS `active_count` |
|
69 |
|
|
FROM |
`subscriptions` |
|
70 |
|
|
WHERE |
`sb_is_active` = 'Y') AS |
`tmp_active_count`, |
71 |
|
|
(SELECT |
COUNT(`sb_id`) AS `inactive_count` |
|
72 |
|
|
FROM |
`subscriptions` |
|
73 |
|
|
WHERE |
`sb_is_active` = 'N') AS |
`tmp_inactive_count`, |
74 |
|
|
(SELECT |
SUM(DATEDIFF(`sb_finish`, `sb_start`)) AS `days_sum` |
|
75 |
|
|
FROM |
`subscriptions` |
|
76 |
|
|
WHERE |
`sb_is_active` = 'N') AS |
`tmp_days_sum` |
77 |
|
SET |
`books_taken` = `active_count` / |
`subscribers_count`, |
|
78`days_to_read` = `days_sum` / `inactive_count`,
79`books_returned` = `inactive_count` / `subscribers_count`;
80END;
81$$
82
83-- Восстановление разделителя завершения запросов:
84DELIMITER ;
Проверим работоспособность полученного решения. Будем изменять данные в таблицах subscribers и subscriptions и отслеживать изменения данных в таблице averages.
Исходное состояние таблицы averages таково:
|
books_taken |
days_to_read |
books_returned |
|
||||
|
1.25 |
|
|
|
46 |
|
1.5 |
|
|
|
|
Добавим читателя: |
|
|
|||
|
|
|
|
|
|
|||
|
MySQL |
|
Решение 4.1.1.b (проверка работоспособности) |
|
||||
|
1 |
INSERT INTO `subscribers` |
|
|
||||
|
2 |
|
|
|
(`s_id`, |
|
|
|
|
3 |
|
|
|
`s_name`) |
|
|
|
|
4 |
VALUES |
(500, |
|
|
|
||
|
5 |
|
|
|
'Читателев Ч.Ч.') |
|
||
|
|
|
|
|||||
|
books_taken |
days_to_read |
|
books_returned |
|
|||
|
1 |
|
|
|
46 |
|
1.2 |
|
Теперь удалим его:
MySQL Решение 4.1.1.b (проверка работоспособности)
1DELETE FROM `subscribers`
2WHERE `s_id` = 500
books_taken days_to_read books_returned |
||
1.25 |
46 |
1.5 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 284/545