Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 34: контроль формата и значений данных
MySQL I |
Решение 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-Za-яА-ЯёЁХ'-]+([ла^А-2а-яА-ЯёЁ\'-]+[a-zA-Za-HA-
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); |
21 |
RAISERROR @msg 16 1); |
22ROLLBACK TRANSACTION;
23RETURN;
24END;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 360/545
Пример 34: контроль формата и значений данных
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 361/545
Пример 34: контроль формата и значений данных
MS SQL і Решение 4.2.2.a (триггеры для таблицы subscribers) (продолжение)
25SET IDENTITY_INSERT.[subscribers] ON; .........
26INSERT INTO [subscribers]
|
[s_id] , |
|
|
27 |
[s_name]' |
|
|
28 |
SELECT ( CASE |
|
|
29 |
WHEN |
[s_id] IS NULL |
|
30 |
OR |
[s_id] = 0 THEN IDENT_CURRENT('subscribers') |
|
31 |
|
+ IDENT_INCR('subscribers') |
|
32 |
|
+ ROW_NUMBER() OVER (ORDER BY |
|
33 |
|
|
(SELECT 1)) |
34 |
|
|
- 1 |
35 |
|
ELSE |
[s_id] |
36 |
END ) AS [s_id] |
|
|
37[s_name]
38FROM [inserted];
39 |
SET |
IDENTITY_INSERT [subscribers] OFF; |
40 |
GO |
|
41 |
|
|
42CREATE TRIGGER [sbsrs_cntrl_name_upd]
43ON [subscribers] INSTEAD OF UPDATE AS
44DECLARE @bad_records NVARCHAR(max);
45DECLARE @msg NVARCHAR(max);
46 |
|
|
|
47 |
IF |
(UPDATE([s_id] |
) |
48BEGIN
49RAISERROR ('Please, do NOT update surrogate PK on table [subscribers]!',
50 |
16, 1); |
51ROLLBACK TRANSACTION; RETURN;
52END;
53 |
|
|
|
|
|
54 |
SELECT @bad_records = STUFF((SELECT ', ' |
+ |
[s_name] |
||
|
|
|
|
||
55 |
|
FROM [inserted] |
|
|
|
|
|
|
|
||
56 |
|
WHERE |
|
|
|
|
|
|
|
||
57 |
|
CHARINDEX(' ', LTRIM(RTRIM([s_name]))) = 0 OR |
|||
|
|
|
|
||
58 |
|
CHARINDEX('.', [s_name]) = 0 |
|
||
|
|
|
|
||
59 |
|
FOR XML PATH(''), TYPE).value('.', |
'nvarchar(max)'), |
||
|
|
|
|
||
60 |
|
1, 2, ''); |
|
|
|
|
|
|
|
||
61 |
IF (LEN(@bad_records) > 0) BEGIN |
|
|
||
62 |
|
|
|||
SET @msg = CONCAT('Subscribers name should contain at least two words and |
|||||
63 |
|||||
|
one point, but the following names violate this rule: |
||||
64 |
|
||||
|
', @bad_records); |
|
|
||
65 |
|
|
|
||
RAISERROR (@msg, 16, 1); |
|
|
|||
66 |
|
|
|||
ROLLBACK TRANSACTION; RETURN; |
|
|
|||
67 |
|
|
|||
END; |
|
|
|
||
68 |
|
|
|
||
|
|
|
|
||
69 |
UPDATE |
[subscribers] |
|
|
|
70 |
|
|
|||
SET |
[subscribers] [s_name]= [inserted] |
[s_name] |
|||
71 |
|||||
72FROM [subscribers]
73JOIN [inserted]
74ON [subscribers] [s_id] = [inserted] [s_id] GO
75Переходим к решению для Oracle, которое полностью повторяет логику 7677 решения для MySQL — BEFORE-триггер на основе регулярного выражения и
78 функции проверки существования подстроки в строке.
79
80
81
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 362/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[a-zA-Za-HA-HeE''-]+([Aa-zA-Za-nA-
7ЯёЁ''-J+Ea-zA-Za-яА-ЯёЁ''.-]+){1,}$'))
8 OR (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 I |
Решение 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
10 |
SET @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
24 |
SET @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 Стр: 363/545
Пример 34: контроль формата и значений данных
MS SQL I |
Решение 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 ; |
18 |
RAISERROR @msg 16 1!; |
19 |
ROLLBACK TRANSACTION; |
20 |
RETURN; |
21 |
END; |
22 |
GO |
Oracl |
і |
Решение 4.2.2.b (триггеры для таблицы books) |
| |
||
e |
|||||
|
|
|
|
||
1 |
CREATE TRIGGER "books cntrl year ins upd" |
||||
2 |
BEFORE INSERT OR UPDATE |
|
|
||
3 |
ON "books" |
|
|
||
4 |
FOR EACH ROW |
|
|
||
5 |
|
BEGIN |
|
|
|
6 |
|
IF ((TO NUMBER(TO CHAR(SYSDATE, 'YYYY')) - :new "b year") > 100) |
|||
7 |
|
THEN |
|
|
|
8 |
|
RAISE APPLICATION ERROR(-20001 |
'The following issuing year is |
||
9 |
|
|
|
more than 100 years in the past: ' |
|
10 |
|
|
|
|| :new "b year" ; |
|
11 |
|
END IF; |
|
|
|
12 |
|
END; |
|
|
|
На этом решение данной задачи завершено. Убедиться в его корректности вы можете самостоятельно, выполнив запросы к таблице 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 Стр: 364/545