Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 32: обеспечение консистентности данных
MS SQL |
Решение 4.1.2.b (триггеры для таблицы m2m_books_genres) (продолжение) |
38-- Реакция на удаление связи между книгами и жанрами:
39CREATE TRIGGER [g_has_books_on_m2m_b_g_del]
40ON [m2m_books_genres]
41AFTER DELETE
42AS
43UPDATE [genres]
44 |
|
SET |
[g_books] = [g_books] - [g_old_books] |
|
45 |
|
FROM |
[genres] |
|
46 |
|
|
JOIN (SELECT |
[g_id], |
47 |
|
|
|
COUNT([b_id]) AS [g_old_books] |
48 |
|
|
FROM |
[deleted] |
49 |
|
|
GROUP |
BY [g_id]) AS [prepared_data] |
50ON [genres].[g_id] = [prepared_data].[g_id];
51GO
Логика UPDATE-триггера чуть более сложная. Чтобы не выполнять два отдельных обновления таблицы genres, мы сначала в строках 26-34 запроса получаем «сводную таблицу» по удалённым и добавленным связям между книгами и жанрами. Эта таблица в некоторой гипотетической ситуации может выглядеть так:
g_id |
delta |
3 |
4 |
5 |
1 |
1 |
-3 |
3 |
-6 |
6 |
2 |
Со знаком минус представлено количество удалённых связей между книгами и жанрами, со знаком полюс — количество добавленных связей. Обратите внимание на жанр с идентификатором 3, у которого за одну операцию обновления часть связей было удалено, часть добавлено. В строке 25 запроса эти отрицательные и положительные значения суммируются, формируя таким образом итоговую дельту количества связей между жанрами и книгами.
g_id |
delta |
3 |
-2 |
5 |
1 |
1 |
-3 |
6 |
2 |
В строке 22 запроса эти данные используются для изменения значения счётчика связей между жанрами и книгами.
Проверить работоспособность полученного решения можно с помощью следующих запросов (которые подробно рассмотрены в решении для MySQL).
MS SQL Решение 4.1.2.b (проверка работоспособности)
1-- Добавление двух связей к жанру «Наука» (идентификатор жанра равен 4):
2INSERT INTO [m2m_books_genres]
3 |
|
|
([b_id], |
|
4 |
|
|
[g_id]) |
|
5 |
|
VALUES |
(1, |
4), |
6 |
|
|
(2, |
4); |
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 310/545
Пример 32: обеспечение консистентности данных
MS SQL Решение 4.1.2.b (проверка работоспособности) (продолжение)
7-- Изменение в добавленных связях значения идентификаторов книг,
8-- без изменения значения идентификаторов жанров:
9UPDATE [m2m_books_genres]
10 |
|
SET |
[b_id] = 3 |
11WHERE [b_id] = 1
12AND [g_id] = 4;
13 |
|
|
|
14 |
|
UPDATE |
[m2m_books_genres] |
15 |
|
SET |
[b_id] = 4 |
16WHERE [b_id] = 2
17AND [g_id] = 4;
18
19-- Изменение в добавленных связях значения идентификаторов жанров,
20-- без изменения значения идентификаторов книг:
21UPDATE [m2m_books_genres]
22 |
|
SET |
[g_id] = 5 |
23WHERE [b_id] = 3
24AND [g_id] = 4;
25 |
|
|
|
26 |
|
UPDATE |
[m2m_books_genres] |
27 |
|
SET |
[g_id] = 5 |
28WHERE [b_id] = 4
29AND [g_id] = 4;
30
31-- Изменение в добавленных связях значения идентификаторов жанров,
32-- и идентификаторов книг одновременно:
33UPDATE [m2m_books_genres]
34 |
|
SET |
[b_id] = 1, |
|
|
|
|
35[g_id] = 4
36WHERE [b_id] = 3
37AND [g_id] = 5;
38 |
|
|
|
39 |
|
UPDATE |
[m2m_books_genres] |
40 |
|
SET |
[b_id] = 2, |
41[g_id] = 4
42WHERE [b_id] = 4
43AND [g_id] = 5;
44
45-- Удаление ранее созданных связей:
46DELETE FROM [m2m_books_genres]
47WHERE [b_id] = 1
48AND [g_id] = 4;
49
50DELETE FROM [m2m_books_genres]
51WHERE [b_id] = 2
52AND [g_id] = 4;
53
54-- Удаление книг с идентификаторами 1 и 2 (обе эти книги одновременно
55-- относятся к жанрам «Поэзия» и «Классика»):
56DELETE FROM [books]
57WHERE [b_id] IN (1, 2);
Итак, решение для MySQL завершено и проверено.
Переходим к решению для Oracle. Т.к. данная СУБД не поддерживает псевдотаблицы inserted и deleted, мы будем опираться на логику решения для MySQL. Все соответствующие подробности этого решения уже описаны выше, потому здесь будет представлен только SQL-код.
Также отметим, что поскольку в Oracle триггеры активируются каскадными операциями, в данном решении (в отличие от решения для MySQL) не потребуется создавать DELETE-триггер на таблице books.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 311/545
Пример 32: обеспечение консистентности данных
Oracle Решение 4.1.2.b (модификация таблицы и инициализация данных)
1-- Модификация таблицы:
2ALTER TABLE "genres"
3ADD ("g_books" NUMBER(10) DEFAULT 0 NOT NULL);
4
5-- Инициализация данных:
6UPDATE "genres" "outer"
|
7 |
|
SET |
"g_books" = |
|
|
8 |
|
|
|
NVL((SELECT COUNT("b_id") AS "g_has_books" |
|
9 |
|
|
|
FROM "m2m_books_genres" |
|
10 |
|
|
|
WHERE "outer"."g_id" = "g_id" |
|
11 |
|
|
|
GROUP BY "g_id"), 0); |
|
|
|
|
|
|
|
Oracle |
|
|
Решение 4.1.2.b (триггеры для таблицы m2m_books_genres) |
|
1-- Реакция на добавление связи между книгами и жанрами:
2CREATE TRIGGER "g_has_bks_on_m2m_b_g_ins"
3AFTER INSERT
4ON "m2m_books_genres"
5FOR EACH ROW
6BEGIN
7UPDATE "genres"
8 |
|
SET |
"g_books" = "g_books" + 1 |
|
|
|
|
9WHERE "g_id" = :new."g_id";
10END;
11
12-- Реакция на обновление связи между книгами и жанрами:
13CREATE TRIGGER "g_has_bks_on_m2m_b_g_upd"
14AFTER UPDATE
15ON "m2m_books_genres"
16FOR EACH ROW
17BEGIN
18UPDATE "genres"
19 |
|
SET |
"g_books" = "g_books" + 1 |
20WHERE "g_id" = :new."g_id";
21UPDATE "genres"
22 |
|
SET |
"g_books" = "g_books" - 1 |
23WHERE "g_id" = :old."g_id";
24END;
25
26-- Реакция на удаление связи между книгами и жанрами:
27CREATE TRIGGER "g_has_bks_on_m2m_b_g_del"
28AFTER DELETE
29ON "m2m_books_genres"
30FOR EACH ROW
31BEGIN
32UPDATE "genres"
33 |
|
SET |
"g_books" = "g_books" - 1 |
34WHERE "g_id" = :old."g_id";
35END;
Проверить работоспособность полученного решения можно с помощью следующих запросов (которые подробно рассмотрены в решении для MySQL).
Oracle Решение 4.1.2.b (проверка работоспособности)
1-- Добавление двух связей к жанру «Наука» (идентификатор жанра равен 4):
2INSERT INTO "m2m_books_genres"
3 |
|
|
("b_id", "g_id") |
4 |
|
VALUES |
(1, 4); |
5 |
|
|
|
6 |
|
INSERT INTO "m2m_books_genres" |
|
7 |
|
|
("b_id", "g_id") |
8 |
|
VALUES |
(2, 4); |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 312/545
Пример 32: обеспечение консистентности данных
Oracle Решение 4.1.2.b (проверка работоспособности)
9-- Изменение в добавленных связях значения идентификаторов книг,
10-- без изменения значения идентификаторов жанров:
11UPDATE "m2m_books_genres"
12 |
|
SET |
"b_id" = 3 |
13WHERE "b_id" = 1
14AND "g_id" = 4;
15 |
|
|
|
16 |
|
UPDATE |
"m2m_books_genres" |
17 |
|
SET |
"b_id" = 4 |
18WHERE "b_id" = 2
19AND "g_id" = 4;
20
21-- Изменение в добавленных связях значения идентификаторов жанров,
22-- без изменения значения идентификаторов книг:
23UPDATE "m2m_books_genres"
24 |
|
SET |
"g_id" = 5 |
25WHERE "b_id" = 3
26AND "g_id" = 4;
27 |
|
|
|
28 |
|
UPDATE |
"m2m_books_genres" |
29 |
|
SET |
"g_id" = 5 |
30WHERE "b_id" = 4
31AND "g_id" = 4;
32
33-- Изменение в добавленных связях значения идентификаторов жанров,
34-- и идентификаторов книг одновременно:
35UPDATE "m2m_books_genres"
36 |
|
SET |
"b_id" = 1, |
|
|
|
|
37"g_id" = 4
38WHERE "b_id" = 3
39AND "g_id" = 5;
40 |
|
|
|
41 |
|
UPDATE |
"m2m_books_genres" |
42 |
|
SET |
"b_id" = 2, |
43"g_id" = 4
44WHERE "b_id" = 4
45AND "g_id" = 5;
46
47-- Удаление ранее созданных связей:
48DELETE FROM "m2m_books_genres"
49WHERE "b_id" = 1
50AND "g_id" = 4;
51
52DELETE FROM "m2m_books_genres"
53WHERE "b_id" = 2
54AND "g_id" = 4;
55
56-- Удаление книг с идентификаторами 1 и 2 (обе эти книги одновременно
57-- относятся к жанрам «Поэзия» и «Классика»):
58DELETE FROM "books"
59WHERE "b_id" IN (1, 2);
На этом решение данной задачи завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 313/545
Пример 32: обеспечение консистентности данных
Задание 4.1.2.TSK.A: доработать триггеры из решений{292}, {305} задач 4.1.2.a{292} и 4.1.2.b{292} таким образом, чтобы ни при каких манипуляциях с данными значения полей s_books (в таблице subscribers) и g_books (в таблице genres) не могли оказаться отрицательными.
Задание 4.1.2.TSK.B: модифицировать схему базы данных «Библиотека» таким образом, чтобы таблица subscribers хранила информацию о том, сколько раз читатель брал в библиотеке книги (этот счётчик должен инкрементироваться каждый раз, когда читателю выдаётся книга; уменьшение значения этого счётчика не предусмотрено).
Задание 4.1.2.TSK.C: оптимизировать код UPDATE-триггера из решения{292} задачи 4.1.2.a{292} для MS SQL Server так, чтобы выполнялась одна операция обновления таблицы subscribers (а не две отдельных операции, как это реализовано сейчас).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 314/545