Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 30: модификация данных с использованием триггеров на представлениях
MS SQL Решение 3.2.2.a (проверка работоспособности операции вставки)
1-- Запрос 1 (вставка выполняется):
2INSERT INTO [subscriptions_with_text]
3 |
|
|
([sb_id], |
4 |
|
|
[sb_subscriber], |
5 |
|
|
[sb_book], |
6 |
|
|
[sb_start], |
7 |
|
|
[sb_finish], |
8 |
|
|
[sb_is_active]) |
9 |
|
VALUES |
(5000, |
10 |
|
|
1, |
11 |
|
|
3, |
12 |
|
|
'2015-01-12', |
13 |
|
|
'2015-02-12', |
14 |
|
|
'N'), |
15 |
|
|
(5005, |
16 |
|
|
1, |
17 |
|
|
1, |
18 |
|
|
'2015-01-12', |
19 |
|
|
'2015-02-12', |
20 |
|
|
'N'); |
21 |
|
|
|
22-- Запрос 2 (вставка выполняется):
23INSERT INTO [subscriptions_with_text]
24 |
|
|
([sb_subscriber], |
25 |
|
|
[sb_book], |
26 |
|
|
[sb_start], |
27 |
|
|
[sb_finish], |
28 |
|
|
[sb_is_active]) |
29 |
|
VALUES |
(1, |
30 |
|
|
3, |
31 |
|
|
'2015-01-12', |
32 |
|
|
'2015-02-12', |
33 |
|
|
'N'), |
34 |
|
|
(1, |
35 |
|
|
1, |
36 |
|
|
'2015-01-12', |
37 |
|
|
'2015-02-12', |
38 |
|
|
'N'); |
39 |
|
|
|
|
|
|
|
40-- Запрос 3 (вставка НЕ выполняется):
41INSERT INTO [subscriptions_with_text]
42 |
|
|
([sb_subscriber], |
43 |
|
|
[sb_book], |
44 |
|
|
[sb_start], |
45 |
|
|
[sb_finish], |
46 |
|
|
[sb_is_active]) |
47 |
|
VALUES |
(N'Иванов И.И.', |
48 |
|
|
3, |
49 |
|
|
'2015-01-12', |
50 |
|
|
'2015-02-12', |
51 |
|
|
'N'); |
52 |
|
|
|
53-- Запрос 4 (вставка НЕ выполняется):
54INSERT INTO [subscriptions_with_text]
55 |
|
|
([sb_subscriber], |
56 |
|
|
[sb_book], |
57 |
|
|
[sb_start], |
58 |
|
|
[sb_finish], |
59 |
|
|
[sb_is_active]) |
60 |
|
VALUES |
(1, |
61 |
|
|
N'Какая-то книга', |
62 |
|
|
'2015-01-12', |
63 |
|
|
'2015-02-12', |
64 |
|
|
'N'); |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 260/545
Пример 30: модификация данных с использованием триггеров на представлениях
MS SQL Решение 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 Стр: 261/545
Пример 30: модификация данных с использованием триггеров на представлениях
MS SQL Решение 3.2.2.a (создание триггера для реализации операции обновления)
1CREATE TRIGGER [subscriptions_with_text_upd]
2ON [subscriptions_with_text]
3INSTEAD OF UPDATE
4AS
5IF EXISTS(SELECT 1
6 |
|
FROM [inserted] |
|||
7 |
|
WHERE |
( |
|
UPDATE([sb_subscriber]) |
8 |
|
|
|
AND |
PATINDEX('%[^0-9]%', [sb_subscriber]) > 0) |
9 |
|
OR |
( |
|
UPDATE([sb_book]) |
10 |
|
|
|
AND |
PATINDEX('%[^0-9]%', [sb_book]) > 0)) |
11BEGIN
12RAISERROR ('Use digital identifiers for [sb_subscriber]
13 |
|
and [sb_book]. Do not use subscribers'' names |
14 |
|
or books'' titles', 16, 1); |
|
|
|
15ROLLBACK;
16END
17ELSE
18BEGIN
19IF UPDATE([sb_id])
20BEGIN
21 |
|
RAISERROR |
('UPDATE |
of Primary Key through |
|
22 |
|
|
[subscriptions_with_text] |
|
|
23 |
|
|
view is |
prohibited.', 16, |
1); |
24 |
|
ROLLBACK; |
|
|
|
25END
26ELSE
27 |
|
BEGIN |
|
28 |
|
UPDATE |
[subscriptions] |
29 |
|
SET |
[subscriptions].[sb_subscriber] = |
30 |
|
|
CASE |
31 |
|
|
WHEN (PATINDEX('%[^0-9]%', |
32 |
|
|
[inserted].[sb_subscriber]) = 0) |
33 |
|
|
THEN [inserted].[sb_subscriber] |
34 |
|
|
ELSE [subscriptions].[sb_subscriber] |
35 |
|
|
END, |
36 |
|
|
[subscriptions].[sb_book] = |
37 |
|
|
CASE |
38 |
|
|
WHEN (PATINDEX('%[^0-9]%', |
39 |
|
|
[inserted].[sb_book]) = 0) |
40 |
|
|
THEN [inserted].[sb_book] |
41 |
|
|
ELSE [subscriptions].[sb_book] |
42 |
|
|
END, |
43 |
|
|
[subscriptions].[sb_start] = [inserted].[sb_start], |
44 |
|
|
[subscriptions].[sb_finish] = [inserted].[sb_finish], |
45 |
|
|
[subscriptions].[sb_is_active] = |
6 |
|
|
[inserted].[sb_is_active] |
47 |
|
FROM |
[subscriptions] |
48 |
|
JOIN |
[inserted] |
49 |
|
ON |
[subscriptions].[sb_id] = [inserted].[sb_id]; |
50 |
|
END |
|
51END
52GO
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 262/545
Пример 30: модификация данных с использованием триггеров на представлениях
Проверим, как работают следующие запросы на обновление данных. Запросы 1-3 выполнятся успешно, а запросы 4-6 — нет, т.к. в запросах 4-5 происходит попытка передать нечисловые значения имени читателя и названия книги, а в запросе 6 происходит попытка обновить значение первичного ключа.
MS SQL Решение 3.2.2.a (проверка работоспособности операции обновления)
1-- Запрос 1 (обновление выполняется):
2UPDATE [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 |
|
|
|
|
|
|
|
|
|
21-- Запрос 5 (обновление НЕ выполняется):
22UPDATE [subscriptions_with_text]
23 |
|
SET |
[sb_book] |
= N'Книга' |
24 |
|
WHERE |
[sb_id] = |
5000; |
25 |
|
|
|
|
26-- Запрос 6 (обновление НЕ выполняется):
27UPDATE [subscriptions_with_text]
28 |
|
SET |
[sb_id] |
= |
5001 |
29 |
|
WHERE |
[sb_id] |
= |
5000; |
Создадим триггер, позволяющий реализовать операцию удаления данных. Обратите внимание, насколько его код короче и проще только что рассмотренных триггеров, реализующих операции вставки и обновления данных. Но, к сожалению, проблем с этим триггером будет намного больше.
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 Стр: 263/545
Пример 30: модификация данных с использованием триггеров на представлениях
полноценных имён читателей и/или названий книг позволяет обнаружить совпадения, но не гарантирует, что мы нашли нужные строки (напомним: у нас могут быть одноимённые читатели и книги с одинаковыми названиями).
Как уже было упомянуто, с первой проблемой мы ничего не можем сделать, вторая же имеет не самое надёжное и не самое красивое, но всё же работающее решение: мы можем получить внутри триггера код запроса, выполнение которого активировало триггер, и проверить, упомянуты ли в этом запросе поля sb_sub-
scriber и/или 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));
19
20-- Для отладки можно раскомментировать следующую строку
21-- и убедиться, что в ней расположен запрос, вызвавший
22-- срабатывание триггера:
23-- PRINT(@Qry);
24 |
|
|
25 |
IF |
((CHARINDEX ('sb_subscriber', @Qry) > 0) |
26OR (CHARINDEX ('sb_book', @Qry) > 0))
27BEGIN
28RAISERROR ('Deletion from [subscriptions_with_text] view
29 |
|
using [sb_subscriber] and/or [sb_book] |
30 |
|
is prohibited.', 16, 1); |
|
|
|
31ROLLBACK;
32END
33SET NOCOUNT OFF;
34
35-- Здесь выполняется само удаление:
36DELETE FROM [subscriptions]
37WHERE [sb_id] IN (SELECT [sb_id]
38 |
|
FROM [deleted]); |
39 |
|
GO |
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 264/545