Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 31: обновление кэширующих таблиц и полей
MySQL Решение 4.1.1.a (проверка работоспособности)
|
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 |
|
Решение 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 |
|
Решение 4.1.1.a (проверка работоспособности) |
||||
|
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 |
|
||
Изменим значение даты ранее откорректированной выдачи книги (которую мы переписали с Петрова П.П. на Иванова И.И.):
|
MySQL |
|
Решение 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 Стр: 275/545
Пример 31: обновление кэширующих таблиц и полей
Удалим эту выдачу:
MySQL Решение 4.1.1.a (проверка работоспособности)
1DELETE FROM `subscriptions`
2WHERE `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 |
Удалим единственную выдачу книги Петрову П.П.:
MySQL Решение 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:
MySQL |
Исследование 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 Стр: 276/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;
4
5-- Инициализация данных:
6UPDATE [subscribers]
7 |
|
SET |
[s_last_visit] = |
[last_visit] |
8 |
|
FROM |
[subscribers] |
|
9 |
|
|
LEFT JOIN (SELECT |
[sb_subscriber], |
10 |
|
|
|
MAX([sb_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 Стр: 277/545
Пример 31: обновление кэширующих таблиц и полей
MS SQL |
Решение 4.1.1.a (создание триггеров) |
1CREATE TRIGGER [last_visit_on_subscriptions_ins_upd_del]
2ON [subscriptions]
3AFTER INSERT, UPDATE, DELETE
4AS
5UPDATE [subscribers]
6 |
|
SET |
[s_last_visit] = |
[last_visit] |
7 |
|
FROM |
[subscribers] |
|
8 |
|
|
LEFT JOIN (SELECT |
[sb_subscriber], |
9 |
|
|
|
MAX([sb_start]) AS [last_visit] |
10 |
|
|
FROM |
[subscriptions] |
11 |
|
|
GROUP |
BY [sb_subscriber]) AS [prepared_data] |
12 |
|
|
ON [s_id] = [sb_subscriber]; |
|
В корректности работы полученного решения вы можете убедиться, выполнив следующие запросы (они выполнены пошагово с пояснениями и показом результатов в решении для MySQL):
MS SQL 
Решение 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 Стр: 278/545
Пример 31: обновление кэширующих таблиц и полей
MS SQL Решение 4.1.1.a (запросы для проверки работоспособности) (продолжение)
47-- Удаление этой откорректированной выдачи:
48DELETE FROM [subscriptions]
49WHERE [sb_id] = 200;
50
51-- Удаление единственной выдачи Петрову П.П.:
52DELETE FROM [subscriptions]
53WHERE [sb_id] = 201;
54
55 SET IDENTITY_INSERT [subscriptions] OFF;
Переходим к решению для Oracle, которое отличается от решения для MS SQL Server только синтаксическими особенностями реализации той же логики:
Oracle Решение 4.1.1.a (модификация таблицы и инициализация данных)
1-- Модификация таблицы:
2ALTER TABLE "subscribers"
3ADD ("s_last_visit" DATE DEFAULT NULL NULL);
4
5-- Инициализация данных:
6UPDATE "subscribers" "outer"
7 |
|
SET |
"s_last_visit" = |
|
|
8 |
|
|
( |
|
|
9 |
|
|
SELECT |
"last_visit" |
|
10 |
|
|
FROM |
"subscribers" |
|
11 |
|
|
LEFT JOIN (SELECT |
"sb_subscriber", |
|
12 |
|
|
|
|
MAX("sb_start") AS "last_visit" |
13 |
|
|
|
FROM |
"subscriptions" |
14 |
|
|
|
GROUP BY |
"sb_subscriber") "prepared_data" |
15 |
|
|
ON |
"s_id" = |
"sb_subscriber" |
16 |
|
|
WHERE "outer"."s_id" = "sb_subscriber"); |
||
Oracle Решение 4.1.1.a (создание триггеров)
1CREATE TRIGGER "last_visit_on_scs_ins_upd_del"
2AFTER INSERT OR UPDATE OR DELETE
3ON "subscriptions"
4BEGIN
5UPDATE "subscribers" "outer"
6 |
|
SET |
"s_last_visit" = |
|
7 |
|
|
( |
|
8 |
|
|
SELECT |
"last_visit" |
9 |
|
|
FROM |
"subscribers" |
10 |
|
LEFT JOIN (SELECT |
"sb_subscriber", |
|
11 |
|
|
|
MAX("sb_start") AS "last_visit" |
12 |
|
|
FROM |
"subscriptions" |
13 |
|
|
GROUP BY |
"sb_subscriber") "prepared_data" |
14 |
|
ON |
"s_id" = |
"sb_subscriber" |
15WHERE "outer"."s_id" = "sb_subscriber");
16END;
Вданном решении мы использовали возможность Oracle создавать т.н. «триггеры уровня выражения» (statement level triggers), работающие аналогично триггерам MS SQL Server: такой триггер активируется после выполнения всей операции один раз, а не для каждого модифицируемого ряда отдельно, как это происходит, например, в MySQL, где поддерживаются только «триггеры уровня записи» (row level triggers), активирующиеся отдельно для каждого модифицируемого ряда.
Вкорректности работы полученного решения вы можете убедиться, выполнив следующие запросы (они выполнены пошагово с пояснениями и показом результатов в решении для MySQL):
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 279/545