Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 33: контроль операций модификации данных
MS SQL Решение 4.2.1.a (триггеры для таблицы subscriptions, первый вариант решения) (продолжение)
27-- Блокировка выдач книг с датой возврата в прошлом.
28DECLARE @deleted_records INT;
29DECLARE @inserted_records INT;
30
31SELECT @deleted_records = COUNT(*) FROM [deleted];
32SELECT @inserted_records = COUNT(*) FROM [inserted];
33
34SELECT @bad_records = STUFF((SELECT ', ' + CAST([sb_id] AS NVARCHAR) +
35' (' + CAST([sb_start] AS NVARCHAR) + ')'
36 |
|
FROM |
[inserted] |
|
37 |
|
WHERE |
[sb_finish] |
< CONVERT(date, GETDATE()) |
38 |
|
ORDER |
BY [sb_id] |
|
39 |
|
FOR XML PATH(''), |
TYPE).value('.', 'nvarchar(max)'), |
|
40 |
|
1, 2, |
''); |
|
|
|
|
|
|
41IF ((LEN(@bad_records) > 0) AND
42(@deleted_records = 0) AND
43(@inserted_records > 0))
44BEGIN
45SET @msg =
46CONCAT('The following subscriptions'' deactivation dates are
47in the past: ', @bad_records);
48RAISERROR (@msg, 16, 1);
49ROLLBACK TRANSACTION;
50RETURN
51END;
52
53-- Блокировка выдач книг с датой возврата меньшей, чем дата выдачи.
54SELECT @bad_records = STUFF((SELECT ', ' + CAST([sb_id] AS NVARCHAR) +
55' (act: ' + CAST([sb_start] AS NVARCHAR) + ', deact: ' +
56CAST([sb_finish] AS NVARCHAR) + ')'
57 |
|
FROM |
[inserted] |
|
58 |
|
WHERE |
[sb_finish] |
< [sb_start] |
59 |
|
ORDER |
BY [sb_id] |
|
60 |
|
FOR XML PATH(''), |
TYPE).value('.', 'nvarchar(max)'), |
|
61 |
|
1, 2, |
''); |
|
62IF LEN(@bad_records) > 0
63BEGIN
64SET @msg =
65CONCAT('The following subscriptions'' deactivation dates are less
66 |
than activation dates: ', @bad_records); |
67RAISERROR (@msg, 16, 1);
68ROLLBACK TRANSACTION;
69RETURN
70END;
71GO
Второй вариант реализации триггера блокирует только операции с «плохими» записями, а операции с «хорошими» записями выполняет. Также он выводит сообщение с информацией о «хороших» записях и сообщение об ошибке с информацией о «плохих».
Чтобы добиться такого эффекта мы будем использовать INSTEAD OF триггер, который активируется вместо соответствующей операции с данными. Если в теле такого триггера не выполнять никаких действий, то исходная операция с данными не выполнится по определению, и даже нет надобности «откатывать транзакцию». Чтобы операция выполнилась, в теле триггера нужно будет выполнить соответствующий запрос на модификацию данных в таблице.
К сожалению, в таблице subscriptions есть внешние кличи с каскадным обновлением, потому MS SQL Server не позволит создать INSTEAD OF UPDATE триггер, но продемонстрировать общую логику решения можно и на одной лишь операции вставки.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 320/545
Пример 33: контроль операций модификации данных
Если бы перед нами остро стояла задача применить именно этот вариант решения и для операции обновления, мы могли бы изменить свойства внешних ключей, убрав там операцию каскадного обновления и реализовав её отдельным триггером.
Обратите внимание на то, как в данном варианте решения реализована последовательность действий: поскольку мы не отменяем операцию и не выходим из тела триггера (как это реализовано в строках 24-25, 49-50, 68-69 первого варианта решения), триггер доработает до конца своего тела, что вынуждает нас одновременно учитывать все три условия задачи при принятии решения о том, «хорошая» ли нам попалась запись или «плохая».
В первую очередь мы получаем три списка «плохих» записей — по каждому из условий задачи (строки 14-40). В INSTEAD OF триггере нам неизвестны значения автоинкрементируемого первичного ключа (IDENTITY-поля sb_id), потому мы собираем только сами значения дат. Однако, для реальной вставки данных (строки 81-105) эти значения нам понадобятся: в решении{246} задачи 3.2.1.a{245} подробно объяснена логика их получения и использования.
MS SQL Решение 4.2.1.a (триггер для таблицы subscriptions, второй вариант решения)
1-- Вариант с частичной блокировкой операции.
2CREATE TRIGGER [subscriptions_control]
3ON [subscriptions]
4INSTEAD OF INSERT
5AS
6-- Переменные для хранения сообщений и списков записей.
7DECLARE @bad_records_act_future NVARCHAR(max);
8DECLARE @bad_records_deact_past NVARCHAR(max);
9DECLARE @bad_records_act_greater_than_deact NVARCHAR(max);
10
11DECLARE @good_records NVARCHAR(max);
12DECLARE @msg NVARCHAR(max);
13
14-- Блокировка выдач книг с датой выдачи в будущем.
15SELECT @bad_records_act_future =
16 |
|
STUFF((SELECT ', ' + CAST([sb_start] AS NVARCHAR) |
|
17 |
|
FROM |
[inserted] |
18 |
|
WHERE |
[sb_start] > CONVERT(date, GETDATE()) |
19 |
|
ORDER |
BY [sb_start] |
20 |
|
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
|
21 |
|
1, 2, |
''); |
22 |
|
|
|
23-- Блокировка выдач книг с датой возврата в прошлом.
24SELECT @bad_records_deact_past =
25 |
|
STUFF((SELECT ', ' + CAST([sb_finish] AS NVARCHAR) |
|
26 |
|
FROM |
[inserted] |
27 |
|
WHERE |
[sb_finish] < CONVERT(date, GETDATE()) |
28 |
|
ORDER |
BY [sb_finish] |
29 |
|
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
|
30 |
|
1, 2, |
''); |
31 |
|
|
|
32-- Блокировка выдач книг с датой возврата меньшей, чем дата выдачи.
33SELECT @bad_records_act_greater_than_deact =
34 |
|
STUFF((SELECT ', (act: ' + CAST([sb_start] AS NVARCHAR) + |
|
35 |
|
|
', deact: ' + CAST([sb_finish] AS NVARCHAR) + ')' |
36 |
|
FROM |
[inserted] |
37 |
|
WHERE |
[sb_finish] < [sb_start] |
38 |
|
ORDER |
BY [sb_start], [sb_finish] |
39 |
|
|
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
40 |
|
1, 2, |
''); |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 321/545
Пример 33: контроль операций модификации данных
MS SQL Решение 4.2.1.a (триггер для таблицы subscriptions, второй вариант решения) (продолжение)
41IF ((LEN(@bad_records_act_future) > 0) OR
42(LEN(@bad_records_deact_past) > 0) OR
43(LEN(@bad_records_act_greater_than_deact) > 0))
44BEGIN
45SET @msg = 'Some records were NOT inserted!';
46IF (LEN(@bad_records_act_future) > 0)
47BEGIN
48SET @msg = CONCAT(@msg, CHAR(13), CHAR(10),
49'The following activation dates are in the future: ',
50@bad_records_act_future);
51END;
52IF (LEN(@bad_records_deact_past) > 0)
53BEGIN
54SET @msg = CONCAT(@msg, CHAR(13), CHAR(10),
55'The following deactivation dates are in the past: ',
56@bad_records_deact_past);
57END;
58IF (LEN(@bad_records_act_greater_than_deact) > 0)
59BEGIN
60SET @msg = CONCAT(@msg, CHAR(13), CHAR(10),
61'The following deactivation dates are less than activation dates: ',
62@bad_records_act_greater_than_deact);
63END;
64RAISERROR (@msg, 16, 1);
65END;
66 |
|
|
|
67 |
|
SELECT @good_records = STUFF((SELECT ', ' + |
|
68 |
|
CAST([sb_start] AS NVARCHAR) + '/' + |
|
69 |
|
CAST([sb_finish] AS NVARCHAR) |
|
70 |
|
FROM |
[inserted] |
71 |
|
WHERE (([sb_start] <= CONVERT(date, GETDATE())) AND |
|
72 |
|
|
([sb_finish] >= CONVERT(date, GETDATE())) AND |
73 |
|
|
([sb_finish] >= [sb_start])) |
74 |
|
ORDER |
BY [sb_start], [sb_finish] |
75 |
|
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
|
76 |
|
1, 2, ''); |
|
77 |
|
|
|
78IF LEN(@good_records) > 0
79BEGIN
80SET IDENTITY_INSERT [subscriptions] ON;
81INSERT INTO [subscriptions]
82 |
|
([sb_id], |
83 |
|
[sb_subscriber], |
84 |
|
[sb_book], |
85 |
|
[sb_start], |
86 |
|
[sb_finish], |
87 |
|
[sb_is_active]) |
88 |
|
SELECT ( CASE |
89 |
|
WHEN [sb_id] IS NULL |
90 |
|
OR [sb_id] = 0 THEN IDENT_CURRENT('subscriptions') |
91 |
|
+ IDENT_INCR('subscriptions') |
92 |
|
+ ROW_NUMBER() OVER (ORDER BY |
93 |
|
(SELECT 1)) |
94 |
|
- 1 |
95 |
|
ELSE [sb_id] |
96 |
|
END ) AS [sb_id], |
97[sb_subscriber],
98[sb_book],
99[sb_start],
100[sb_finish],
101[sb_is_active]
102FROM [inserted]
103WHERE (([sb_start] <= CONVERT(date, GETDATE())) AND
104 |
|
([sb_finish] |
>= |
CONVERT(date, |
GETDATE())) AND |
105 |
|
([sb_finish] |
>= |
[sb_start])); |
|
|
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 322/545
Пример 33: контроль операций модификации данных
MS SQL |
Решение 4.2.1.a (триггер для таблицы subscriptions, второй вариант решения) (окончание) |
106SET IDENTITY_INSERT [subscriptions] OFF;
107SET @msg =
108CONCAT('Subscriptions with the following activation/deactivation
109dates were inserted successfully: ', @good_records);
110PRINT @msg;
111END;
112GO
Код в строках 41-65 проверяет, были ли обнаружены «плохие» записи и, если да, формирует сообщение об ошибке, учитывающее все три условия задачи. Поскольку после отправки сообщения об ошибке (строка 64) мы не откатываем транзакцию и не выходим из тела триггера, выполнение продолжается дальше, и мы получаем возможность произвести вставку в таблицу «хороших» записей.
В строках 67-76 формируется список таких «хороших» записей, данные в которых не нарушают ни одного из условий задачи. Если такие записи были обнаружены, в строках 78-111 мы выполняем их вставку в таблицу и выводим сообщение (просто сообщение, не сообщение об ошибке) со списком их дат. Легко догадаться, что эта операция вставки не приводит к повторной активации INSTEAD OF триггера (иначе мы получили бы бесконечную рекурсию).
Важно! Этот (второй) вариант решения показан в учебных целях для демонстрации возможностей триггеров MS SQL Server. В реальных приложениях такая «частичная» обработка данных (когда часть записей успешно вставляется в таблицу, а часть — нет) может привести к сложнообнаружимым дефектам и иным слабопредсказуемым последствиям.
Итак, оба варианты решений готовы, осталось проверить их работоспособность. Будем выполнять запросы и отслеживать полученные сообщения.
Выполним вставку данных с явно указанными значениями первичного ключа и частью записей, удовлетворяющей условиям задачи, а частью — не удовлетворяющей:
MS SQL Решение 4.2.1.a (проверка работоспособности)
1SET IDENTITY_INSERT [subscriptions] ON;
2INSERT INTO [subscriptions]
3 |
|
|
([sb_id], |
4 |
|
|
[sb_subscriber], |
5 |
|
|
[sb_book], |
6 |
|
|
[sb_start], |
7 |
|
|
[sb_finish], |
8 |
|
|
[sb_is_active]) |
9 |
|
VALUES |
(500, |
10 |
|
|
3, |
11 |
|
|
3, |
12 |
|
|
'2020-01-12', |
13 |
|
|
'2020-02-12', |
14 |
|
|
'N'), |
15 |
|
|
(600, |
16 |
|
|
3, |
17 |
|
|
4, |
18 |
|
|
'2021-01-12', |
19 |
|
|
'2021-02-12', |
20 |
|
|
'N'), |
21 |
|
|
(700, |
22 |
|
|
4, |
23 |
|
|
4, |
24 |
|
|
'2001-01-12', |
25 |
|
|
'2021-02-12', |
26 |
|
|
'N'); |
27 |
|
SET IDENTITY_INSERT [subscriptions] OFF; |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 323/545
Пример 33: контроль операций модификации данных
Полученные сообщения:
•В первом варианте решения — сообщение об ошибке:
The following subscriptions' activation dates are in the future: 500
(2020-01-12), 600 (2021-01-12)
• Во втором варианте решения: o Сообщение об ошибке:
Some records were NOT inserted! The following activation dates are in the future: 2020-01-12, 2021-01-12
o Информационное сообщение:
Subscriptions with the following activation/deactivation dates were
inserted successfully: 2001-01-12/2021-02-12
Выполним вставку данных с без указания значений первичного ключа и с частью записей, удовлетворяющей условиям задачи, а частью — не удовлетворяющей:
MS SQL Решение 4.2.1.a (проверка работоспособности)
1 |
INSERT INTO [subscriptions] |
|
2 |
|
([sb_subscriber], |
3 |
|
[sb_book], |
4 |
|
[sb_start], |
5 |
|
[sb_finish], |
6 |
|
[sb_is_active]) |
7 |
VALUES |
(3, |
8 |
|
3, |
9 |
|
'2020-01-12', |
10 |
|
'2020-02-12', |
11 |
|
'N'), |
12 |
|
(3, |
13 |
|
4, |
14 |
|
'2021-01-12', |
15 |
|
'2021-02-12', |
16 |
|
'N'), |
17 |
|
(4, |
18 |
|
4, |
19 |
|
'2001-01-12', |
20 |
|
'2021-02-12', |
21 |
|
'N') |
Полученные сообщения:
•В первом варианте решения — сообщение об ошибке:
The following subscriptions' deactivation dates are in the past: 704 (2001-01-12), 705 (2002-01-12)
• Во втором варианте решения: o Сообщение об ошибке:
Some records were NOT inserted! The following deactivation dates are in the past: 2001-02-12, 2002-02-12
o Информационное сообщение:
Subscriptions with the following activation/deactivation dates were
inserted successfully: 2001-01-12/2021-02-12
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 324/545