Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 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) мы не увидим этих сообщений. Но сами триггеры при этом корректно выполняют свою работу и корректируют некорректные данные.
Решение 4.2.3.a |
для |
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/showwarnings.html
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 365/545
Пример 35: прозрачное исправление ошибок в данных
MySQL Решение 4.2.3.a (триггеры для таблицы subscribers) (продолжение)
18CREATE TRIGGER 'sbsrs_name_lp_upd'
19BEFORE UPDATE
20ON 'subscribers'
21FOR EACH ROW
22BEGIN
23IF (SUBSTRING(NEW.'s_name', -1) <> '.')
24THEN
25SET @new_value = CONCAT(NEW.'s_name', '.');
26SET @msg = CONCAT('Value [', NEW.'s_name', '] was automatically
27 |
changed to [', @new_value ,']'); |
28SET NEW.'s_name' = @new_value;
29SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = @msg MYSQL_ERRNO = 1000;
30END IF;
31END;
32$$
33
34DELIMITER ;
ВMS SQL Server так же просто и красиво подменить некорректноезначение корректным не получится, т.к. в этой СУБД нет триггеров уровня записи.
Потому нам придётся создавать INSTEAD OF триггеры (со всемивытекающими отсюда проблемами и ограничениями в виде «ручной» генерациизначения автоинкрементируемого первичного ключа на вставке данных и запрета изменения значения первичного ключа на обновлении данных).
Зато в MS SQL Server можно очень легко и удобно передавать сообщения из триггеров. Для демонстрации этих возможностей в представленном ниже коде реализовано два варианта поведения:
•В строках 18 и 68: с помощью конструкции PRINT (которая просто выводит текстовое сообщение в консоль).
•В строках 19 и 69: с помощью функции RAISERROR (которая при таких параметрах (см. документацию16) генерирует сообщение, не приводящее к остановке операции; к тому же мы не «откатываем» транзакцию в теле триггера).
MS SQL |
Решение 4.2.3.a (триггеры для таблицы subscribers) |
|||
1 |
CREATE TRIGGER [sbsrs name lp ins] |
|
||
2 |
ON [subscribers] |
|
|
|
3 |
INSTEAD OF INSERT |
|
|
|
4 |
AS |
|
|
|
5 |
|
DECLARE @bad records NVARCHAR(max); |
||
6 |
|
DECLARE @msg NVARCHAR(max); |
|
|
7 |
|
|
|
|
8 |
|
SELECT @bad records = STUFF((SELECT ', ' + '[' + [s name] + '] -> [' + |
||
9 |
|
|
|
[s name] + '.]' |
10 |
|
|
FROM |
[inserted] |
11 |
|
|
WHERE |
RIGHT([s name], 1 <> '.' |
12 |
|
FOR XML PATH(''), TYPE) value('.', 'nvarchar(max)'), |
||
13 |
|
1, 2 |
''); |
|
14 |
|
|
|
|
15 |
|
IF (LEN @bad records) > 0 |
|
|
16 |
|
BEGIN |
|
|
17 |
|
SET @msg = CONCAT('Some values were changed: ', @bad records); |
||
18 |
|
PRINT @msg; |
|
|
19 |
|
RAISERROR @msg |
16 0); |
|
20 |
|
END; |
|
|
|
|
|
|
|
16 https://msdn.microsoft.com/en-us/library/ms178592.aspx
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 366/545
Пример 35: прозрачное исправление ошибок в данных
MS SQL I |
Решение 4.2.3.a (триггеры для таблицы subscribers) (продолжение) |
| |
21SET IDENTITY INSERT [subscribers] ON;
22INSERT INTO [subscribers]
23 |
|
([s id], |
|
24 |
|
[s name]) |
|
25 |
SELECT ( CASE |
|
|
26 |
|
WHEN [s id] IS NULL |
|
27 |
|
OR [s id] = 0 THEN IDENT CURRENT('subscribers') |
|
28 |
|
+ IDENT INCR('subscribers') |
|
29 |
|
+ ROW NUMBER() OVER (ORDER BY |
|
30 |
|
|
(SELECT 1 ) |
31 |
|
|
- 1 |
32 |
|
ELSE [s id] |
|
33 |
|
END ) AS [s id] |
|
34 |
|
( CASE |
|
35 |
|
WHEN RIGHT([s name], 1 |
<> '.' |
36 |
|
THEN CONCAT [s name] |
'.') |
37 |
|
ELSE [s name] |
|
38 |
|
END ) AS [s name] |
|
39 |
FROM |
[inserted] |
|
40SET IDENTITY INSERT [subscribers] OFF;
41GO
42
43CREATE TRIGGER [sbsrs name lp 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 |
|
[s name] + '.]' |
|
60 |
FROM |
[inserted] |
|
61 |
WHERE |
RIGHT([s name], |
1 <> '.' |
62 |
FOR XML PATH(''), TYPE) value('.', 'nvarchar(max)'), |
||
63 |
1, 2 ''); |
|
|
64 |
|
|
|
65IF (LEN @bad records) > 0
66BEGIN
67SET @msg = CONCAT('Some values were changed: ', @bad records);
68PRINT @msg;
69 |
RAISERROR @msg 16 0); |
|
70 |
END; |
|
71 |
|
|
72 |
UPDATE [subscribers] |
|
73 |
SET |
[subscribers] [s name] = |
74 |
|
( CASE |
75 |
|
WHEN RIGHT([inserted] [s name], 1 <> '.' |
76 |
|
THEN CONCAT [inserted] [s name], '.') |
77 |
|
ELSE [inserted] [s name] |
78 |
|
END ) |
79 |
FROM |
[subscribers] |
80 |
|
JOIN [inserted] |
81 |
|
ON [subscribers] [s id] = [inserted] [s id] |
82 |
GO |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 367/545
Пример 35: прозрачное исправление ошибок в данных
Решение для Oracle традиционно повторяет логику решения для MySQL с той лишь разницей, что здесь мы можем только вывести текстовое сообщение (сгенерировать предупреждение, не влияющее на выполнение операции, на текущий момент в Oracle нельзя).
Чтобы увидеть выводимое сообщение необходимо предварительно выполнить команду SET SERVEROUTPUT ON, включающую показ таких данных.
|
Oracl |
і |
Решение 4.2.3.a (триггеры для таблицы subscribers) |
| |
||
e |
|
|||||
|
|
|
|
|
|
|
1 |
|
CREATE TRIGGER "sbsrs name lp ins upd" |
|
|||
2 |
|
BEFORE INSERT OR UPDATE |
|
|
|
|
3 |
|
ON "subscribers" |
|
|
|
|
4 |
|
FOR EACH ROW |
|
|
|
|
5 |
|
DECLARE |
|
|
|
|
6 |
|
|
new value NVARCHAR2 150); |
|
|
|
7 |
|
|
BEGIN |
|
|
|
8 |
|
|
IF (SUBSTR(:new "s name" |
-1 |
<> '.') |
|
9THEN
10new value := CONCAT( new "s name" '.');
11DBMS OUTPUT.PUT LINE('Value [' || new "s name" ||
12'] was automatically changed to [' || new value || ']');
13new "s name" := new value
14END IF;
15END;
На этом решение данной задачи завершено. Убедиться в его корректности вы можете самостоятельно, выполнив запросы к таблице subscribers на вставку
иобновление данных — как нарушающие условие задачи, так и не нарушающие.
уЦ7
Решение 4.2.3.b{344}.
Общая логика решения данной задачи повторяет логику решения{344} задачи 4.2.3. a{344}, и достойным отдельного упоминания здесь можно считать только следующее:
•в MySQL и MS SQL Server приходится делать два отдельных триггера (MySQL не умеет «объединять» объявление триггеров для нескольких операций, а в MS SQL Server различается внутреннее поведение INSERT- и UPDATE-
триггера), в то время как в Oracle получается компактный одинаковый код, актуальный для обеих операций;
•в MS SQL Server без доработки модели БД можно создать только INSTEAD OF INSERT триггер, а для создания INSTEAD OF UPDATE триггера придётся отключить каскадное обновление на внешних ключах таблицы subscriptions и реализовать соответствующие операции по обеспечению ссылочной целостности в самом триггере;
•отображаемые триггерами сообщения об автоматической корректировке значения поля sb_finish в представленной ниже реализации могут содержать начальное и конечное значение даты в разных форматах (чтобы этого избежать, нужно явно приводить оба значения к одинаковому формату даты).
Востальном все представленные далее триггеры содержат лишь вариации на тему рассмотренных ранее операций.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 368/545
Пример 35: прозрачное исправление ошибок в данных
MySQL I |
Решение 4.2.3.b (триггеры для таблицы subscriptions) | |
|
|
1 |
DELIMITER $$ |
|
|
2 |
|
|
|
3 |
CREATE TRIGGER 'sbscs date tm ins' |
|
|
4 |
BEFORE INSERT |
|
|
5 |
ON 'subscriptions' |
|
|
6 |
|
FOR EACH ROW |
|
7 |
|
BEGIN |
|
8 |
|
IF (NEW.'sb finish' < NEW.'sb start') OR (NEW.'sb |
< CURDATE() ) |
9 |
|
THEN |
|
10 |
|
SET @new value = DATE ADD(CURDATE(), INTERVAL 2 |
|
11 |
|
SET @msg = CONCAT('Value [', NEW.'sb finish', '] was automatically |
|
12 |
|
changed to [', @new value ,']'); |
|
13 |
|
SET NEW.'sb finish' = @new value; |
|
14 |
|
SIGNAL SQLSTATE '01000' SET MESSAGE TEXT = @msg, MYSQL ERRNO = 1000; |
|
15 |
|
END IF; |
|
16 |
|
END; |
|
17 |
$$ |
|
|
18 |
|
|
|
19 |
CREATE TRIGGER 'sbscs date tm upd' |
|
|
20 |
BEFORE UPDATE |
|
|
21 |
ON 'subscriptions' |
|
|
22 |
|
FOR EACH ROW |
|
23 |
|
BEGIN |
|
24 |
|
IF (NEW.'sb finish' < NEW.'sb start') OR (NEW.'sb |
< CURDATE() ) |
25 |
|
THEN |
|
26 |
|
SET @new value = DATE ADD(CURDATE(), INTERVAL 2 |
|
27 |
|
SET @msg = CONCAT('Value [', NEW.'sb finish', '] was automatically |
|
28 |
|
changed to [', @new value ,']'); |
|
29 |
|
SET NEW.'sb finish' = @new value; |
|
30 |
|
SIGNAL SQLSTATE '01000' SET MESSAGE TEXT = @msg, MYSQL ERRNO = 1000; |
|
31 |
|
END IF; |
|
32 |
|
END; |
|
33 |
$$ |
|
|
34 |
|
|
|
35 |
DELIMITER ; |
|
|
|
|
|
|
MS SQL Решение 4.2.3.b (триггеры для таблицы subscriptions)
1 |
CREATE TRIGGER [sbscs date tm ins] |
||
2 |
ON [subscriptions] |
|
|
3 |
INSTEAD OF INSERT |
|
|
4 |
AS |
|
|
5 |
DECLARE @bad records NVARCHAR(max); |
||
6 |
DECLARE @msg NVARCHAR(max); |
||
7 |
|
|
|
8 |
SELECT @bad records = |
|
|
9 |
STUFF((SELECT ', ' + '[' + CAST([sb finish] AS NVARCHAR) + |
||
10 |
|
'] -> [' + FORMAT(DATEADD(month, 2, GETDATE()), |
|
11 |
|
|
'yyyy-MM-dd') + ']' |
12 |
|
FROM |
[inserted] |
13 |
|
WHERE |
([sb finish] < [sb start]) OR |
14 |
|
|
([sb finish] < GETDATE()) |
15 |
FOR XML PATH(''), TYPE) value ('.', 'nvarchar(max) '), |
||
16 |
1, 2, |
''); |
|
17 |
|
|
|
18 |
IF (LEN @bad records) > 0 |
||
19 |
BEGIN |
|
|
20 |
SET @msg = CONCAT('Some values were changed: ', @bad records); |
||
21 |
PRINT @msg; |
|
|
22 |
RAISERROR |
@msg 16 |
0); |
23 |
END; |
|
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 369/545