Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 33: контроль операций модификации данных
СУБД запретит эту операцию, вернув следующее сообщение об ошибке:
Error Code: 1001. Date 2020-01-12 for subscription 501 activation is in the future.
Попытаемся добавить выдачу книги с датой возврата в прошлом:
MySQL і |
Решение 4.2.1.a (проверка работоспособности) |
|
1 |
INSERT INTO 'subscriptions' (502 |
|
2 |
VALUES |
1, 1, '2000-01-12', '2000-02-12', 'N' ) |
3
4
5
6
7
СУБД запретит эту операцию, вернув следующее сообщение об ошибке:
Error Code: 1002. Date 2000-02-12 for subscription 502 deactivation is in the past.
Попытаемся добавить выдачу книги, не нарушая ни одного из условий данной задачи:
MySQL і Решение 4.2.1.a (проверка работоспособности)
1 |
INSERT INTO 'subscriptions' |
|
2 |
VALUES |
(503 |
3 |
|
1, |
4 |
|
1, |
5 |
|
'2000-01-12', |
6 |
|
'2020-02-12', |
7 |
|
'N' ) |
|
|
|
СУБД позволит выполнить вставку.
Попытаемся обновить добавленную выдачу книги так, чтобы дата её активации оказалась в будущем:
MySQL і |
Решение 4.2.1.a (проверка работоспособности) |
|
1 |
UPDATE |
'subscriptions' |
2 |
SET |
'sb start' = '2020-01-01' |
3 |
WHERE 'sb id' = 503 |
|
СУБД запретит эту операцию, вернув следующее сообщение об ошибке:
Error Code: 1001. Date 2020-01-01 for subscription 503 activation is in the future.
Попытаемся обновить добавленную выдачу книги так, чтобы дата её активации оказалась позже даты возврата:
MySQL і Решение 4.2.1.a (проверка работоспособности)
1 UPDATE 'subscriptions'
2 SET 'sb_start' = '2010-01-01',
3'sb finish' = '2005-01-01'
4WHERE 'sb id' = 503
СУБД запретит эту операцию, вернув следующее сообщение об ошибке:
Error Code: 1003. Date 2005-01-01 for subscription 503 deactivation is less than the date for its activation (2010-01-01).
Попытаемся обновить добавленную выдачу книги так, чтобы дата её возврата была в прошлом (для операции обновления такое разрешено):
MySQL |
Решение 4.2.1 .а (проверка |
|
1 |
UPDATEработоспособности) : |
|
2 |
SET |
'subscriptions' |
3'sb_start' = '2005-01-01',
4WHERE 'sb_finish' = '2006-01-01'
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 335/545
Пример 33: контроль операций модификации данных
СУБД позволит выполнить обновление.
Попытаемся обновить добавленную выдачу книги, не нарушая ни одного из условий задачи:
MySQL |
Решение 4.2.1 .а (проверка работоспособности) |
|
1 |
UPDATE 'subscriptions' |
|
2 |
SET |
'sb_start' = '2005-01-01', |
3'sb finish' = '2010-01-01'
4WHERE 'sb id' = 503
СУБД позволит выполнить обновление.
Итак, решение данной задачи для MySQL готово и проверено. Переходим к решению для MS SQL Server.
В MS SQL Server нам доступны только триггеры уровня выражения, потому их внутренняя логика будет иной, нежели в MySQL. Также несколько иначе будут выглядеть возвращаемые триггерами сообщения об ошибках, т.к. в них нужно будет отразить информацию обо всех выдачах книг: в одном варианте реализации — только о «плохих», во втором — и о «плохих», и о «хороших».
Первый вариант реализации триггера полностью блокирует операцию, если хотя бы одна из записей нарушает условия задачи. Информация о таких записях аккумулируется в строковой переменной @bad_records (пояснение о логике ра-
боты функции STUFF см. в решении{72} задачи 2.2.2.а{71}).
Далее проверяется длина полученной строки: если она не равна нулю, значит, «плохие» записи обнаружены, и триггер должен отправить клиенту сообщение об ошибке (строки 23, 48, 67) и отменить операцию («откатить транзакцию») (строки
24, 49, 68).
В строках 28-32 мы получаем информацию о количестве записей в псевдотаблицах inserted и deleted, чтобы затем в строках 42-43 определить, выполнялась ли операция вставки (её признак: нет записей в deleted, есть записи в inserted).
MS SQL |
Решение 4.2.1 .а (триггеры для таблицы subscriptions, первый вариант |
1-- Вариант с полной блокировкойрешенияоперации) .
2CREATE TRIGGER [subscriptions control]
3ON [subscriptions]
4AFTER INSERT, UPDATE
5AS
6-- Переменные для хранения списка "плохих записей" и сообщения об ошибке.
7DECLARE @bad records NVARCHAR(max);
8DECLARE @msg NVARCHAR(max);
9
10-- Блокировка выдач книг с датой выдачи в будущем.
11SELECT @bad records = STUFF((SELECT ', ' + CAST [sb id] AS NVARCHAR) +
12 |
|
' (' + CAST([sb start] AS NVARCHAR) + ')' |
13 |
FROM |
[inserted] |
14 |
WHERE [sb start] > CONVERT(date, GETDATE()) |
|
15 |
ORDER BY [sb id] |
|
16 |
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
|
17 |
1, 2 |
''); |
18IF LEN(@bad records) > 0
19BEGIN
20SET @msg =
21CONCAT('The following subscriptions'' activation dates are
22in the future: ', @bad records);
23 RAISERROR @msg 16 1);
24ROLLBACK TRANSACTION;
25RETURN
26END;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 336/545
Пример 33: контроль операций модификации данных
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 337/545
Пример 33: контроль операций модификации данных
MS SQL I |
Решение 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];
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);
48 RAISERROR @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 ; |
67 |
RAISERROR @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 Стр: 338/545
Пример 33: контроль операций модификации данных
Если бы перед нами остро стояла задача применить именно этот вариант решения и для операции обновления, мы могли бы изменить свойства внешних ключей, убрав там операцию каскадного обновления и реализовав её отдельным триггером.
Обратите внимание на то, как в данном варианте решения реализована последовательность действий: поскольку мы не отменяем операцию и не выходим из тела триггера (как это реализовано в строках 24-25, 49-50, 68-69 первого варианта решения), триггер доработает до конца своего тела, что вынуждает нас одновременно учитывать все три условия задачи при принятии решения о том, «хорошая» ли нам попалась запись или «плохая».
В первую очередь мы получаем три списка «плохих» записей — по каждому из условий задачи (строки 14-40). В INSTEAD OF триггере нам неизвестны значения автоинкрементируемого первичного ключа (IDENTITY-ПОЛЯ sb_id), потому мы со-
бираем только сами значения дат. Однако, для реальной вставки данных (строки 81105) эти значения нам понадобятся: в решении{246} задачи 3.2.1.a{245} подробно объяснена логика их получения и использования.
MS |
QL | |
решение 4.2.1 .a (триггер для таолицы subscriptions, второй вариант решения) |
І |
|
|||
1 |
-- Вариант с частичной блокировкой операции. |
|
|
2 |
CREATE TRIGGER [subscriptions_control] |
|
|
3 |
ON [subscriptions] INSTEAD OF INSERT AS |
|
|
4 |
-- Переменные для хранения сообщений и списков записей. |
|
|
5 |
DECLARE @bad_records_act_future NVARCHAR(max); |
|
|
6 |
DECLARE @bad_records_deact_past NVARCHAR(max); |
|
|
7 |
DECLARE @bad_records_act_greater_than_deact NVARCHAR(max); |
|
|
8 |
|
|
|
9 |
DECLARE @good_records NVARCHAR(max); |
|
|
10 |
DECLARE @msg NVARCHAR(max); |
|
|
11 |
|
|
|
12-- Блокировка выдач книг с датой выдачи в будущем.
13SELECT @bad_records_act_future =
14 |
STUFF((SELECT ', ' + CAST [sb_start] AS NVARCHAR) |
|||
15 |
FROM |
[inserted] |
||
16 |
WHERE [sb_start] > CONVERT(date, GETDATE()) |
|||
17 |
ORDER BY |
[sb_start] |
||
18 |
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
|||
19 |
1, 2 |
''); |
|
|
20 |
|
|
|
|
23 |
STUFF((SELECT ', ' + CAST [sb_finish] AS NVARCHAR) |
|||
|
|
|
||
24 |
FROM |
[inserted] |
||
|
|
|
||
25 |
WHERE |
[sb_finish] < CONVERT(date, GETDATE()) |
||
|
|
|
||
26 |
ORDER BY |
[sb_finish] |
||
|
|
|
||
27 |
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
|||
|
|
|
||
28 |
1, 2 |
''); |
|
|
|
|
|
||
29 |
-- Блокировка выдач книг с датой возврата меньшей, чем дата выдачи. |
|||
30 |
||||
SELECT @bad_records_act_greater_than_deact = |
||||
31 |
||||
STUFF((SELECT ', (act: ' + CAST([sb_start] AS NVARCHAR) + |
||||
32 |
||||
|
', deact: ' + CAST [sb_finish] AS NVARCHAR) + ')' |
|||
33 |
|
|||
FROM |
[inserted] |
|||
34 |
||||
WHERE |
[sb_finish] < [sb_start] |
|||
35 |
||||
ORDER BY |
[sb_start], [sb_finish] |
|||
36 |
||||
21 |
-- Блокировка выдач книг с датой возврата в прошлом. |
|||
22 |
SELECT @bad_records_deact_past = |
|||
|
|
|
||
37 |
|
FOR XML PATH(''), TYPE).value('.', |
||
|
|
'nvarchar(max)' |
||
38 |
|
|
||
|
) |
|
||
39 |
|
|
||
1, 2 |
''); |
|
||
40 |
|
|||
|
|
|
||
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 339/545