Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 33: контроль операций модификации данных
Решение 4.2.1.c{315}.
Поскольку по условию задачи запрещено только изменение значения поля sb_is_active с N на Y у уже существующих записей, нам понадобится только
UPDATE-триггер.
Задачи такого типа очень просто и удобно решаются с использованием триггеров уровня записи (поддерживаются MySQL и Oracle) — мы используем ключевые слова old и new для доступа к старому и новому значению поля sb_is_active.
В случае с триггерами уровня выражения (только такие триггеры есть в MS SQL Server) придётся использовать запрос не объединение из псевдотаблиц inserted и deleted, чтобы найти взаимное соответствие старого и нового значений поля sb_is_active для каждой записи. Также придётся запретить изменение значения первичного ключа (строки 8-14 кода триггера для MS SQL Server), чтобы иметь возможность гарантированно получать соответствие старого и нового значения контролируемого поля.
В остальном логика решения этой задачи тривиальна: если происходит попытка изменения данных запрещённым по условию задачи образом, мы выводим сообщение об ошибке и «откатываем транзакцию».
Традиционно начнём с кода для MySQL.
MySQL і Решение 4.2.1.С (триггер для таблицы subscriptions)
1 DELIMITER $$
2
3CREATE TRIGGER 'sbs cntrl is active'
4BEFORE UPDATE
5ON 'subscriptions'
6FOR EACH ROW
7BEGIN
8IF ((OLD.'sb is active' = 'N') AND (NEW.'sb is active' = 'Y'))
9THEN
10SET @msg = CONCAT('It is prohibited to activate previously
11 |
deactivated subscriptions (rule violated |
|
12 |
for subscription with id ', NEW.'sb id', |
').'); |
13SIGNAL SQLSTATE '45001' SET MESSAGE TEXT = @msg, MYSQL ERRNO = 1001;
14END IF;
15END;
16$$
17
18DELIMITER ;
Врешении для MS SQL Server можно было бы пойти по более оптимальному
сточки зрения производительности пути и сделать INSTEAD OF триггер, но в таком
случае код триггера стал бы сложнее.
Поскольку в решении{328} задачи 4.2.1.b{315} мы уже рассматривали такую ситуацию, здесь мы пожертвуем производительностью ради краткости и понятности кода самого триггера.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 355/545
Пример 33: контроль операций модификации данных
MS SQL I |
Решение 4.2.1.c (триггер для таблицы subscriptions) |
| |
1CREATE TRIGGER [sbs cntrl is active]
2ON [subscriptions]
3AFTER UPDATE
4AS
5DECLARE @bad records NVARCHAR(max);
6DECLARE @msg NVARCHAR(max);
7 |
|
8 |
IF (UPDATE([sb id])) |
9 |
BEGIN |
10 |
RAISERROR ('Please, do NOT update surrogate PK |
11 |
on table [subscriptions]!', 16. 1); |
12ROLLBACK TRANSACTION;
13RETURN;
14END;
15 |
|
|
16 |
SELECT @bad records = STUFF((SELECT ', ' + |
|
17 |
|
CASTl[inserted] [sb id] AS NVARCHAR |
18 |
FROM |
[deleted] |
19 |
|
JOIN [inserted] |
20 |
|
ON [deleted] [sb id] = |
21 |
|
[inserted] [sb id] |
22 |
WHERE |
[deleted] [sb is active] = 'N' |
23 |
|
AND [inserted] [sb is active] = 'Y' |
24 |
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
|
25 |
1, 2 ''); |
|
26 |
|
|
27 |
IF (LEN @bad records 1 > 0 |
|
28BEGIN
29SET @msg = CONCAT('It is prohibited to activate previously
30 |
deactivated subscriptions (rule violated for |
31 |
subscriptions with id ', @bad records, ').'); |
32 |
RAISERROR @msg 16 1); |
33 |
ROLLBACK TRANSACTION; |
34 |
RETURN; |
35 |
END; |
36 |
GO |
И, наконец, представим решение для Oracle. Оно отличается от решения для MySQL только синтаксически, т.к. сама логика этих двух решений полностью идентична.
Oracle |
Решение 4.2.1 .с (триггер для таблицы |
1CREATEsubscriptions)TRIGGER "sbs ctr is active"
2BEFORE UPDATE
3ON "subscriptions"
4FOR EACH ROW
5BEGIN
6IF (( old "sb is active" = 'N') AND ( new "sb is active" = 'Y'))
7THEN
8 |
RAISE APPLICATION ERROR(-20001 'It is prohibited to activate |
9 |
previously deactivated subscriptions |
10 |
(rule violated for subscription with |
11 |
id ' || :new "sb id" || ').'); |
12END IF;
13END;
На этом решение данной задачи завершено. Проверить его работоспособность вы можете сами, выполняя запросы на обновление данных в таблице subscriptions так, чтобы либо не нарушить, либо нарушить условие задачи.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 356/545
Пример 33: контроль операций модификации данных
Задание 4.2.1.TSK.A: создать триггер, не позволяющий добавить в базу &данных информацию о выдаче книги, если выполняется хотя бы одно из
условий:
•дата выдачи или возврата приходится на воскресенье;
•читатель брал за последние полгода более 100 книг;
•промежуток времени между датами выдачи и возврата менее трёх дней.
Задание 4.2.1.TSK.B: создать триггер, не позволяющий выдать книгу читателю, у которого на руках находится пять и более книг, при условии, что суммарное время, оставшееся до возврата всех выданных ему книг, составляет менее одного месяца.
Задание 4.2.1.TSK.C: переработать решение{335} задачи 4.2.1.c{315} для MS SQL Server, изменив AFTER-триггер на INSTEAD OF триггер.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 357/545
Пример 34: контроль формата и значений данных
4.2.2.Пример 34: контроль формата и значений данных
ОЗадача 4.2.2.a{338}: создать триггер, допускающий регистрацию в библиотеке только таких читателей, имя которых содержит хотя бы два слова и одну точку.
ОЗадача 4.2.2.b{342}: создать триггер, допускающий регистрацию в библиотеке только книг, изданных не более ста лет назад.
Ожидаемый результат 4.2.2.a:
При попытке внести в базу данных изменения, противоречащие условию задачи, операция (транзакция) должна быть отменена. Также должно быть выведено сообщение об ошибке, наглядно поясняющее суть проблемы, например: «Subscribers name should contain at least two words and one point, but the following name violates this rule: ИвановИИ».
Ожидаемый результат 4.2.2.b:
При попытке внести в базу данных изменения, противоречащие условию задачи, операция (транзакция) должна быть отменена. Также должно быть выведено сообщение об ошибке, наглядно поясняющее суть проблемы, например: «The following issuing year is more than 100 years in the past: 1812».
•4 Решение 4.2.2.a{338}:
В решении{328} задачи 4.2.1.b{315} мы уже рассматривали подробно преимущества BEFORE- и INSTEAD OF триггеров перед AFTER-триггерами в плане произво-
дительности, потому здесь не будем повторно приводить те же самые рассуждения, а сразу переходим к сути задачи.
Самое сложное здесь — посчитать слова. И сложность эта — не столько техническая, сколько «философская»: что считать словом? Договоримся, что словом мы будем считать непрерывную последовательность из букв и знаков - (минус) и ' (апостроф). Приняв это допущение, мы можем построить универсальное решение на основе регулярных выражений (для СУБД, которые их поддерживают).
Изучение сути регулярных выражений выходит за рамки этой книги, потому
— вот готовый универсальный вариант, который должен сработать в подавляющем большинстве СУБД и языков программирования (да, можно написать более оптимальный и элегантный вариант, но это повышает риск потери универсальности):
Л[а-иА-2а-яА-ЯёЁ\'-]+([Ла-иА-2а-яА-ЯёЁ\'-]+[а-иА-2а-яА-ЯёЁ\'.-]+){1,}$
Графическое представление этого регулярного выражения представлено на рисунке 4.a. Буквы «ё» добавлены в символьный класс как отдельный символ потому, что они не входят в диапазон букв русского алфавита.
Если по какой-то причине вы не хотите или не можете использовать регулярные выражения, есть второй способ убедиться, что в строке есть два слова (если допустить, что разделителем слов является пробел): нужно подсчитать количество пробелов в строке, у которой гарантированно удалены т.н. «концевые пробелы» в начале и конце. Если полученное число больше ноля, в строке точно есть как минимум два слова.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 358/545
Пример 34: контроль формата и значений данных
Рисунок 4.a — Графическое представление регулярного выражения14
Традиционно начинаем с MySQL. И сразу же сталкиваемся с проблемой: эта СУБД не поддерживает мультибайтовые строки при использовании регулярных выражений14 15, а потому нам придётся преобразовывать анализируемые значения к однобайтовой кодировке (например, CP1251). Для русского алфавита это безопасно, но для других языков может привести к искажениям данных и неверной работе механизма регулярных выражений.
MySQL I |
Решение 4.2.2.a (триггеры для таблицы subscribers) |
| |
1 DELIMITER $$
2
3CREATE TRIGGER 'sbsrs cntrl name ins'
4BEFORE INSERT
5ON 'subscribers'
6FOR EACH ROW
7BEGIN
8IF ((CAST(NEW.'s name' AS CHAR CHARACTER SET cp1251) REGEXP
9CAST('Л[a-zA-Za-яА-ЯёЁХ'-] + ([лa-zA-Zа-яА-ЯёЁ\'-]+[a-zA-Za-яА-
10ЯёЁ\'.-]+){1,}$' AS CHAR CHARACTER SET cp1251 ) = 0)
11OR (LOCATE('.', NEW.'s name') = 0
12THEN
13 |
SET @msg = CONCAT('Subscribers name |
should contain at |
|
14 |
least two words and onepoint, but |
the following |
|
15 |
name violates this rule ', NEW. 's name'); |
||
16 |
SIGNAL SQLSTATE '45001' SET MESSAGE |
TEXT = @msg MYSQL |
ERRNO = 1001 |
17END IF;
18END;
19$$
14https://regexper.com/#'“[a-zA-Z%D0%B0-%D1%8F%D0%90-%D0%AF%D1%91%D0%81\%27-]%2B%28['“a-zA-Z%D0%B0- %D1 %8F%D0%90-%D0%AF%D1 %91 %D0%81 \%27-]%2B[a-zA-Z%D0%B0-%D1 %8F%D0%90- %D0%AF%D1 %91 %D0%81\%27-]%2B%29{1 %2C}%24
15http://dev.mysql.Com/doc/refman/5.6/en/regexp.html#operator_regexp
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 359/545