Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 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 1 > 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 Стр: 340/545
Пример 33: контроль операций модификации данных
MS SQL |
Решение 4.2.1.a (триггер для таблицы subscriptions, второй вариант решения) (окончание) |
|
106 |
............. SET IDENTITY_INSERT |
.........................[Subscriptions] |
107 |
............. ........... |
OFF; |
108SET @msg =
109CONCAT('Subscriptions with the following activation/deactivation dates
110were inserted successfully: ', @good_records);
111PRINT @msg;
END; 112 GO
Код в строках 41 -65 проверяет, были ли обнаружены «плохие» записи и, если да, формирует сообщение об ошибке, учитывающее все три условия задачи. Поскольку после отправки сообщения об ошибке (строка 64) мы не откатываем транзакцию и не выходим из тела триггера, выполнение продолжается дальше, и мы получаем возможность произвести вставку в таблицу «хороших» записей.
В строках 67-76 формируется список таких «хороших» записей, данные в которых не нарушают ни одного из условий задачи. Если такие записи были обнаружены, в строках 78-111 мы выполняем их вставку в таблицу и выводим сообщение (просто сообщение, не сообщение об ошибке) со списком их дат. Легко догадаться, что эта операция вставки не приводит к повторной активации INSTEAD OF триггера (иначе мы получили бы бесконечную рекурсию).
Важно! Этот (второй) вариант решения показан в учебных целях для демонстрации возможностей триггеров MS SQL Server. В реальных приложениях такая «частичная» обработка данных (когда часть записей успешно вставляется в таблицу, а часть — нет) может привести к сложнообнаружимым дефектам и иным слабопредсказуемым последствиям.
Итак, оба варианты решений готовы, осталось проверить их работоспособность. Будем выполнять запросы и отслеживать полученные сообщения.
Выполним вставку данных с явно указанными значениями первичного ключа и частью записей, удовлетворяющей условиям задачи, а частью — не удовлетворяющей:
MS SQL I Решение 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 Стр: 341/545
Пример 33: контроль операций модификации данных
Полученные сообщения:
• В первом варианте решения — сообщение об ошибке:
The following subscriptions' activation dates are in the future: 500 (2020- 01-12), 600 (2021-01-12)
• Во втором варианте решения: о Сообщение об ошибке:
Some records were NOT inserted! The following activation dates are in
the future: 2020-01-12, |
2021-01-12 |
о Информационное сообщение:
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)
•Во втором варианте решения:
оСообщение об ошибке:
Some records were NOT inserted! The following deactivation dates are
in the past: 2001-02-12, |
2002-02-12 |
о Информационное сообщение:
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 Стр: 342/545
Пример 33: контроль операций модификации данных
Выполним вставку данных, удовлетворяющих всем условиям задачи:
MS SQL I Решение 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 |
4 , |
|
8 |
|
4 , |
|
9 |
|
'2001-01-12', |
|
10 |
|
'2021-02-12', |
|
11 |
|
'N' |
) |
|
|
|
|
Полученные сообщения:
•В первом варианте решения: никаких сообщений от триггера нет.
•Во втором варианте решения:
оСообщение об ошибке: отсутствует.
оИнформационное сообщение:
Subscriptions with the following activation/deactivation dates were inserted successfully: 2001-01-12/2021-02-12
Выполним обновление данных с нарушением одного из условия задачи:
MS SQL Решение 4.2.1 .а (проверка работоспособности)
1 |
UPDATE [subscriptions] |
|
2 |
SET |
[sb_finish] = '2005-01-01' |
3 |
WHERE |
[sb start] > '2011-01-01' |
Триггер во втором варианте решения не реагирует на операцию обновления, а от триггера в первом варианте решения поступит следующее сообщение об ошибке:
The following subscriptions' deactivation dates are less than activation dates: 2
(act: 2011-01-12, deact: 2005-01-01), |
3 (act: 2012-05-17, deact: 2005-01-01), |
|
42 (act: 2012-06-11, deact: 2005-01-01), |
57 (act: 2012-06-11, deact: 2005-0101), |
|
|
61 |
(act: 2014-08-03, deact: 2005-01-01), |
|
62 |
(act: 2014-08-03, deact: 200501-01), |
|
86 |
(act: 2014-08-03, deact: 2005-01-01), |
|
91 |
(act: 2015-10-07, deact: |
2005-01-01), 95 (act: 2015-10-07, deact: |
2005-01-01), 99 (act: 2015-10-08, deact: |
|
2005-01-01), 100 (act: 2011-01-12, deact: 2005-01-01) |
||
Выполним обновление данных с соблюдением всех условий задачи:
MS SQL і Решение 4.2.1 .а (проверка работоспособности)
1 |
UPDATE |
[subscriptions] |
2 |
SET |
[sb_finish] = '2002-01-01' |
3 |
WHERE |
[sb start] = '2001-01-12'; |
Триггер во втором варианте решения не реагирует на операцию обновления, а от триггера в первом варианте решения не поступит никаких сообщений.
Итак, решение данной задачи для MS SQL Server получено и проверено. Переходим к решению для Oracle.
Поскольку Oracle не поддерживает псевдотаблицы deleted и inserted, мы реализуем ту же логику, что и в решении для MySQL, используя триггеры уровня записи.
Таким образом, отличие в решении для Oracle от решения для MySQL будет только в способе отмена операции (с одновременным выводом сообщения об ошибке): в Oracle для таких задач удобно использовать функцию RAISE_APPLICA-
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 343/545
Пример 33: контроль операций модификации данных
TION_ERROR.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 344/545