Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 32: обеспечение консистентности данных
MS SQL Решение 4.1.2.a (триггеры для таблицы subscriptions)
1-- Реакция на обновление выдачи книги:
2CREATE TRIGGER [s_has_books_on_subscriptions_upd]
3ON [subscriptions]
4AFTER UPDATE
5AS
6-- (Это, фактически, -- код DELETE-триггера):
7UPDATE [subscribers]
8 |
|
SET |
[s_books] = [s_books] - [s_old_books] |
|
9 |
|
FROM |
[subscribers] |
|
10 |
|
|
JOIN (SELECT |
[sb_subscriber], |
11 |
|
|
|
COUNT([sb_id]) AS [s_old_books] |
12 |
|
|
FROM |
[deleted] |
13 |
|
|
|
WHERE [sb_is_active] = 'Y' |
14 |
|
|
GROUP |
BY [sb_subscriber]) AS [prepared_data] |
|
|
|
|
|
15ON [s_id] = [sb_subscriber];
16-- (Это, фактически, -- код INSERT-триггера):
17UPDATE [subscribers]
18 |
|
SET |
[s_books] = [s_books] + [s_new_books] |
|
19 |
|
FROM |
[subscribers] |
|
20 |
|
|
JOIN (SELECT |
[sb_subscriber], |
21 |
|
|
|
COUNT([sb_id]) AS [s_new_books] |
22 |
|
|
FROM |
[inserted] |
23 |
|
|
|
WHERE [sb_is_active] = 'Y' |
24 |
|
|
GROUP |
BY [sb_subscriber]) AS [prepared_data] |
25ON [s_id] = [sb_subscriber];
26GO
MS SQL Решение 4.1.2.a (проверка работоспособности)
1 |
|
SET IDENTITY_INSERT [subscriptions] ON; |
2 |
|
|
|
|
|
3-- Добавим Иванову И.И. две активных выдачи,
4-- а Петрову П.П. одну активную и одну неактивную:
5INSERT INTO [subscriptions]
6 |
|
|
([sb_id], |
7 |
|
|
[sb_subscriber], |
8 |
|
|
[sb_book], |
9 |
|
|
[sb_start], |
10 |
|
|
[sb_finish], |
11 |
|
|
[sb_is_active]) |
12 |
|
VALUES |
(200, |
13 |
|
|
1, |
14 |
|
|
3, |
15 |
|
|
'2011-01-12', |
16 |
|
|
'2011-02-12', |
17 |
|
|
'Y'), |
18 |
|
|
(201, |
19 |
|
|
1, |
20 |
|
|
4, |
21 |
|
|
'2011-01-12', |
22 |
|
|
'2011-02-12', |
23 |
|
|
'Y'), |
24 |
|
|
(202, |
25 |
|
|
2, |
26 |
|
|
3, |
27 |
|
|
'2011-01-12', |
28 |
|
|
'2011-02-12', |
29 |
|
|
'Y'), |
30 |
|
|
(203, |
31 |
|
|
2, |
32 |
|
|
4, |
33 |
|
|
'2011-01-12', |
34 |
|
|
'2011-02-12', |
35 |
|
|
'N'); |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 300/545
Пример 32: обеспечение консистентности данных
MS SQL Решение 4.1.2.a (проверка работоспособности) (продолжение)
36-- Удалим добавленные выдачи:
37DELETE FROM [subscriptions]
38WHERE [sb_id] IN (200, 201, 202, 203);
39
40-- Проверим реакцию на обновление выдач книг.
41-- Сначала добавим две выдачи:
42INSERT INTO [subscriptions]
43 |
|
|
([sb_id], |
44 |
|
|
[sb_subscriber], |
45 |
|
|
[sb_book], |
46 |
|
|
[sb_start], |
47 |
|
|
[sb_finish], |
48 |
|
|
[sb_is_active]) |
49 |
|
VALUES |
(300, |
50 |
|
|
1, |
51 |
|
|
3, |
52 |
|
|
'2011-01-12', |
53 |
|
|
'2011-02-12', |
54 |
|
|
'Y'), |
55 |
|
|
(301, |
56 |
|
|
1, |
57 |
|
|
4, |
58 |
|
|
'2011-01-12', |
59 |
|
|
'2011-02-12', |
60 |
|
|
'Y'); |
61 |
|
|
|
62-- Не меняя идентификатор читателя сделаем выдачи неактивными:
63UPDATE [subscriptions]
64 |
|
SET |
[sb_is_active] = |
'N' |
65 |
|
WHERE |
[sb_id] IN (300, |
301); |
66 |
|
|
|
|
67-- Не меняя идентификатор читателя сделаем выдачи снова активными:
68UPDATE [subscriptions]
69 |
|
SET |
[sb_is_active] = |
'Y' |
70 |
|
WHERE |
[sb_id] IN (300, |
301); |
71 |
|
|
|
|
72-- Изменим идентификатор читателя, не меняя состояние активности выдач:
73UPDATE [subscriptions]
74 |
|
SET |
[sb_subscriber] = 2 |
75 |
|
WHERE |
[sb_id] IN (300, 301); |
76 |
|
|
|
77-- Изменим идентификатор читателя и сделаем выдачи неактивными:
78UPDATE [subscriptions]
79 |
|
SET |
[sb_subscriber] = 1, |
80[sb_is_active] = 'N'
81WHERE [sb_id] IN (300, 301);
82
83-- Изменим идентификатор читателя и сделаем выдачи активными:
84UPDATE [subscriptions]
85 |
|
SET |
[sb_subscriber] = 2, |
86[sb_is_active] = 'Y'
87WHERE [sb_id] IN (300, 301);
88
89-- Удаление книги с идентификатором 1 (выдана по одному экземпляру
90-- Петрову и обоим Сидоровым):
91DELETE FROM [books]
92WHERE [b_id] = 1;
93 |
|
94 |
SET IDENTITY_INSERT [subscriptions] OFF; |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 301/545
Пример 32: обеспечение консистентности данных
Переходим к решению поставленной задачи для Oracle. Модифицируем таблицу subscribers и проинициализируем добавленное поле данными.
MS SQL Решение 4.1.2.a (модификация таблицы и инициализация данных)
1-- Модификация таблицы:
2ALTER TABLE "subscribers"
3ADD ("s_books" INT DEFAULT 0 NOT NULL);
4
5-- Инициализация данных:
6UPDATE "subscribers"
7 |
|
SET |
"s_books" |
= NVL( |
|
8 |
|
|
(SELECT |
COUNT("sb_id") AS |
"s_has_books" |
9 |
|
|
FROM |
"subscriptions" |
|
10 |
|
|
WHERE |
"sb_is_active" = 'Y' |
|
11 |
|
|
AND |
"sb_subscriber" = |
"s_id" |
12 |
|
|
GROUP BY |
"sb_subscriber"), |
0); |
Обратите внимание: из-за синтаксических особенностей Oracle, вынуждающих нас писать такой запрос на обновление, приходится применять функцию NVL, потому что коррелирующий подзапрос в строках 7-16 выполнится для каждого ряда таблицы subscribers, и в некоторых случаях вернёт NULL.
Несмотря на то, что Oracle (как и MS SQL Server) поддерживает триггеры уровня выражения, мы не можем использовать представленную в решении для MS SQL логику, т.к. в Oracle нет псевдотаблиц inserted и updated. Нам придётся идти по пути решения для MySQL и использовать триггеры уровня записи.
Oracle Решение 4.1.2.a (триггеры для таблицы subscriptions)
1-- Реакция на добавление выдачи книги:
2CREATE OR REPLACE TRIGGER "s_has_books_on_sbps_ins"
3AFTER INSERT
4ON "subscriptions"
5FOR EACH ROW
6BEGIN
7IF (:new."sb_is_active" = 'Y') THEN
8UPDATE "subscribers"
9 |
|
SET |
"s_books" = "s_books" + 1 |
|
|
|
|
10WHERE "s_id" = :new."sb_subscriber";
11END IF;
12END;
13
14-- Реакция на удаление выдачи книги:
15CREATE OR REPLACE TRIGGER "s_has_books_on_sbps_del"
16AFTER DELETE
17ON "subscriptions"
18FOR EACH ROW
19BEGIN
20IF (:old."sb_is_active" = 'Y') THEN
21UPDATE "subscribers"
22 |
|
SET |
"s_books" = "s_books" - 1 |
23WHERE "s_id" = :old."sb_subscriber";
24END IF;
25END;
ВUPDATE-триггере мы также используем один в один тот же самый код, который был использован в решении для MySQL (там же были рассмотрены и показаны графически все ситуации, которые должен учитывать данный триггер).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 302/545
Пример 32: обеспечение консистентности данных
Oracle Решение 4.1.2.a (триггеры для таблицы subscriptions)
1-- Реакция на обновление выдачи книги:
2CREATE OR REPLACE TRIGGER "s_has_books_on_sbps_upd"
3AFTER UPDATE
4ON "subscriptions"
5FOR EACH ROW
6BEGIN
7-- A) Читатель тот же, Y -> N
8IF ((:old."sb_subscriber" = :new."sb_subscriber") AND
9(:old."sb_is_active" = 'Y') AND
10(:new."sb_is_active" = 'N')) THEN
11UPDATE "subscribers"
12 |
SET |
"s_books" = "s_books" - 1 |
13WHERE "s_id" = :old."sb_subscriber";
14END IF;
15
16-- B) Читатель тот же, N -> Y
17IF ((:old."sb_subscriber" = :new."sb_subscriber") AND
18(:old."sb_is_active" = 'N') AND
19(:new."sb_is_active" = 'Y')) THEN
20UPDATE "subscribers"
21 |
SET |
"s_books" = "s_books" + 1 |
22WHERE "s_id" = :old."sb_subscriber";
23END IF;
24
25-- C) Читатели разные, Y -> Y
26IF ((:old."sb_subscriber" != :new."sb_subscriber") AND
27(:old."sb_is_active" = 'Y') AND
28(:new."sb_is_active" = 'Y')) THEN
29UPDATE "subscribers"
30 |
|
SET |
"s_books" = "s_books" - 1 |
31WHERE "s_id" = :old."sb_subscriber";
32UPDATE "subscribers"
33 |
|
SET |
"s_books" = "s_books" + 1 |
34WHERE "s_id" = :new."sb_subscriber";
35END IF;
36
37-- D) Читатели разные, Y -> N
38IF ((:old."sb_subscriber" != :new."sb_subscriber") AND
39(:old."sb_is_active" = 'Y') AND
40(:new."sb_is_active" = 'N')) THEN
41UPDATE "subscribers"
42 |
|
SET |
"s_books" = "s_books" - 1 |
43WHERE "s_id" = :old."sb_subscriber";
44END IF;
45
46-- E) Читатели разные, N -> Y
47IF ((:old."sb_subscriber" != :new."sb_subscriber") AND
48(:old."sb_is_active" = 'N') AND
49(:new."sb_is_active" = 'Y')) THEN
50UPDATE "subscribers"
51 |
|
SET |
"s_books" = "s_books" + 1 |
52WHERE "s_id" = :new."sb_subscriber";
53END IF;
54END;
Витоге код триггеров для Oracle получился полностью идентичным коду триггеров для MySQL, потому и запросы для проверки работоспособности полученного решения также совпадают для обеих СУБД.
См. код самих запросов ниже, а логика их работы с пояснением и демонстрацией изменения содержимого таблицы subscribers представлена в решении для
MySQL.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 303/545
Пример 32: обеспечение консистентности данных
Oracle 
Решение 4.1.2.a (проверка работоспособности)
1 ALTER TRIGGER "TRG_subscriptions_sb_id" DISABLE; 2
3-- Добавим Иванову И.И. активную выдачу, а Петрову П.П. неактивную:
4INSERT INTO "subscriptions"
5 |
|
VALUES |
(200, |
6 |
|
|
1, |
7 |
|
|
1, |
8 |
|
|
TO_DATE('2011-01-12', 'YYYY-MM-DD'), |
9 |
|
|
TO_DATE('2011-02-12', 'YYYY-MM-DD'), |
10 |
|
|
'Y'); |
11 |
|
INSERT INTO "subscriptions" |
|
12 |
|
VALUES |
(201, |
13 |
|
|
2, |
14 |
|
|
1, |
15 |
|
|
TO_DATE('2011-01-12', 'YYYY-MM-DD'), |
16 |
|
|
TO_DATE('2011-02-12', 'YYYY-MM-DD'), |
17 |
|
|
'N'); |
18 |
|
|
|
19-- Удалим добавленные выдачи:
20DELETE FROM "subscriptions"
21WHERE "sb_id" IN ( 200, 201 );
22
23-- Проверим реакцию на обновление выдач книг. Сначала добавим выдачу:
24INSERT INTO "subscriptions"
25 |
|
VALUES |
(300, |
26 |
|
|
1, |
27 |
|
|
1, |
28 |
|
|
TO_DATE('2011-01-12', 'YYYY-MM-DD'), |
29 |
|
|
TO_DATE('2011-02-12', 'YYYY-MM-DD'), |
30 |
|
|
'Y'); |
31 |
|
|
|
32-- A) Не меняя идентификатор читателя сделаем выдачу неактивной:
33UPDATE "subscriptions"
34 |
|
SET |
"sb_is_active" |
= 'N' |
35 |
|
WHERE |
"sb_id" = 300; |
|
36 |
|
|
|
|
37-- B) Не меняя идентификатор читателя сделаем выдачу снова активной:
38UPDATE "subscriptions"
39 |
|
SET |
"sb_is_active" |
= 'Y' |
40 |
|
WHERE |
"sb_id" = 300; |
|
41 |
|
|
|
|
42-- C) Изменим идентификатор читателя, не меняя состояние активности выдачи:
43UPDATE "subscriptions"
44 |
|
SET |
"sb_subscriber" = 2 |
45 |
|
WHERE |
"sb_id" = 300; |
46 |
|
|
|
47-- D) Изменим идентификатор читателя и сделаем выдачу неактивной:
48UPDATE "subscriptions"
49 SET |
"sb_subscriber" = 1, |
50"sb_is_active" = 'N'
51WHERE "sb_id" = 300;
52
53-- E) Изменим идентификатор читателя и сделаем выдачу активной:
54UPDATE "subscriptions"
55 |
|
SET |
"sb_subscriber" = 2, |
56"sb_is_active" = 'Y'
57WHERE "sb_id" = 300;
58
59-- Удалим книгу с id = 1 (выдана по одной штуке Петрову и обоим Сидоровым):
60DELETE FROM [books]
61WHERE [b_id] = 1;
62 
63 ALTER TRIGGER "TRG_subscriptions_sb_id" ENABLE;
Итак, решение данной задачи получено и проверено для всех трёх СУБД.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 304/545