Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 30: модификация данных с использованием триггеров на представлениях
MS SQL I |
Решение 3.2.2.a (проверка работоспособности операции вставки) (продолжение) | |
65-- Запрос 5 (вставка НЕ выполняется):
66INSERT INTO [subscriptions with text]
67 |
|
([sb subscriber], |
68 |
|
[sb book], |
69 |
|
[sb start] |
70 |
|
[sb finish], |
71 |
|
[sb is active] |
72 |
VALUES |
(N'Какой-то читатель', |
73 |
|
N'Какая-то книга', |
74 |
|
'2015-01-12', |
75 |
|
'2015-02-12', |
76 |
|
'N'); |
Создадим триггер, позволяющий реализовать операцию обновления данных. Его логика будет несколько сложнее, чем в только что рассмотренном INSTEAD OF INSERT триггере.
Мы должны реагировать на нечисловые значения в полях sb_subscriber и sb_book только в том случае, если эти значения были явно переданы в запросе,
— этим вызвана необходимость создания более сложного условия в строках 7-10.
Если поля sb_subscriber и sb_book не были переданы в UPDATE-запросе,
в них естественным образом появятся человекочитаемые имена читателя и название книги (т.к. псевдотаблицы inserted и deleted наполняются данными из представления).
Эту ситуацию мы рассматриваем и исправляем в строках 29-42: если в полях оказываются нечисловые данные (и выполнение триггера дошло до этой части), значит в UPDATE-запросе эти поля не фигурируют, и их значения нужно взять из исходной таблицы (subscriptions).
И, наконец, как и было подчёркнуто в решении{246} задачи 3.2.1.a{245}, мы не можем корректно определить взаимоотношение записей в псевдотаблицах inserted и deleted, если было изменено значение первичного ключа, поэтому мы запрещаем эту операцию (строки 19-25).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 275/545
MS SQL |
Решение 3.2.2.a (создание триггера для реализации операции обновления) |
|||||
1 |
CREATE TRIGGER |
[subscriptions_with_text_upd] |
|
|||
ON |
[subscriptions_with_text] |
|
||||
2 |
|
|||||
INSTEAD OF UPDATE |
|
|
|
|||
3 |
|
|
|
|||
AS |
|
|
|
|
|
|
4 |
|
|
|
|
|
|
IF EXISTS(SELECT 1 |
|
|
|
|||
5 |
|
|
|
|||
|
FROM [inserted] |
|
|
|||
6 |
|
|
|
|||
|
WHERE |
( |
UPDATE([sb_subscriber] |
|
||
7 |
|
|
||||
|
|
AND PATINDEX('%[A0-9]%', [sb_subscriber]) > 0 |
||||
8 |
|
|
||||
9 |
|
OR |
( |
UPDATE([sb_book]) |
|
|
|
|
AND PATINDEX('%[A0-9]%', [sb_book]) > 0)) BEGIN |
||||
10 |
|
|
||||
11 |
|
RAISERROR ('Use digital identifiers for [sb_subscriber] |
||||
|
|
and |
[sb_book]. Do not use subscribers'' names |
|||
12 |
|
|
||||
|
|
or books'' titles', 16, 1); |
|
|||
13 |
|
|
|
|||
|
ROLLBACK; |
|
|
|
|
|
14 |
|
|
|
|
|
|
END ELSE |
|
|
|
|
||
15 |
|
|
|
|
||
|
BEGIN |
|
|
|
|
|
16 |
|
|
|
|
|
|
|
IF UPDATE [sb_id]) |
|
|
|||
17 |
|
|
|
|||
|
BEGIN |
|
|
|
|
|
18 |
|
|
|
|
|
|
|
RAISERROR ('UPDATE of Primary Key through |
|||||
19 |
|
|||||
|
|
[subscriptions_with_text] view is prohibited.', |
||||
20 |
|
|
||||
|
|
|
|
16, 1); |
||
21 |
|
|
|
|
||
|
ROLLBACK; END ELSE |
|
|
|||
22 |
|
|
|
|||
|
BEGIN |
|
|
|
|
|
23 |
|
|
|
|
|
|
|
UPDATE |
[subscriptions] |
|
|
||
24 |
|
|
|
|||
|
SET |
[subscriptions] [sb_subscriber] = |
||||
25 |
|
|||||
|
|
CASE |
|
|
|
|
26 |
|
|
|
|
|
|
|
|
WHEN (PATINDEX('%[A0-9]%', [inserted] |
||||
27 |
|
|
||||
28 |
|
|
|
|
[sb_subscriber])= 0) |
|
|
|
THEN [inserted].[sb_subscriber] ELSE [subscriptions] |
||||
29 |
|
|
||||
|
|
|
|
[sb_subscriber] |
||
30 |
|
|
|
|
||
|
|
END, [subscriptions][sb_book] |
= |
|||
31 |
|
|
||||
|
|
CASE |
|
|
|
|
32 |
|
|
|
|
|
|
|
|
WHEN (PATINDEX('%[A0-9]%', [inserted].[sb_book]) = 0) |
||||
33 |
|
|
||||
|
|
THEN [inserted].[sb_book] ELSE [subscriptions].[sb_book] |
||||
34 |
|
|
||||
|
|
END, [subscriptions] |
[sb_start] = |
|||
35 |
|
|
||||
|
|
[inserted] |
[sb_start] |
|
||
36 |
|
|
|
|||
|
|
[subscriptions].[sb_finish] = [inserted].[sb_finish], |
||||
37 |
|
|
||||
|
|
[subscriptions] |
[sb_is_active] = |
|||
Пример 30: модификация данных с использованием триггеров на представлениях
38
39 |
[inserted] [sb_is_active] |
|
FROM [subscriptions] |
||
40 |
||
JOIN [inserted] |
||
41 |
||
ON [subscriptions] [sb_id] = [inserted] [sb_id]; |
||
42 |
||
END |
||
43 |
||
END |
||
44 |
||
GO |
||
45 |
||
|
||
6 |
|
|
47 |
|
|
48 |
|
|
49 |
|
|
50 |
|
|
51 |
|
|
52 |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 276/545
Пример 30: модификация данных с использованием триггеров на представлениях
Проверим, как работают следующие запросы на обновление данных. Запросы 1-3 выполнятся успешно, а запросы 4-6 — нет, т.к. в запросах 4-5 происходит попытка передать нечисловые значения имени читателя и названия книги, а в запросе 6 происходит попытка обновить значение первичного ключа.
MS |
QL | решение 3.2.2.a (проверка работоспособности операции обновления) |
|
|
||
1 |
-- Запрос 1 (обновление выполняется): |
|
2 |
UPDATE [subscriptions_with_text] |
|
3 |
SET |
[sb_start] = '2021-01-12' |
4 |
WHERE |
[sb_id] = 5000; |
5 |
|
|
6-- Запрос 2 (обновление выполняется):
7UPDATE [subscriptions_with_text]
8 |
SET |
[sb_subscriber] = 3 |
9 |
WHERE |
[sb_id] = 5000; |
10 |
|
|
11-- Запрос 3 (обновление выполняется):
12UPDATE [subscriptions_with_text]
13 |
SET |
[sb_book] = 4 |
14 |
WHERE |
[sb_id] = 5000; |
15 |
|
|
16-- Запрос 4 (обновление НЕ выполняется):
17UPDATE [subscriptions_with_text]
18 |
SET |
[sb_subscriber] = N'Читатель' |
|
|
|
||
19 |
WHERE |
[sb_id] = 5000; |
|
|
|
||
20 |
-- Запрос 5 (обновление НЕ выполняется): |
||
21 |
|||
UPDATE [subscriptions_with_text] |
|||
22 |
|||
SET |
[sb_book] = N'Книга' |
||
23 |
|||
WHERE |
[sb_id] = 5000; |
||
24 |
|||
|
|
||
25 |
-- Запрос 6 (обновление НЕ выполняется): |
||
26 |
|||
UPDATE [subscriptions_with_text] |
|||
27 |
|||
SET |
[sb_id] = 5001 |
||
28 |
|||
WHERE |
[sb id] = 5000; |
||
29 |
|||
|
|
||
Создадим триггер, позволяющий реализовать операцию удаления данных. Обратите внимание, насколько его код короче и проще только что рассмотренных триггеров, реализующих операции вставки и обновления данных. Но, к сожалению, проблем с этим триггером будет намного больше.
MS SQL Решение 3.2.2.a (создание триггера для реализации операции удаления)
1CREATE TRIGGER [subscriptions_with_text_del]
2ON [subscriptions_with_text]
3INSTEAD OF DELETE
4AS
5DELETE FROM [subscriptions]
6WHERE [sb_id] IN (SELECT [sb_id]
7 |
FROM [deleted]); |
8 |
GO |
Первая проблема состоит в том, что MS SQL Server наполняет таблицу deleted данными до того, как передаёт управление триггеру. Поэтому мы никак не можем перехватить ситуацию передачи в DELETE-запрос строго числовых данных в полях sb_subscriber и sb_book, а такая ситуация приводит к ошибке выполнения запроса с резолюцией: невозможно преобразовать {текстовое значение} к {числовому значению}.
Вторая проблема состоит в том, что передача имени читателя или названия книги в виде числа (идентификатора), представленного строкой, приводит к нулевому количеству найденных совпадений (что вполне логично, т.к. представление извлекает не идентификаторы, а имена читателей и названия книг). Передача же
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 277/545
Пример 30: модификация данных с использованием триггеров на представлениях
полноценных имён читателей и/или названий книг позволяет обнаружить совпадения, но не гарантирует, что мы нашли нужные строки (напомним: у нас могут быть одноимённые читатели и книги с одинаковыми названиями).
Как уже было упомянуто, с первой проблемой мы ничего не можем сделать, вторая же имеет не самое надёжное и не самое красивое, но всё же работающее решение: мы можем получить внутри триггера код запроса, выполнение которого активировало триггер, и проверить, упомянуты ли в этом запросе поля sb_subscriber и/или sb_book. Если они упомянуты, мы отменим транзакцию.
MS SQL Решение 3.2.2.a (создание триггера для реализации операции удаления; более сложный вариант)
1CREATE TRIGGER [subscriptions with text del]
2ON [subscriptions_with_text]
3INSTEAD OF DELETE
4AS
5-- Попытка определить, переданы ли в DELETE-запрос
6-- поля sb subscriber и/или sb book:
7SET NOCOUNT ON;
8DECLARE @ExecStr VARCHAR 50), @Qry NVARCHAR 255);
9CREATE TABLE #inputbuffer
10(
11[EventType] NVARCHAR(30 ,
12[Parameters] INT,
13[EventInfo] NVARCHAR(255
14);
15
16SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')';
17INSERT INTO #inputbuffer EXEC @ExecStr);
18SET @Qry = LOWER((SELECT [EventInfo] FROM #inputbuffer));
20-- Для отладки можно раскомментировать следующую строку
21-- и убедиться, что в ней расположен запрос, вызвавший
22-- срабатывание триггера:
23 |
— PRINT(@Qry); |
|
24 |
|
|
25 |
IF |
((CHARINDEX ('sb subscriber', @Qry > 0 |
26 |
OR |
(CHARINDEX ('sb book', @Qry > 0») |
27 |
|
BEGIN |
28 |
|
RAISERROR ('Deletion from [subscriptions with text] view |
29 |
|
using [sb subscriber] and/or [sb book] |
30 |
|
is prohibited.', 16, 1); |
31 |
|
ROLLBACK; |
32END
33SET NOCOUNT OFF;
35-- Здесь выполняется само удаление:
36DELETE FROM [subscriptions]
37 |
WHERE [sb id] IN (SELECT [sb id] |
|
38 |
FROM |
[deleted]); |
39 |
GO |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 278/545
Пример 30: модификация данных с использованием триггеров на представлениях
Проверим, как работают запросы на удаление. В комментариях к каждому из запросов дано пояснение, в каких случаях он будет выполняться успешно или завершится той или иной ошибкой. Результат будет зависеть от того, какую из двух представленных выше версий триггера вы реализуете.
MS |
QL | |
решение 3.2.2.a (проверка раоотоспосооности операции удаления) |
|
|
|||
1 |
-- Запрос 1 (удаление работает): |
||
2 |
DELETE FROM [subscriptions_with_text] |
||
3 |
WHERE |
[sb_id] < 10 |
|
4 |
|
|
|
5 |
-- Запрос 2 (удаление работает): DELETE FROM |
||
6 |
|
|
[subscriptions_with_text] |
7 |
WHERE |
[sb_start] = '2012-01-12'; |
|
8 |
|
|
|
9— Запрос 3 (удаление НЕ работает
10-- при обеих версиях триггера): DELETE FROM
11 |
[subscriptions_with_text] |
12 |
WHERE [sb_book] = 2; |
13 |
|
14-- Запрос 4 (нулевое количество совпадений -- в
15первой версии триггера и отмена транзакции -- во
16второй версии триггера):
17DELETE FROM [subscriptions_with_text]
18 |
WHERE |
[sb_book] = '2'; |
|
|
|
||
19 |
— Запрос 5 (возможно обнаружение совпадений |
||
20 |
|||
-- в первой версии триггера; во второй версии |
|||
21 |
|||
-- триггера всегда будет отмена транзакции): |
|||
22 |
|||
DELETE FROM [subscriptions_with_text] |
|||
23 |
|||
WHERE |
[sb book] = N'Евгений Онегин'; |
||
24 |
|||
|
|
||
Переходим к решению данной задачи для Oracle. Код самого представления
— такой же, как для MySQL и MS SQL Server:
Oracle і Решение 3.2.2.a (создание представления)
1CREATE VIEW "subscriptions with text"
2AS
3SELECT "sb id",
4"s name" AS "sb subscriber"
5"b name" AS "sb book",
6"sb start",
7"sb finish",
8"sb is active"
9 FROM "subscriptions"
10JOIN "subscribers" ON "sb subscriber" = "s id"
11JOIN "books" ON "sb _book" = "b_id"
Создадим триггер, позволяющий реализовать операцию вставки данных. Логика проверки (строки 5-13) схожа в MS SQL Server и Oracle: исходная таб-
лица subscriptions содержит в полях sb_subscriber и sb_book числовые идентификаторы читателя и книги, потому мы обязаны получать вставляемые значения этих полей в числовом виде, следовательно, мы запрещаем операцию вставки нечисловых данных.
Алгоритмически мы выполняем одни и те же действия в обеих СУБД, а разница состоит лишь в функциях по работе с регулярными выражениями и генерации сообщения об ошибке.
Основная часть триггера, отвечающая непосредственно за вставку данных, в Oracle снова получилась чуть более простой, чем в MS SQL Server в силу возможности обрабатывать каждый ряд отдельно.
Механизм управления автоинкрементируемыми первичными ключами (построенный на последовательности (SEQUENCE) и отдельном триггере) позволяет
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 279/545