Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 32: обеспечение консистентности данных
|
|
|
|
Старое значение |
|
Новое значение |
|
Действие |
|
Код действия |
|
|
|
|
|
sb_is_active |
|
sb_is_active |
|
|
|
|
|
|
Идентификатор |
|
|
Y |
|
Y |
|
- |
|
|
|
|
читателя |
|
|
Y |
|
N |
|
OLD-1 |
|
|
A |
|
остался неиз- |
|
|
N |
|
Y |
|
OLD+1 |
|
|
B |
|
менным |
|
|
N |
|
N |
|
- |
|
|
|
|
Идентификатор |
|
Y |
|
Y |
|
OLD-1, NEW+1 |
|
C |
||
|
|
Y |
|
N |
|
OLD-1 |
|
D |
|||
|
читателя изме- |
|
|
|
|
||||||
|
|
N |
|
Y |
|
NEW+1 |
|
E |
|||
|
нился |
|
|
|
|
||||||
|
|
N |
|
N |
- |
|
|
|
|||
|
|
|
|
|
|
|
|
||||
И, наконец, здесь мы приведём решение проблемы, описанной в заданиях
3.2.1.TSK.D{240}, 4.1.1.TSK.D{291}, 4.1.1.TSK.E{291}. Напомним, что MySQL не активирует триггеры каскадными операциями, потому удаление книг (которое приведёт к удалению всех записей о выдачах этих книг) активирует «незаметное» для DELETE- триггера на таблице subscriptions удаление данных.
Чтобы учесть этот эффект, мы создадим дополнительный триггер на таблице books, реагирующий на удаление книг (вставка или обновление данных в таблице books не влияет на распределение уже имеющихся выдач книг по тем или иным читателям, потому здесь достаточно создать только DELETE-триггер).
Обратите внимание: здесь мы создаём BEFORE-триггер, т.к. в момент активации AFTER-триггера искомая информация в таблице subscriptions уже будет удалена.
MySQL |
Решение 4.1.2.a (триггер для таблицы books) |
1 DELIMITER $$
2
3-- Реакция на удаление книги:
4CREATE TRIGGER `s_has_books_on_books_del`
5BEFORE DELETE
6ON `books`
7FOR EACH ROW
8BEGIN
9UPDATE `subscribers`
10 |
|
JOIN (SELECT |
`sb_subscriber`, |
|
11 |
|
|
COUNT(`sb_book`) |
AS `delta` |
12 |
|
FROM |
`subscriptions` |
|
13 |
|
WHERE |
`sb_book` = OLD.`b_id` |
|
14 |
|
AND |
`sb_is_active` = |
'Y' |
15GROUP BY `sb_subscriber`) AS `prepared_data`
16ON `s_id` = `sb_subscriber`
17 |
|
SET |
`s_books` = `s_books` - `delta`; |
18 |
|
END; |
|
19 |
|
$$ |
|
20 |
|
|
|
21 |
|
DELIMITER ; |
|
Проверим работоспособность полученного решения. Будем изменять данные в таблицах books и subscriptions и отслеживать изменения данных в таб-
лице subscribers.
Исходное состояние таблицы subscribers таково:
s_id |
s_name |
s_books |
1 |
Иванов И.И. |
0 |
2 |
Петров П.П. |
0 |
3 |
Сидоров С.С. |
3 |
4 |
Сидоров С.С. |
2 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 295/545
Пример 32: обеспечение консистентности данных
Проверим реакцию на добавление и удаление выдач книг. Добавим Иванову И.И. активную выдачу, а Петрову П.П. неактивную:
|
MySQL |
|
Решение 4.1.2.a (проверка работоспособности) |
||||
|
1 |
INSERT INTO `subscriptions` |
|||||
|
2 |
VALUES |
(200, |
|
|||
|
3 |
|
|
|
1, |
|
|
|
4 |
|
|
|
1, |
|
|
|
5 |
|
|
|
'2011-01-12', |
||
|
6 |
|
|
|
'2011-02-12', |
||
|
7 |
|
|
|
'Y'), |
||
|
8 |
|
|
|
(201, |
|
|
|
9 |
|
|
|
2, |
|
|
|
10 |
|
|
|
1, |
|
|
|
11 |
|
|
|
'2011-01-12', |
||
|
12 |
|
|
|
'2011-02-12', |
||
|
13 |
|
|
|
'N') |
||
|
|
|
|
|
|
|
|
|
s_id |
|
|
s_name |
|
s_books |
|
|
1 |
|
Иванов И.И. |
1 |
|
||
|
2 |
|
Петров П.П. |
0 |
|
||
|
3 |
|
Сидоров С.С. |
3 |
|
||
|
4 |
|
Сидоров С.С. |
2 |
|
||
Удалим добавленные выдачи:
MySQL Решение 4.1.2.a (проверка работоспособности)
1DELETE FROM `subscriptions`
2WHERE `sb_id` IN ( 200, 201 )
s_id |
s_name |
s_books |
1 |
Иванов И.И. |
0 |
2 |
Петров П.П. |
0 |
3 |
Сидоров С.С. |
3 |
4 |
Сидоров С.С. |
2 |
Проверим реакцию на обновление выдач книг. Сначала добавим выдачу:
|
MySQL |
|
Решение 4.1.2.a (проверка работоспособности) |
||||
|
1 |
INSERT INTO `subscriptions` |
|||||
|
2 |
VALUES |
(300, |
|
|||
|
3 |
|
|
|
1, |
|
|
|
4 |
|
|
|
1, |
|
|
|
5 |
|
|
|
'2011-01-12', |
||
|
6 |
|
|
|
'2011-02-12', |
||
|
7 |
|
|
|
'Y') |
||
|
|
|
|
|
|
|
|
|
s_id |
|
|
s_name |
|
s_books |
|
|
1 |
|
Иванов И.И. |
1 |
|
||
|
2 |
|
Петров П.П. |
0 |
|
||
|
3 |
|
Сидоров С.С. |
3 |
|
||
|
4 |
|
Сидоров С.С. |
2 |
|
||
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 296/545
Пример 32: обеспечение консистентности данных
Не меняя идентификатор читателя сделаем выдачу неактивной:
MySQL Решение 4.1.2.a (проверка работоспособности)
1-- A
2UPDATE `subscriptions`
|
3 |
|
SET |
`sb_is_active` = 'N' |
||
|
4 |
|
WHERE |
`sb_id` = 300 |
||
|
|
|
|
|
||
s_id |
s_name |
s_books |
|
|||
|
1 |
|
Иванов И.И. |
0 |
|
|
2 |
|
Петров П.П. |
0 |
|
||
3 |
|
Сидоров С.С. |
3 |
|
||
4 |
|
Сидоров С.С. |
2 |
|
||
Не меняя идентификатор читателя сделаем выдачу снова активной:
MySQL Решение 4.1.2.a (проверка работоспособности)
1-- B
2UPDATE `subscriptions`
|
3 |
|
SET |
`sb_is_active` = 'Y' |
||
|
4 |
|
WHERE |
`sb_id` = 300 |
||
|
|
|
|
|
||
s_id |
s_name |
s_books |
|
|||
|
1 |
|
Иванов И.И. |
1 |
|
|
2 |
|
Петров П.П. |
0 |
|
||
3 |
|
Сидоров С.С. |
3 |
|
||
4 |
|
Сидоров С.С. |
2 |
|
||
Изменим идентификатор читателя, не меняя состояние активности выдачи:
MySQL Решение 4.1.2.a (проверка работоспособности)
1-- C
2UPDATE `subscriptions`
|
3 |
|
SET |
`sb_subscriber` = 2 |
||
|
4 |
|
WHERE |
`sb_id` = 300 |
||
|
|
|
|
|
||
s_id |
s_name |
s_books |
|
|||
|
1 |
|
Иванов И.И. |
0 |
|
|
|
2 |
|
Петров П.П. |
1 |
|
|
3 |
|
Сидоров С.С. |
3 |
|
||
4 |
|
Сидоров С.С. |
2 |
|
||
Изменим идентификатор читателя и сделаем выдачу неактивной:
MySQL Решение 4.1.2.a (проверка работоспособности)
1-- D
2UPDATE `subscriptions`
3 |
|
SET |
`sb_subscriber` = 1, |
4`sb_is_active` = 'N'
5WHERE `sb_id` = 300
s_id |
s_name |
s_books |
1 |
Иванов И.И. |
0 |
2 |
Петров П.П. |
0 |
3 |
Сидоров С.С. |
3 |
4 |
Сидоров С.С. |
2 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 297/545
Пример 32: обеспечение консистентности данных
Изменим идентификатор читателя и сделаем выдачу активной:
MySQL Решение 4.1.2.a (проверка работоспособности)
1-- E
2UPDATE `subscriptions`
3 |
|
SET |
`sb_subscriber` = 2, |
|
|
|
|
4`sb_is_active` = 'Y'
5WHERE `sb_id` = 300
s_id |
s_name |
s_books |
1 |
Иванов И.И. |
0 |
2 |
Петров П.П. |
1 |
3 |
Сидоров С.С. |
3 |
4 |
Сидоров С.С. |
2 |
Удалим книгу с идентификатором 1 (такая книга сейчас выдана Петрову и обоим Сидоровым по одному экземпляру):
MySQL Решение 4.1.2.a (проверка работоспособности)
1DELETE FROM `books`
2WHERE `b_id` = 1
s_id |
s_name |
s_books |
1 |
Иванов И.И. |
0 |
2 |
Петров П.П. |
0 |
3 |
Сидоров С.С. |
2 |
4 |
Сидоров С.С. |
1 |
Как показало исследование, все операции выполняются корректно и приводят к верным изменениям значений поля s_books.
Переходим к решению поставленной задачи для MS SQL Server. Модифицируем таблицу subscribers и проинициализируем добавленное поле данными.
MS SQL Решение 4.1.2.a (модификация таблицы и инициализация данных)
1-- Модификация таблицы:
2ALTER TABLE [subscribers]
3ADD [s_books] INT NOT NULL DEFAULT 0;
4
5-- Инициализация данных:
6UPDATE [subscribers]
7 |
|
SET |
[s_books] = |
[s_has_books] |
8 |
|
FROM |
[subscribers] |
|
9 |
|
|
JOIN (SELECT |
[sb_subscriber], |
10 |
|
|
|
COUNT([sb_id]) AS [s_has_books] |
11 |
|
|
FROM |
[subscriptions] |
12 |
|
|
WHERE |
[sb_is_active] = 'Y' |
13 |
|
|
GROUP |
BY [sb_subscriber]) AS [prepared_data] |
14 |
|
|
ON [s_id] = |
[sb_subscriber]; |
Логика работы триггеров в MS SQL Server будет иной, т.к. эта СУБД не поддерживает триггеры уровня записи, и нам придётся за один раз обрабатывать все произведённые изменения.
С INSERT- и DELETE-триггерами, как и в случае с MySQL, будет более-менее просто — нужно выяснить идентификаторы читателей, получивших (или вернувших) книги, количество таких книг по каждому читателю, а затем увеличить или уменьшить счётчики выданных книг соответствующим читателям на соответствующие величины.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 298/545
Пример 32: обеспечение консистентности данных
MS SQL Решение 4.1.2.a (триггеры для таблицы subscriptions)
1-- Реакция на добавление выдачи книги:
2CREATE TRIGGER [s_has_books_on_subscriptions_ins]
3ON [subscriptions]
4AFTER INSERT
5AS
6UPDATE [subscribers]
7 |
|
SET |
[s_books] = [s_books] + [s_new_books] |
|
8 |
|
FROM |
[subscribers] |
|
9 |
|
|
JOIN (SELECT |
[sb_subscriber], |
10 |
|
|
|
COUNT([sb_id]) AS [s_new_books] |
11 |
|
|
FROM |
[inserted] |
12 |
|
|
|
WHERE [sb_is_active] = 'Y' |
13 |
|
|
GROUP |
BY [sb_subscriber]) AS [prepared_data] |
14ON [s_id] = [sb_subscriber];
15GO
16
17-- Реакция на удаление выдачи книги:
18CREATE TRIGGER [s_has_books_on_subscriptions_del]
19ON [subscriptions]
20AFTER DELETE
21AS
22UPDATE [subscribers]
23 |
|
SET |
[s_books] = [s_books] - [s_old_books] |
|
24 |
|
FROM |
[subscribers] |
|
25 |
|
|
JOIN (SELECT |
[sb_subscriber], |
26 |
|
|
|
COUNT([sb_id]) AS [s_old_books] |
27 |
|
|
FROM |
[deleted] |
28 |
|
|
|
WHERE [sb_is_active] = 'Y' |
29 |
|
|
GROUP |
BY [sb_subscriber]) AS [prepared_data] |
30 |
|
|
ON [s_id] = [sb_subscriber]; |
|
31 |
|
|
|
|
32 |
|
GO |
|
|
UPDATE-триггер получится чуть более сложным, но не настолько, как в MySQL: здесь мы можем посчитать количество сданных и возвращённых книг на основе информации из псевдотаблиц deleted и inserted, и при этом нам не важно изменение состояния выдач книг — мы лишь считаем (независимо) количество сданных и полученных книг для каждого читателя и изменяем его счётчик книг на эти две величины.
Иными словами, нам не важно, из какого в какое состояние (и от какого к какому читателю) переключается выдача — нас интересует только «у кого удалились активные выдачи», и «кому добавились активные выдачи».
Потому UPDATE-триггер будет просто содержать в себе код из INSERT- триггера и DELETE-триггера. Чуть более элегантным решением была бы реализация в теле триггера такой логики, при которой было бы достаточно выполнить только одну операцию обновления таблицы subscribers — в этом и будет состо-
ять задание 4.1.2.TSK.C{314}.
Для проверки работоспособности полученного решения можно использовать запросы, представленные после кода UPDATE-триггера (их общая логика и ожидаемая реакция СУБД пояснены в решении для MySQL, но обратите внимание, что здесь мы оперируем чуть большим количеством данных в каждом запросе).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 299/545