Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 34: контроль формата и значений данных
MySQL Решение 4.2.2.a (триггеры для таблицы subscribers) (продолжение)
20CREATE TRIGGER `sbsrs_cntrl_name_upd`
21BEFORE UPDATE
22ON `subscribers`
23FOR EACH ROW
24BEGIN
25IF ((CAST(NEW.`s_name` AS CHAR CHARACTER SET cp1251) REGEXP
26CAST('^[a-zA-Zа-яА-ЯёЁ\'-]+([^a-zA-Zа-яА-ЯёЁ\'-]+[a-zA-Zа-яА-
27ЯёЁ\'.-]+){1,}$' AS CHAR CHARACTER SET cp1251)) = 0)
28OR (LOCATE('.', NEW.`s_name`) = 0)
29THEN
30SET @msg = CONCAT('Subscribers name should contain at
31 |
|
least two words and one point, but the following |
32 |
|
name violates this rule: ', NEW.`s_name`); |
33SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @msg, MYSQL_ERRNO = 1001;
34END IF;
35END;
36$$
37
38 DELIMITER ;
Поскольку MS SQL Server не поддерживает полноценные регулярные выражения, здесь мы используем альтернативное решение на основе подсчёта оставшихся в строке пробелов.
Вторая проблема MS SQL Server и его триггеров уровня выражения состоит в том, что в UPDATE-триггере мы обязаны запретить изменение первичного ключа (строки 50-56), иначе мы не сможем гарантированно корректно выполнить в коде триггера операцию обновления данных.
Третья уже знакомая нам проблема MS SQL Server связана с необходимостью вычисления значения автоинкрементируемого первичного ключа (строки 2938) в INSERT-триггере (см. пояснение в решении{246} задачи 3.2.1.a{245}).
Стоит отметить, что если объём данных у нас небольшой и производительность не снижается сколь бы то ни было заметным образом от использования AF- TER-триггеров, то решение этой задачи можно сделать гораздо более коротким, простым и универсальным (код INSERT- и UPDATE-триггера будет полностью идентичным). Убедитесь в этом самостоятельно, выполнив задание 4.2.2.TSK.B{343}.
MS SQL Решение 4.2.2.a (триггеры для таблицы subscribers)
1CREATE TRIGGER [sbsrs_cntrl_name_ins]
2ON [subscribers]
3INSTEAD OF INSERT
4AS
5DECLARE @bad_records NVARCHAR(max);
6DECLARE @msg NVARCHAR(max);
7 |
|
|
|
8 |
|
SELECT @bad_records = STUFF((SELECT |
', ' + [s_name] |
9 |
|
FROM |
[inserted] |
10 |
|
WHERE |
|
11 |
|
CHARINDEX(' ', LTRIM(RTRIM([s_name]))) = 0 |
|
|
|
|
|
12 |
|
OR CHARINDEX('.', [s_name]) = 0 |
|
13 |
|
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
|
14 |
|
1, 2, ''); |
|
15 |
|
|
|
16IF (LEN(@bad_records) > 0)
17BEGIN
18SET @msg = CONCAT('Subscribers name should contain at least two
19 |
|
words and one point, but the following names |
20 |
|
violate this rule: ', @bad_records); |
21RAISERROR (@msg, 16, 1);
22ROLLBACK TRANSACTION;
23RETURN;
24END;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 340/545
Пример 34: контроль формата и значений данных
MS SQL Решение 4.2.2.a (триггеры для таблицы subscribers) (продолжение)
25SET IDENTITY_INSERT [subscribers] ON;
26INSERT INTO [subscribers]
27 |
|
([s_id], |
28 |
|
[s_name]) |
29 |
|
SELECT ( CASE |
30 |
|
WHEN [s_id] IS NULL |
31 |
|
OR [s_id] = 0 THEN IDENT_CURRENT('subscribers') |
32 |
|
+ IDENT_INCR('subscribers') |
33 |
|
+ ROW_NUMBER() OVER (ORDER BY |
34 |
|
(SELECT 1)) |
35 |
|
- 1 |
36 |
|
ELSE [s_id] |
37 |
|
END ) AS [s_id], |
38 |
|
[s_name] |
39 |
|
FROM [inserted]; |
40SET IDENTITY_INSERT [subscribers] OFF;
41GO
42
43CREATE TRIGGER [sbsrs_cntrl_name_upd]
44ON [subscribers]
45INSTEAD OF UPDATE
46AS
47DECLARE @bad_records NVARCHAR(max);
48DECLARE @msg NVARCHAR(max);
49
50IF (UPDATE([s_id]))
51BEGIN
52RAISERROR ('Please, do NOT update surrogate PK
53 |
on table [subscribers]!', 16, 1); |
54ROLLBACK TRANSACTION;
55RETURN;
56END;
57 |
|
|
|
58 |
|
SELECT @bad_records = STUFF((SELECT |
', ' + [s_name] |
59 |
|
FROM |
[inserted] |
60 |
|
WHERE |
|
61 |
|
CHARINDEX(' ', LTRIM(RTRIM([s_name]))) = 0 |
|
62 |
|
OR CHARINDEX('.', [s_name]) = 0 |
|
63 |
|
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
|
64 |
|
1, 2, ''); |
|
65 |
|
|
|
66IF (LEN(@bad_records) > 0)
67BEGIN
68SET @msg = CONCAT('Subscribers name should contain at least two
69 |
|
words and one point, but the following names |
70 |
|
violate this rule: ', @bad_records); |
71RAISERROR (@msg, 16, 1);
72ROLLBACK TRANSACTION;
73RETURN;
74END;
75 |
|
|
|
76 |
|
UPDATE |
[subscribers] |
77 |
|
SET |
[subscribers].[s_name] = [inserted].[s_name] |
78 |
|
FROM |
[subscribers] |
79JOIN [inserted]
80ON [subscribers].[s_id] = [inserted].[s_id];
81GO
Переходим к решению для Oracle, которое полностью повторяет логику решения для MySQL — BEFORE-триггер на основе регулярного выражения и функции проверки существования подстроки в строке.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 341/545
Пример 34: контроль формата и значений данных
Oracle |
Решение 4.2.2.a (триггеры для таблицы subscribers) |
1CREATE TRIGGER "sbsrs_cntrl_name_ins_upd"
2BEFORE INSERT OR UPDATE
3ON "subscribers"
4FOR EACH ROW
5BEGIN
6IF ((NOT REGEXP_LIKE(:new."s_name", '^[a-zA-Zа-яА-ЯёЁ''-]+([^a-zA-Zа-яА-
7ЯёЁ''-]+[a-zA-Zа-яА-ЯёЁ''.-]+){1,}$'))
8OR (INSTRC(:new."s_name", '.', 1, 1) = 0))
9THEN
10RAISE_APPLICATION_ERROR(-20001, 'Subscribers name should contain
11 |
|
at least two |
words and |
one point, |
12 |
|
but the following name |
violates |
|
13 |
|
this rule: ' |
|| :new."s_name"); |
|
14END IF;
15END;
16
17
18
На этом решение данной задачи завершено. Убедиться в его корректности вы можете самостоятельно, выполнив запросы к таблице subscribers на вставку и обновление данных — как нарушающие условие задачи, так и не нарушающие.
Решение 4.2.2.b{338}.
Поскольку условие данной задачи во многом схоже с предыдущей, реализуем самое простое решение (для MS SQL Server используем AFTER-триггер) и ограничимся лишь кодом без подробных пояснений:
MySQL |
Решение 4.2.2.b (триггеры для таблицы books) |
1 DELIMITER $$
2
3CREATE TRIGGER `books_cntrl_year_ins`
4BEFORE INSERT
5ON `books`
6FOR EACH ROW
7BEGIN
8IF ((YEAR(CURDATE()) - NEW.`b_year`) > 100)
9THEN
10SET @msg = CONCAT('The following issuing year is more than
11 |
|
100 years in the past: ', NEW.`b_year`); |
12SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @msg, MYSQL_ERRNO = 1001;
13END IF;
14END;
15$$
16
17CREATE TRIGGER `books_cntrl_year_upd`
18BEFORE UPDATE
19ON `books`
20FOR EACH ROW
21BEGIN
22IF ((YEAR(CURDATE()) - NEW.`b_year`) > 100)
23THEN
24SET @msg = CONCAT('The following issuing year is more than
25 |
|
100 years in the past: ', NEW.`b_year`); |
26SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @msg, MYSQL_ERRNO = 1001;
27END IF;
28END;
29$$
30
31 DELIMITER ;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 342/545
Пример 34: контроль формата и значений данных
MS SQL |
Решение 4.2.2.b (триггеры для таблицы books) |
1CREATE TRIGGER [books_cntrl_year_ins_upd]
2ON [books]
3AFTER INSERT, UPDATE
4AS
5DECLARE @bad_records NVARCHAR(max);
6DECLARE @msg NVARCHAR(max);
7 |
|
|
|
8 |
|
SELECT @bad_records = STUFF((SELECT |
', ' + CAST([b_year] AS NVARCHAR) |
9 |
|
FROM |
[inserted] |
10 |
|
WHERE |
(YEAR(GETDATE()) - [b_year]) > 100 |
11 |
|
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), |
|
12 |
|
1, 2, ''); |
|
13 |
|
|
|
14IF (LEN(@bad_records) > 0)
15BEGIN
16SET @msg = CONCAT('The following issuing years are more
17 |
|
than 100 years in the past: ', @bad_records); |
18RAISERROR (@msg, 16, 1);
19ROLLBACK TRANSACTION;
20RETURN;
21END;
22GO
Oracle |
Решение 4.2.2.b (триггеры для таблицы books) |
1CREATE TRIGGER "books_cntrl_year_ins_upd"
2BEFORE INSERT OR UPDATE
3ON "books"
4FOR EACH ROW
5BEGIN
6IF ((TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - :new."b_year") > 100)
7THEN
8RAISE_APPLICATION_ERROR(-20001, 'The following issuing year is
9 |
|
more than 100 years in the past: ' |
10 |
|
|| :new."b_year"); |
11END IF;
12END;
На этом решение данной задачи завершено. Убедиться в его корректности вы можете самостоятельно, выполнив запросы к таблице books на вставку и обновление данных — как нарушающие условие задачи, так и не нарушающие.
Задание 4.2.2.TSK.A: модифицировать решение{338} задачи 4.2.2.a{338} для MySQL и Oracle так, чтобы в коде триггеров не использовались регулярные выражения.
Задание 4.2.2.TSK.B: переписать решение{338} задачи 4.2.2.a{338} для MS SQL Server с использованием AFTER-триггеров.
Задание 4.2.2.TSK.C: переписать регулярные выражения в решении{338} задачи 4.2.2.a{338} для MySQL и Oracle так, чтобы:
•исключить необходимость отдельной проверки наличия точки в имени читателя;
•допустить нахождение точки в любом из слов (а не только во втором и далее, как это сделано сейчас).
Задание 4.2.2.TSK.D: создать триггер, допускающий регистрацию в библиотеке только таких автором, имя которых не содержит никаких символов кроме букв, цифр, знаков - (минус), ' (апостроф) и пробелов (не допускается два и более идущих подряд пробела).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 343/545
Пример 35: прозрачное исправление ошибок в данных
4.2.3. Пример 35: прозрачное исправление ошибок в данных
Задача 4.2.3.a{344}: создать триггер, проверяющий наличие точки в конце имени читателя и добавляющий таковую при её отсутствии.
Задача 4.2.3.b{347}: создать триггер, меняющий дату возврата книги на «два месяца с момента выдачи», если дата возврата меньше даты выдачи или находится в прошлом.
Ожидаемый результат 4.2.3.a.
При выполнении операции модификации данных, нарушающей условие задачи, данные должны быть откорректированы в соответствии с условием задачи. Также должно быть выведено информационное сообщение в стиле: «Value [Иванов И.И] was automatically changed to [Иванов И.И.]».
Ожидаемый результат 4.2.3.b.
При выполнении операции модификации данных, нарушающей условие задачи, данные должны быть откорректированы в соответствии с условием задачи. Также должно быть выведено информационное сообщение в стиле: «Return date 2020.01.01 is less than giveaway date 2021.01.01. Return date changed to 2020.03.01.»
Решение 4.2.3.a{344}.
Приведённый ниже код триггеров для MySQL отличается от множества ранее рассмотренных подобных примеров только значением SQLSTATE: значения, начинающиеся с 01, сообщают не об ошибках, а о предупреждениях. И это — единственный способ сообщить из MySQL-триггера о выполненном преобразовании некорректного значения поля в корректное.
К сожалению, до версии 5.7.216 MySQL хранит предупреждения не по всей текущей сессии, а только по последнему выражению, потому в нашем случае (с использованием MySQL 5.6) мы не увидим этих сообщений. Но сами триггеры при этом корректно выполняют свою работу и корректируют некорректные данные.
MySQL Решение 4.2.3.a (триггеры для таблицы subscribers)
1 DELIMITER $$
2
3CREATE TRIGGER `sbsrs_name_lp_ins`
4BEFORE INSERT
5ON `subscribers`
6FOR EACH ROW
7BEGIN
8IF (SUBSTRING(NEW.`s_name`, -1) <> '.')
9THEN
10SET @new_value = CONCAT(NEW.`s_name`, '.');
11SET @msg = CONCAT('Value [', NEW.`s_name`, '] was automatically
12 |
|
changed to [', @new_value ,']'); |
13SET NEW.`s_name` = @new_value;
14SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = @msg, MYSQL_ERRNO = 1000;
15END IF;
16END;
17$$
16 http://dev.mysql.com/doc/refman/5.7/en/show-warnings.html
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 344/545