Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 33: контроль операций модификации данных
Решение 4.2.1.c{315}.
Поскольку по условию задачи запрещено только изменение значения поля sb_is_active с N на Y у уже существующих записей, нам понадобится только UP- DATE-триггер.
Задачи такого типа очень просто и удобно решаются с использованием триггеров уровня записи (поддерживаются 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.c (триггер для таблицы 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 Стр: 335/545
Пример 33: контроль операций модификации данных
MS SQL Решение 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
8IF (UPDATE([sb_id]))
9BEGIN
10RAISERROR ('Please, do NOT update surrogate PK
11 |
|
on table [subscriptions]!', 16, 1); |
12ROLLBACK TRANSACTION;
13RETURN;
14END;
15 |
|
|
|
16 |
|
SELECT @bad_records = STUFF((SELECT |
', ' + |
17 |
|
|
CAST([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 |
|
|
|
27IF (LEN(@bad_records) > 0)
28BEGIN
29SET @msg = CONCAT('It is prohibited to activate previously
30 |
|
deactivated subscriptions (rule violated for |
31 |
|
subscriptions with id ', @bad_records, ').'); |
32RAISERROR (@msg, 16, 1);
33ROLLBACK TRANSACTION;
34RETURN;
35END;
36GO
И, наконец, представим решение для Oracle. Оно отличается от решения для MySQL только синтаксически, т.к. сама логика этих двух решений полностью идентична.
Oracle Решение 4.2.1.c (триггер для таблицы subscriptions)
1CREATE 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
8RAISE_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;
На этом решение данной задачи завершено. Проверить его работоспособность вы можете сами, выполняя запросы на обновление данных в таблице sub-
scriptions так, чтобы либо не нарушить, либо нарушить условие задачи.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 336/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 Стр: 337/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.2.2.a{338}:
В решении{328} задачи 4.2.1.b{315} мы уже рассматривали подробно преимущества BEFORE- и INSTEAD OF триггеров перед AFTER-триггерами в плане производительности, потому здесь не будем повторно приводить те же самые рассуждения, а сразу переходим к сути задачи.
Самое сложное здесь — посчитать слова. И сложность эта — не столько техническая, сколько «философская»: что считать словом? Договоримся, что словом мы будем считать непрерывную последовательность из букв и знаков - (минус) и ' (апостроф). Приняв это допущение, мы можем построить универсальное решение на основе регулярных выражений (для СУБД, которые их поддерживают).
Изучение сути регулярных выражений выходит за рамки этой книги, потому
— вот готовый универсальный вариант, который должен сработать в подавляющем большинстве СУБД и языков программирования (да, можно написать более оптимальный и элегантный вариант, но это повышает риск потери универсальности):
^[a-zA-Zа-яА-ЯёЁ\'-]+([^a-zA-Zа-яА-ЯёЁ\'-]+[a-zA-Zа-яА-ЯёЁ\'.-]+){1,}$
Графическое представление этого регулярного выражения представлено на рисунке 4.a. Буквы «ё» добавлены в символьный класс как отдельный символ потому, что они не входят в диапазон букв русского алфавита.
Если по какой-то причине вы не хотите или не можете использовать регулярные выражения, есть второй способ убедиться, что в строке есть два слова (если допустить, что разделителем слов является пробел): нужно подсчитать количество пробелов в строке, у которой гарантированно удалены т.н. «концевые пробелы» в начале и конце. Если полученное число больше ноля, в строке точно есть как минимум два слова.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 338/545
Пример 34: контроль формата и значений данных
Рисунок 4.a — Графическое представление регулярного выражения14
Традиционно начинаем с MySQL. И сразу же сталкиваемся с проблемой: эта СУБД не поддерживает мультибайтовые строки при использовании регулярных выражений15, а потому нам придётся преобразовывать анализируемые значения к однобайтовой кодировке (например, CP1251). Для русского алфавита это безопасно, но для других языков может привести к искажениям данных и неверной работе механизма регулярных выражений.
MySQL Решение 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-Zа-яА-ЯёЁ\'-]+([^a-zA-Zа-яА-ЯёЁ\'-]+[a-zA-Zа-яА-
10ЯёЁ\'.-]+){1,}$' AS CHAR CHARACTER SET cp1251)) = 0)
11OR (LOCATE('.', NEW.`s_name`) = 0)
12THEN
13SET @msg = CONCAT('Subscribers name should contain at
14 |
|
least two words and one point, but the following |
15 |
|
name violates this rule: ', NEW.`s_name`); |
16SIGNAL 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 Стр: 339/545