Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 30: модификация данных с использованием триггеров на представлениях
нам не заботиться о том, как получить новое значение первичного ключа.
Oracle і Решение 3.2.2.a (создание триггера для реализации операции вставки) |
1CREATE OR REPLACE TRIGGER "subscriptions with text ins"
2INSTEAD OF INSERT ON "subscriptions with text"
3FOR EACH ROW
4BEGIN
5 |
IF |
((REGEXP INSTR( new "sb subscriber", '[Л0-9]') > 0) |
6 |
OR |
(REGEXP INSTR( new "sb book" '[Л0-9]') > 0 ) |
7THEN
8RAISE APPLICATION ERROR(-20001, 'Use digital identifiers for
9 |
"sb subscriber" and "sb book". |
||
10 |
Do |
not use |
subscribers'' names |
11 |
or |
books'' |
titles'); |
12ROLLBACK;
13END IF;
14INSERT INTO "subscriptions"
15 |
|
"sb id", |
16 |
|
"sb subscriber", |
17 |
|
"sb book" |
18 |
|
"sb start", |
19 |
|
"sb finish", |
20 |
|
"sb is active") |
21 |
VALUES |
(:new "sb id", |
22 |
|
new "sb subscriber" |
23 |
|
new "sb book", |
24 |
|
new "sb start", |
25 |
|
new "sb finish", |
26 |
|
new "sb is active"!; |
27 |
END; |
|
Проверим, как выполняются следующие запросы на вставку. Вполне ожидаемо запросы 1 и 2 выполняются корректно, а запросы 3-5 приводят к срабатыванию кода в строках 5-14 триггера и отмене транзакции, т.к. передача нечисловых значений для полей sb_subscriber и/или sb_book запрещена.
Oracl |
і |
Решение 3.2.2.a (проверка работоспособности операции вставки) |
| |
||
e |
|||||
|
|
|
|
||
1 |
-- Запрос 1 (вставка выполняется): |
|
|||
2 |
INSERT 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 |
|
|
TO DATE('2015-01-12', 'YYYY-MM-DD'), |
|
|
13 |
|
|
TO DATE('2015-02-12', 'YYYY-MM-DD'), |
|
|
14 |
|
|
'N'); |
|
|
15 |
|
|
|
|
|
16 |
-- Запрос 2 (вставка выполняется): |
|
|||
17 |
INSERT INTO "subscriptions with text" |
|
|||
18 |
|
|
"sb subscriber", |
|
|
19 |
|
|
"sb book", |
|
|
20 |
|
|
"sb start" |
|
|
21 |
|
|
"sb finish", |
|
|
22 |
|
|
"sb is active" |
|
|
23 |
VALUES |
1, |
|
||
24 |
|
|
3, |
|
|
25 |
|
|
TO DATE('2015-01-12', 'YYYY-MM-DD'), |
|
|
26 |
|
|
TO DATE('2015-02-12', 'YYYY-MM-DD'), |
|
|
27 |
|
|
'N'); |
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 280/545
Пример 30: модификация данных с использованием триггеров на представлениях
Oracl |
і |
Решение 3.2.2.a (проверка работоспособности операции вставки) (продолжение) |
| |
|
e |
||||
|
|
|
||
28 |
-- Запрос 3 (вставка НЕ выполняется): |
|
||
29 |
INSERT INTO "subscriptions with text" |
|
||
30 |
|
("sb subscriber", |
|
|
31 |
|
"sb book", |
|
|
32 |
|
"sb start" |
|
|
33 |
|
"sb finish" |
|
|
34 |
|
"sb is active" |
|
|
35 |
VALUES |
(N'Иванов И.И.', |
|
|
36 |
|
3, |
|
|
37 |
|
TO DATE('2015-01-12', 'YYYY-MM-DD'), |
|
|
38 |
|
TO DATE('2015-02-12', 'YYYY-MM-DD'), |
|
|
39 |
|
'N'); |
|
|
40 |
|
|
|
|
41 |
-- Запрос 4 (вставка НЕ выполняется): |
|
||
42 |
INSERT INTO "subscriptions with text" |
|
||
43 |
|
("sb subscriber", |
|
|
44 |
|
"sb book", |
|
|
45 |
|
"sb start" |
|
|
46 |
|
"sb finish", |
|
|
47 |
|
"sb is active" |
|
|
48 |
VALUES |
1, |
|
|
49 |
|
Ы'Какая-то книга', |
|
|
50 |
|
TO DATE('2015-01-12', 'YYYY-MM-DD'), |
|
|
51 |
|
TO DATE('2015-02-12', 'YYYY-MM-DD'), |
|
|
52 |
|
'N'); |
|
|
53 |
|
|
|
|
54 |
-- Запрос 5 (вставка НЕ выполняется): |
|
||
55 |
INSERT INTO "subscriptions with text" |
|
||
56 |
|
("sb subscriber", |
|
|
57 |
|
"sb book", |
|
|
58 |
|
"sb start" |
|
|
59 |
|
"sb finish", |
|
|
60 |
|
"sb is active" |
|
|
61 |
VALUES |
(N'Какой-то читатель', |
|
|
62 |
|
N'Какая-то книга', |
|
|
63 |
|
TO DATE('2015-01-12', 'YYYY-MM-DD'), |
|
|
64 |
|
TO DATE('2015-02-12', 'YYYY-MM-DD'), |
|
|
65 |
|
'N'); |
|
|
Создадим триггер, позволяющий реализовать операцию обновления данных. Как и в решении для MS SQL Server, мы должны реагировать на нечисловые значения в полях sb_subscriber и sb_book только в том случае, если эти значения были явно переданы в запросе, — этим вызвана необходимость более сложного
(чем в iNSERT-триггере) условия в строках 5-8.
В строках 19-30 мы вновь проверяем, пришло ли в наборе новых данных числовое значение полей sb_subscriber и sb_book, и используем имеющееся в таблице subscriptions старое значение, если новое является не числом.
В строках 23 и 29 применён «трюк» с добавлением (конкатенацией) к исходному значению поля пустой строки в юникод-представлении, что приводит к автоматическому преобразованию типа данных к юникод-строке. Это позволяет избежать ошибки компиляции триггера, вызванной несовпадением типов данных полей sb_subscriber и sb_book в таблице subscriptions (NUMBER) и представлении subscriptions_with_text (NVARCHAR2). При этом получившееся строковое
представление числа безошибочно автоматически преобразуется к типу NUMBER и корректно используется для вставки в таблицу subscriptions.
В отличие от MS SQL Server, где триггеру передаётся весь набор обрабатываемых данных, в Oracle наш триггер обрабатывает отдельно каждый обновляемый
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 281/545
Пример 30: модификация данных с использованием триггеров на представлениях
ряд, и потому мы точно знаем старое и новое значение первичного ключа. Это позволяет избавиться от запрета на обновление значения первичного ключа.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 282/545
Пример 30: модификация данных с использованием триггеров на представлениях
Oracle і Решение 3.2.2.a (создание триггера для реализации операции обновления) |
1CREATE OR REPLACE TRIGGER "subscriptions with text upd"
2INSTEAD OF UPDATE ON "subscriptions with text"
3FOR EACH ROW
4BEGIN
5 |
IF |
((:old "sb subscriber" != :new "sb subscriber") |
||
6 |
AND |
(REGEXP |
INSTR( |
new "sb subscriber", '[Л0-9]') > 0)) |
7 |
OR |
((:old "sb book" != new "sb book" |
||
8 |
AND |
(REGEXP |
INSTR( |
new "sb book" '[Л0-9]') > 0)) |
9THEN
10RAISE APPLICATION ERROR(-20001, 'Use digital identifiers for
11 |
"sb subscriber" and "sb book". |
||
12 |
Do |
not use |
subscribers'' names |
13 |
or |
books'' |
titles'); |
14ROLLBACK;
15END IF;
16 |
|
|
|
|
17 |
UPDATE "subscriptions" |
|||
18 |
SET |
"sb id" = |
new "sb id" |
|
19 |
|
"sb subscriber" = |
||
20 |
|
CASE |
|
|
21 |
|
WHEN (REGEXP INSTR( new "sb subscriber", '[Л0-9]') = 0) |
||
22 |
|
THEN |
new "sb subscriber" |
|
23 |
|
ELSE "sb subscriber" || N'' |
||
24 |
|
END, |
|
|
25 |
|
"sb book" = |
|
|
26 |
|
CASE |
|
|
27 |
|
WHEN (REGEXP INSTR( new "sb book" '[Л0-9]') = 0 |
||
28 |
|
THEN |
new "sb book" |
|
29 |
|
ELSE "sb book" || N'' |
||
30 |
|
END, |
|
|
31 |
|
"sb start" = |
new "sb start" |
|
32 |
|
"sb finish" = :new "sb finish" |
||
33 |
|
"sb is active" = new "sb is active" |
||
34 |
WHERE |
"sb id" = |
old "sb id"; |
|
35 |
END; |
|
|
|
Проверим, как работают следующие запросы на обновление данных. Запросы 1-3 и 6 выполнятся успешно (запрос 6 в MS SQL Server не выполняется изза обновления значения первичного ключа), а запросы 4-5 — нет, т.к. в них происходит попытка передать нечисловые значения имени читателя и названия книги.
Oracl |
і Решение 3.2.2.a (проверка работоспособности операции обновления) |
| |
||
e |
||||
|
|
|
||
1 |
-- Запрос 1 (обновление выполняется): |
|
||
2 |
UPDATE "subscriptions with text" |
|
||
3 |
SET |
"sb start" = TO DATE('2021-01-12', 'YYYY-MM-DD') |
||
4 |
WHERE |
"sb_id" = 101 |
|
|
|
|
|||
5 |
|
|
|
|
6 |
-- Запрос 2 (обновление выполняется): |
|
||
7 |
UPDATE "subscriptions with text" |
|
||
8 |
SET |
"sb subscriber" = 3 |
|
|
9 |
WHERE |
"sb id" = 101 |
|
|
10 |
|
|
|
|
11 |
-- Запрос 3 (обновление выполняется): |
|
||
12 |
UPDATE "subscriptions with text" |
|
||
13 |
SET |
"sb book" = 4 |
|
|
14 |
WHERE |
"sb id" = 101 |
|
|
15 |
|
|
|
|
16 |
-- Запрос 4 (обновление НЕ выполняется): |
|
||
17 |
UPDATE "subscriptions with text" |
|
||
18 |
SET |
"sb subscriber" = К'Читатель' |
|
|
19 |
WHERE |
"sb id" = 101 |
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 283/545
Пример 30: модификация данных с использованием триггеров на представлениях
Oracle |
Решение 3.2.2.a (проверка работоспособности операции обновления) (продолжение) |
|||
|
|
|
||
20 |
-- Запрос 5 (обновление НЕ выполняется) |
|||
21 |
UPDATE "subscriptions_with_text" |
|||
22 |
SET |
|
"sb_book" = Ы'Книга' |
|
23 |
WHERE |
"sb_id" = 101; |
||
24 |
|
|
|
|
25-- Запрос 6 (обновление выполняется):
26UPDATE "subscriptions_with_text"
27 |
SET |
"sb_id" |
= 1001 |
28 |
WHERE |
"sb id" |
= 101; |
Создадим триггер, позволяющий реализовать операцию удаления данных. Его код отличается от кода аналогичной триггера для MS SQL Server только логикой определения идентификаторов удаляемых записей.
Oracle і Решение 3.2.2.a (создание триггера для реализации операции удаления) |
1CREATE OR REPLACE TRIGGER "subscriptions with text del"
2INSTEAD OF DELETE ON "subscriptions with text"
3FOR EACH ROW
4BEGIN
5DELETE FROM "subscriptions"
6 |
WHERE "sb id" = old "sb id"; |
7 |
END; |
Oracle (как и MS SQL Server) выполняет операцию поиска соответствующих условию удаления записей до того, как передаёт управление триггеру. Поэтому мы никак не можем перехватить ситуацию передачи в DELETE-запрос строго числовых данных в полях sb_subscriber и sb_book, а такая ситуация приводит к ошибке выполнения запроса с резолюцией «некорректное числовое значение».
Вторая проблема (как и в случае с MS SQL Server) состоит в том, что передача имени читателя или названия книги в виде числа (идентификатора), представленного строкой, приводит к нулевому количеству найденных совпадений, а передача полноценных имён читателей и/или названий книг позволяет обнаружить совпадения, но не гарантирует, что мы нашли нужные строки (напомним: у нас могут быть одноимённые читатели и книги с одинаковыми названиями).
И если MS SQL Server позволяет решить вторую проблему через анализ SQLзапроса, активировавшего триггер, то в Oracle не существует способа получить текст SQL запроса в триггерах, реагирующих на выражения модификации данных (DML-триггерах). Таким образом, DELETE-триггер в решении данной задачи для Oracle скорее вреден и опасен, чем полезен — он приводит к появлению неожиданных сообщений об ошибках и позволяет случайно удалить лишние данные.
И всё же проверим, как работают запросы на удаление.
Oracle і |
Решение 3.2.2.a (проверка работоспособности операции удаления) |
[ |
1-- Запрос 1 (удаление работает):
2DELETE FROM "subscriptions_with_text"
3WHERE "sb_id" = 103;
4
5-- Запрос 2 (удаление работает):
6DELETE FROM "subscriptions_with_text"
WHERE "sb_start" = TO_DATE('2011-01-12', 'YYYY-MM-DD');
8
9-- Запрос 3 (удаление НЕ работает:
10-- ошибка преобразования типов):
11DELETE FROM "subscriptions_with_text"
12WHERE "sb book" = 2;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 284/545