Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 29: модификация данных с использованием «прозрачных» представлений
Oracle Решение 3.2.1.a (создание триггера для реализации операции вставки)
1CREATE OR REPLACE TRIGGER "subscribers_upper_case_ins"
2INSTEAD OF INSERT ON "subscribers_upper_case"
3FOR EACH ROW
4BEGIN
5INSERT INTO "subscribers"
6 |
|
|
("s_id", |
7 |
|
|
"s_name") |
8 |
|
VALUES |
(:new."s_id", |
9 |
|
|
:new."s_name"); |
10 |
|
END; |
|
Oracle Решение 3.2.1.a (создание триггера для реализации операции обновления)
1CREATE OR REPLACE TRIGGER "subscribers_upper_case_upd"
2INSTEAD OF UPDATE ON "subscribers_upper_case"
3FOR EACH ROW
4BEGIN
5UPDATE "subscribers"
6 |
|
SET |
"s_id" |
= |
:new."s_id", |
|
|
|
|
|
|
7 |
|
|
"s_name" |
= :new."s_name" |
|
8 |
|
WHERE |
"s_id" |
= |
:old."s_id"; |
9 |
|
END; |
|
|
|
|
|
|
|
|
|
Код обоих триггеров сводится к выполнению запроса на вставку или обновление к таблице, на которой построено представление. Как и в MySQL, мы можем использовать в Oracle ключевые слова old и new для обращения к «старым» (удаляемым или обновляемым) и новым (добавляемым или обновлённым) данным.
Теперь следующие запросы выполняются корректно (к слову, удаление и так не требовало никаких доработок, но проверить всё же стоит):
Oracle Решение 3.2.1.a (проверка работоспособности модификации данных)
1INSERT ALL
2INTO "subscribers" ("s_id", "s_name") VALUES (1, N'Соколов С.С.')
3INTO "subscribers" ("s_id", "s_name") VALUES (2, N'Беркутов Б.Б.')
4INTO "subscribers" ("s_id", "s_name") VALUES (3, N'Филинов Ф.Ф.')
5SELECT 1 FROM "DUAL";
6 |
|
|
|
|
7 |
|
UPDATE |
"subscribers_upper_case" |
|
8 |
|
SET |
"s_name" |
= N'Синицын З.З.' |
9 |
|
WHERE |
"s_id" = |
6; |
10 |
|
|
|
|
|
|
|
|
|
11-- Такой запрос НЕ НАЙДЁТ искомое, т.к. имя должно быть в верхнем регистре:
12UPDATE "subscribers_upper_case"
13 |
|
SET |
"s_name" |
= |
N'Синцын С.С.' |
14 |
|
WHERE |
"s_name" |
= |
N'Синицын З.З.'; |
15 |
|
|
|
|
|
16-- А такой запрос найдёт искомое:
17UPDATE "subscribers_upper_case"
18 |
|
SET |
"s_name" |
= |
N'Синцын С.С.' |
19 |
|
WHERE |
"s_name" |
= |
N'СИНИЦЫН З.З.'; |
20 |
|
|
|
|
|
21DELETE FROM "subscribers_upper_case"
22WHERE "s_id" = 6;
23
24-- Такой запрос НЕ НАЙДЁТ искомое, т.к. имя должно быть в верхнем регистре:
25DELETE FROM "subscribers_upper_case"
26WHERE "s_name" = N'Филинов Ф.Ф.';
27
28-- А такой запрос найдёт искомое:
29DELETE FROM "subscribers_upper_case"
30WHERE "s_name" = N'ФИЛИНОВ Ф.Ф.';
31
32DELETE FROM "subscribers_upper_case"
33WHERE "s_id" > 4;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 250/545
Пример 29: модификация данных с использованием «прозрачных» представлений
Решение 3.2.1.b{245}.
Решение этой задачи для MySQL подпадает под все ограничения, характерные для решения{246} задачи 3.2.1.a{245}: мы также можем создать представление или удовлетворяющее формату выборки и допускающее лишь удаление данных, или не удовлетворяющее формату выборки (с дополнительными полями) и допускающее обновление и удаление данных. Но вставка данных всё равно работать не будет.
MySQL Решение 3.2.1.b (представление, допускающее только удаление данных)
1CREATE VIEW `subscriptions_wcd`
2AS
3SELECT `sb_id`,
4`sb_subscriber`,
5`sb_book`,
6CONCAT(`sb_start`, ' - ', `sb_finish`) AS `sb_dates`,
7`sb_is_active`
8 FROM `subscriptions`
MySQL Решение 3.2.1.b (представление, допускающее удаление и обновление данных)
1CREATE VIEW `subscriptions_wcd_trick`
2AS
3SELECT `sb_id`,
4`sb_subscriber`,
5`sb_book`,
6CONCAT(`sb_start`, ' - ', `sb_finish`) AS `sb_dates`,
7`sb_start`,
8`sb_finish`,
9`sb_is_active`
10FROM `subscriptions`
За исключением уже неоднократно упомянутой проблемы со вставкой, не позволяющей полностью решить поставленную задачу в MySQL, код представлений совершенно тривиален и построен на элементарных запросах на выборку.
Упомянутые в решении{246} задачи 3.2.1.a{245} ограничения MS SQL Server относительно обновляемых представлений актуальны и в данной задаче: мы снова будем вынуждены создавать INSTEAD OF триггеры для реализации обновления и вставки данных.
MS SQL Решение 3.2.1.b (создание представления)
1CREATE VIEW [subscriptions_wcd]
2WITH SCHEMABINDING
3AS
4SELECT [sb_id],
5[sb_subscriber],
6[sb_book],
7CONCAT([sb_start], ' - ', [sb_finish]) AS [sb_dates],
8[sb_is_active]
9FROM [dbo].[subscriptions]
Такое представление уже позволяет извлекать данные в указанном в условии задачи формате, а также выполнять удаление данных. Для того, чтобы через это представление можно было выполнять вставку и обновление данных, нужно создать на нём два триггера — на операциях вставки и обновления.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 251/545
Пример 29: модификация данных с использованием «прозрачных» представлений
MS SQL Решение 3.2.1.b (создание триггера для реализации операции вставки)
1CREATE TRIGGER [subscriptions_wcd_ins]
2ON [subscriptions_wcd]
3INSTEAD OF INSERT
4AS
5SET IDENTITY_INSERT [subscriptions] ON;
6INSERT INTO [subscriptions]
7 |
|
|
([sb_id], |
8 |
|
|
[sb_subscriber], |
9 |
|
|
[sb_book], |
10 |
|
|
[sb_start], |
11 |
|
|
[sb_finish], |
12 |
|
|
[sb_is_active]) |
13 |
|
SELECT ( CASE |
|
14 |
|
|
WHEN [sb_id] IS NULL |
15 |
|
|
OR [sb_id] = 0 THEN IDENT_CURRENT('subscriptions') |
16 |
|
|
+ IDENT_INCR('subscriptions') |
17 |
|
|
+ ROW_NUMBER() OVER (ORDER BY |
18 |
|
|
(SELECT 1)) |
19 |
|
|
- 1 |
20 |
|
|
ELSE [sb_id] |
21 |
|
|
END ) AS [sb_id], |
22 |
|
|
[sb_subscriber], |
23 |
|
|
[sb_book], |
24 |
|
|
SUBSTRING([sb_dates], 1, (CHARINDEX(' ', [sb_dates]) - 1)) |
25 |
|
|
AS [sb_start], |
26 |
|
|
SUBSTRING([sb_dates], (CHARINDEX(' ', [sb_dates]) + 3), |
27 |
|
|
DATALENGTH([sb_dates]) — |
28 |
|
|
(CHARINDEX(' ', [sb_dates]) + 2)) |
29 |
|
|
AS [sb_finish], |
30 |
|
|
[sb_is_active] |
31 |
|
FROM |
[inserted]; |
32SET IDENTITY_INSERT [subscriptions] OFF;
33GO
Нетривиальная логика получения значения первичного ключа (строки 13-21) подробно объяснена в решении{246} задачи 3.2.1.a{245}.
Что касается получения значений полей sb_start и sb_finish (строки 2425 и 26-29), то здесь мы наблюдаем последствия ещё одного ограничения MS SQL Server: в псевдотаблице inserted нет полей, которых нет в представлении, на котором построен триггер. Т.е. единственный способ13 получить значения этих полей
— извлечь их из значения поля sb_dates. Это выглядит следующим образом (части строки, содержащей две даты, извлекаются с помощью строковых функций):
sb_start sb_finish
ГГГГ-ММ-ДД - ГГГГ-ММ-ДД
Такой подход является медленным и ненадёжным, но альтернатив ему нет. Если мы хотим повысить надёжность работы триггера, можно добавить дополнительную проверку на корректность формата «комбинированной даты» в поле sb_dates, но каждая такая дополнительная операция негативно отразится на производительности.
13 https://technet.microsoft.com/en-us/library/ms190188%28v=sql.105%29.aspx
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 252/545
Пример 29: модификация данных с использованием «прозрачных» представлений
Проверим, как будет работать вставка данных с использованием созданного триггера:
MS SQL 
Решение 3.2.1.b (проверка работоспособности вставки)
1 |
INSERT INTO [subscriptions_wcd] |
|
2 |
|
([sb_id], |
3 |
|
[sb_subscriber], |
4 |
|
[sb_book], |
5 |
|
[sb_dates], |
6 |
|
[sb_is_active]) |
7 |
VALUES |
(1000, |
8 |
|
1, |
9 |
|
3, |
10 |
|
'2017-01-12 - 2017-03-15', |
11 |
|
'N'), |
12 |
|
(2000, |
13 |
|
1, |
14 |
|
1, |
15 |
|
'2017-01-12 - 2017-03-15', |
16 |
|
'N'); |
17 |
|
|
18 |
INSERT INTO [subscriptions_wcd] |
|
19 |
|
([sb_subscriber], |
20 |
|
[sb_book], |
21 |
|
[sb_dates], |
22 |
|
[sb_is_active]) |
23 |
VALUES |
(1, |
24 |
|
3, |
25 |
|
'2019-01-12 - 2019-03-15', |
26 |
|
'N'), |
27 |
|
(1, |
28 |
|
1, |
29 |
|
'2019-01-12 - 2019-03-15', |
30 |
|
'N'); |
31 |
|
|
32 |
INSERT INTO [subscriptions_wcd] |
|
33 |
|
([sb_subscriber], |
34 |
|
[sb_book], |
35 |
|
[sb_dates], |
36 |
|
[sb_is_active]) |
37 |
VALUES |
(1, |
38 |
|
3, |
39 |
|
'Это -- не даты, а ерунда.', |
40 |
|
'N'); |
Первые два запроса работают корректно, а третий ожидаемо приводит к возникновению ошибочной ситуации:
Msg 241, Level 16, State 1, Procedure subscriptions_wcd_ins, Line 6 Conversion failed when converting date and/or time from character string.
Переходим к реализации обновления данных.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 253/545
Пример 29: модификация данных с использованием «прозрачных» представлений
MS SQL Решение 3.2.1.b (создание триггера для реализации операции обновления)
1CREATE TRIGGER [subscriptions_wcd_upd]
2ON [subscriptions_wcd]
3INSTEAD OF UPDATE
4AS
5IF UPDATE([sb_id])
6BEGIN
7 |
|
RAISERROR ('UPDATE |
of Primary Key through |
8 |
|
[subscriptions_wcd_upd] |
|
9 |
|
view is |
prohibited.', 16, 1); |
10ROLLBACK;
11END
12ELSE
13UPDATE [subscriptions]
14 |
|
SET |
[subscriptions].[sb_subscriber] = [inserted].[sb_subscriber], |
15 |
|
|
[subscriptions].[sb_book] = [inserted].[sb_book], |
16 |
|
|
[subscriptions].[sb_start] = |
17 |
|
|
SUBSTRING([sb_dates], 1, |
18 |
|
|
(CHARINDEX(' ', [sb_dates]) - 1)), |
19 |
|
|
[subscriptions].[sb_finish] = |
20 |
|
|
SUBSTRING([sb_dates], |
21 |
|
|
(CHARINDEX(' ', [sb_dates]) + 3), |
22 |
|
|
DATALENGTH([sb_dates]) — |
23 |
|
|
(CHARINDEX(' ', [sb_dates]) + 2)), |
24 |
|
|
[subscriptions].[sb_is_active] = [inserted].[sb_is_active] |
25 |
|
FROM |
[subscriptions] |
26 |
|
JOIN |
[inserted] |
27 |
|
ON |
[subscriptions].[sb_id] = [inserted].[sb_id]; |
28 |
|
GO |
|
|
|
|
|
Логика запрета обновления первичного ключа (строки 5-11) подробно объяснена в решении задачи 3.2.1.a.
Необходимость получать значения полей sb_start и sb_finish (строки 16-
23)только что была рассмотрена в реализации INSERT-триггера.
Востальном запрос в строках 14-27 представляет собой классическую реализацию обновления на основе выборки.
Остаётся убедиться, что обновление и удаление работает корректно:
MS SQL Решение 3.2.1.b (проверка работоспособности обновления и удаления)
1 |
|
UPDATE |
[subscriptions_wcd] |
2 |
|
SET |
[sb_dates] = '2021-01-12 - 2021-03-15' |
3 |
|
WHERE |
[sb_id] = 1000; |
4 |
|
|
|
5 |
|
DELETE |
FROM [subscriptions_wcd] |
6 |
|
WHERE |
[sb_id] = 2000; |
7 |
|
|
|
8DELETE FROM [subscriptions_wcd]
9WHERE [sb_dates] = '2021-01-12 - 2021-03-15';
Попытка обновить значение первичного ключа закономерно приведёт к блокировке операции:
|
MS SQL |
|
Решение 3.2.1.b (проверка невозможности обновления значения первичного ключа) |
||
|
1 |
|
UPDATE [subscriptions_wcd] |
||
|
2 |
|
SET |
[sb_id] = 999 |
|
|
|
|
|
|
|
3WHERE [sb_id] = 1000;
Врезультате выполнения такого запроса будет получено следующее сообщение об ошибке:
Msg 50000, Level 16, State 1, |
Procedure subscriptions_wcd_upd, Line 7 |
||||
UPDATE of |
Primary Key |
through |
[subscriptions_wcd_upd] view is prohibited. |
||
Msg |
3609, |
Level |
16, State 1, |
Line 1 |
|
The |
transaction |
ended |
in the |
trigger. The batch has been aborted. |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 254/545