Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 31: обновление кэширующих таблиц и полей
.i.a
11-- Создание триггера, реагирующего на добавление выдачи книг:
12CREATE TRIGGER 'last_visit_on_subscriptions_ins'
13AFTER INSERT
14ON 'subscriptions'
15FOR EACH ROW
16BEGIN
17IF (SELECT IFNULL('s_last_visit', '1970-01-01')
18 FROM 'subscribers'
19WHERE 's_id' = NEW.'sb_subscriber') < NEW.'sb_start'
20THEN
21UPDATE 'subscribers'
22 |
SET |
's_last_visit' = NEW.'sb_start' |
23WHERE 's_id' = NEW.'sb_subscriber';
24END IF;
25END;
26$$
27
28-- Создание триггера, реагирующего на обновление выдачи книг:
29CREATE TRIGGER 'last_visit_on_subscriptions_upd'
30AFTER UPDATE
31ON 'subscriptions'
32FOR EACH ROW
33BEGIN
34UPDATE 'subscribers'
35LEFT JOIN (SELECT 'sb_subscriber',
36 |
|
MAX('sb_start') AS 'last_visit' |
37 |
|
FROM 'subscriptions' |
38 |
|
GROUP BY 'sb_subscriber') AS 'prepared_data' |
39 |
|
ON 's_id' = 'sb_subscriber' |
40 |
SET |
's_last_visit' = 'last_visit' |
41WHERE 's_id' IN (OLD.'sb_subscriber', NEW.'sb_subscriber');
42END;
43$$
44
45-- Создание триггера, реагирующего на удаление выдачи книг:
46CREATE TRIGGER 'last_visit_on_subscriptions_del'
47AFTER DELETE
48ON 'subscriptions'
49FOR EACH ROW
50BEGIN
51UPDATE 'subscribers'
52LEFT JOIN (SELECT 'sb_subscriber',
53 |
|
MAX('sb_start') AS 'last_visit' |
54 |
|
FROM 'subscriptions' |
55 |
|
GROUP BY 'sb_subscriber') AS 'prepared_data' |
56 |
|
ON 's_id' = 'sb_subscriber' |
57 |
SET |
's_last_visit' = 'last_visit' |
58WHERE 's_id' = OLD.'sb_subscriber';
59END;
60$$
61
62-- Восстановление разделителя завершения запросов:
63DELIMITER ;
Проверим работоспособность полученного решения. Будем изменять данные в таблице subscriptions и отслеживать изменения данных в таблице subscribers.
Для начала добавим выдачу книги читателю с идентификатором 2 (ранее он никогда не был в библиотеке):
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 290/545
Пример 31: обновление кэширующих таблиц и полей
|
MySQL I Решение |
.1.a (проверка работоспособности) | |
|||
|
4.1 |
|
|||
|
1 |
INSERT INTO |
'subscriptions' |
||
|
2 |
VALUES |
200, |
|
|
|
3 |
|
2 |
|
|
|
4 |
|
1 |
|
|
|
5 |
|
'2019-01-12', |
|
|
|
6 |
|
'2019-02-12', |
|
|
|
7 |
|
'N' ) |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
s_id |
s_name |
|
s_last_visit |
|
|
1 |
Иванов И.И. |
|
2015-10-07 |
|
|
2 |
Петров П.П. |
|
2019-01-12 |
|
|
3 |
Сидоров С.С. |
2014-08-03 |
|
|
|
4 |
Сидоров С.С. |
2015-10-08 |
|
|
Теперь эмулируем ситуацию «книга ошибочно записана на другого читателя» и изменим идентификатор читателя в только что добавленной выдаче с 2 на 1. NULL-значение даты последнего визита Петрова П.П. корректно восстановилось:
MySQL I Решение 4.1.1.a (проверка работоспособности)
1 |
UPDATE |
'subscriptions' |
||
2 |
SET |
'sb_subscriber' = 1 |
||
3 |
WHERE |
'sb id' = 200 |
||
|
|
|
|
|
s_id |
|
|
s_name |
s_last_visit |
1 |
|
Иванов И.И. |
2019-01-12 |
|
2 |
|
Петров П.П. |
NULL |
|
3 |
|
Сидоров С.С. |
2014-08-03 |
|
4 |
|
Сидоров С.С. |
2015-10-08 |
|
Снова добавим выдачу книги Петрову П.П.:
|
MySQL І Решение |
.1.a (проверка работоспособности) | |
|||
|
4.1 |
|
|||
|
1 |
INSERT INTO |
'subscriptions' |
||
|
2 |
VALUES |
201, |
|
|
|
3 |
|
2 |
|
|
|
4 |
|
1 |
|
|
|
5 |
|
'2020-01-12', |
|
|
|
6 |
|
'2020-02-12', |
|
|
|
7 |
|
'N' ) |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
s_id |
s_name |
|
s_last_visit |
|
|
1 |
Иванов И.И. |
|
2019-01-12 |
|
|
2 |
Петров П.П. |
|
2020-01-12 |
|
|
3 |
Сидоров С.С. |
2014-08-03 |
|
|
|
4 |
Сидоров С.С. |
2015-10-08 |
|
|
Изменим значение даты ранее откорректированной выдачи книги (которую мы переписали с Петрова П.П. на Иванова И.И.):
|
|
Решение 4.1.1.a |
|
|
|
1 |
UPDATE |
'subscriptions' |
|||
2 |
SET |
'sb start' = '2018-01-12' |
|||
3 |
WHERE 'sb id' = 200 |
||||
|
|
|
|
|
|
s_id |
|
s_name |
s_last_visit |
|
|
1 |
|
Иванов И.И. |
2018-01-12 |
|
|
2 |
|
Петров П.П. |
2020-01-12 |
|
|
3 |
|
Сидоров С.С. |
2014-08-03 |
|
|
4 |
|
Сидоров С.С. |
2015-10-08 |
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 291/545
Пример 31: обновление кэширующих таблиц и полей
Удалим эту выдачу:
MySQL |
Решение 4.1.1.a (проверка работоспособности) |
||||
|
1 |
DELETE FROM 'subscriptions' |
|||
2 |
WHERE 'sb id' = 200 |
||||
|
|
|
|
|
|
s_id |
|
|
s_name |
s_last_visit |
|
1 |
|
Иванов И.И. |
2015-10-07 |
|
|
2 |
|
Петров П.П. |
2020-01-12 |
|
|
3 |
|
Сидоров С.С. |
2014-08-03 |
|
|
4 |
|
Сидоров С.С. |
2015-10-08 |
|
|
Удалим единственную выдачу книги Петрову П.П.:
Решение 4.1.1.a
1DELETE FROM 'subscriptions'
2WHERE 'sb id' = 201
s_id |
s_name |
s_last_visit |
1 |
Иванов И.И. |
2015-10-07 |
2 |
Петров П.П. |
NULL |
3 |
Сидоров С.С. |
2014-08-03 |
4 |
Сидоров С.С. |
2015-10-08 |
Итак, решение для MySQL полностью готово и корректно работает. Но прежде, чем перейти к решению для MS SQL Server проведём небольшое исследование, наглядно демонстрирующее ответ на вопрос о том, «отменяется ли действие BEFORE-триггера в случае ошибки в процессе выполнения операции».
Исследование 4.4.1.EXP.A. Будут ли аннулированы изменения данных, вызванные работой BEFORE-триггера, если операция, активировавшая этот триггер, не сможет завершиться успешно?
Изменим вид iNSERT-триггера с AFTER на BEFORE:
Исследование 4.1.1.a |
типа |
1 DROP TRIGGER 'last_visit_on_subscriptions_ins';
2
3 DELIMITER $$
4
5CREATE TRIGGER 'last visit on subscriptions ins'
6BEFORE INSERT
7ON 'subscriptions'
8FOR EACH ROW
9BEGIN
10IF (SELECT IFNULL('s last visit', '1970-01-01')
11 FROM 'subscribers'
12WHERE 's id' = NEW.'sb subscriber') < NEW.'sb start'
13THEN
14UPDATE 'subscribers'
15 |
SET |
's last visit' = NEW.'sb start' |
16WHERE 's id' = NEW.'sb subscriber';
17END IF;
18END;
19$$
20
21 DELIMITER ;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 292/545
Пример 31: обновление кэширующих таблиц и полей
Выполним последовательно добавление двух выдач книг с разными датами, но одинаковыми значениями первичных ключей:
MySQL | |
Исследование 4.1.1.a (вставка данных) |
| |
|
1 |
INSERT |
INTO 'subscriptions' |
|
2 |
VALUES |
500, |
|
3 |
|
2 , |
|
4 |
|
1 . |
|
5 |
|
'2020-01-12', |
|
6 |
|
'2020-02-12', |
|
7 |
|
'N'); |
|
8 |
|
|
|
9 |
INSERT |
INTO 'subscriptions' |
|
10 |
VALUES |
500, |
|
11 |
|
2 , |
|
12 |
|
1 . |
|
13 |
|
'2021-01-12', |
|
14 |
|
'2021-02-12', |
|
15 |
|
'N'); |
|
|
|
|
|
Проверим данные в таблице subscribers:
s_id |
s_name |
s_last_visit |
1 |
Иванов И.И. |
2015-10-07 |
2 |
Петров П.П. |
2020-01-12 |
3 |
Сидоров С.С. |
2014-08-03 |
4 |
Сидоров С.С. |
2015-10-08 |
Итак, изменения аннулируются, если операция не может быть завершена успешно. В противном случае значением даты последнего визита Петрова П.П.
было бы 2021-01-12.
Переходим к решению поставленной задачи для MS SQL Server. Модифицируем таблицу subscribers и проинициализируем добавленное поле данными.
MS SQL І |
Решение 4.1.1.a (модификация таблицы и инициализация данных) |
| |
1-- Модификация таблицы:
2ALTER TABLE [subscribers]
3ADD [s_last_visit] DATE NULL DEFAULT NULL;
5-- Инициализация данных:
6 |
UPDATE |
[subscribers] |
7 |
SET |
[s_last_visit] = [last_visit] |
8 |
FROM |
[subscribers] |
9 |
LEFT JOIN (SELECT [sb_subscriber], MAX [sb |
|
|
10 |
|
start]) AS |
[last visit] |
11 |
FROM |
[subscriptions] |
|
12 |
GROUP |
BY [sb subscriber]) AS [prepared_data] |
|
13 |
ON [s id] = [sb subscriber]; |
|
|
MS SQL Server поддерживает очень удобный синтаксис создания триггера сразу на нескольких операциях, потому (в отличие от решения для MySQL) мы используем одинаковый код тела триггера для всех трёх случаев (INSERT, UPDATE,
DELETE):
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 293/545
Пример 31: обновление кэширующих таблиц и полей
MS SQL |
|
|
Решение 4.1.1.a (создание триггеров) |
| |
|
|
1 |
CREAT TRIGGER [last visit on subscriptions |
insupd del] |
||||
2 |
ON [subscriptions] |
|
|
|
||
3 |
AFTER INSERT, UPDATE, DELETE |
|
|
|||
4 |
AS |
|
|
|
|
|
5 |
UPDATE |
[subscribers] |
|
|
|
|
6 |
SET |
[s_last_visit] = [last_visit] |
|
|
||
7 |
FROM |
[subscribers] |
|
|
|
|
8 |
|
LEFT JOIN (SELECT [sb_subscriber], |
|
|
||
9 |
|
|
|
MAX [sb_start]) AS |
|
|
10 |
|
|
FROM |
[subscriptions] |
|
[last_visit] |
11 |
|
|
GROUP |
BY |
|
|
12 |
|
|
|
[sb_subscriber]) |
|
AS [prepared_data] |
|
|
|
|
|
|
|
В корректности работы полученного решения вы можете убедиться, выполнив следующие запросы (они выполнены пошагово с пояснениями и показом результатов в решении для MySQL):
MS SQL I Решение 4.1.1.a (запросы для проверки работоспособности) |
1 SET IDENTITY_INSERT [subscriptions] ON;
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 |
|
'2019-01-12', |
|
16 |
|
'2019-02-12', |
|
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 |
|
'2020-01-12', |
|
38 |
|
'2020-02-12', |
|
39 |
|
'N'); |
|
40 |
|
|
|
41-- Изменение значения даты ранее откорректированной
42-- выдачи книги (которую переписали с Петрова П.П.
43-- на Иванова И.И.):
44UPDATE [subscriptions]
45 |
SET |
[sb start] = '2018-01-12' |
46 |
WHERE |
[sb_id] = 200; |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 294/545