Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 32: обеспечение консистентности данных
Изменим идентификатор читателя и сделаем выдачу активной:
MySQL I |
Решение 4.1.2.a (проверка работоспособности) | |
1-- E
2UPDATE 'subscriptions'
3 |
SET |
'sb_subscriber' = 2, |
4'sb_is_active' = 'Y'
5WHERE 'sb id' = 300
s_id |
s_name |
s_books |
1 |
Иванов И.И. |
0 |
2 |
Петров П.П. |
1 |
3 |
Сидоров С.С. |
3 |
4 |
Сидоров С.С. |
2 |
Удалим книгу с идентификатором 1 (такая книга сейчас выдана Петрову и обоим Сидоровым по одному экземпляру):
MySQL і Решение 4.1.2.a (проверка работоспособности) |
1DELETE FROM 'books'
2WHERE 'b id' = 1
s_id |
s_name s_books |
1Иванов И.И. 0
2Петров П.П. 0
3Сидоров С.С. 2
4Сидоров С.С. 1
Как показало исследование, все операции выполняются корректно и приводят к верным изменениям значений поля s_books.
Переходим к решению поставленной задачи для MS SQL Server. Модифицируем таблицу subscribers и проинициализируем добавленное поле данными.
MS SQL Решение 4.1.2.a (модификация таблицы и инициализация данных)
1-- Модификация таблицы:
2ALTER TABLE [subscribers]
34 ADD [s_books] INT NOT NULL DEFAULT 0;
|
-- Инициализация данных: |
||
6 |
UPDAT |
[subscribers] |
|
7 |
SET |
[s books] = [s has books] |
|
8 |
FROM |
[subscribers] |
|
9 |
|
JOIN (SELECT [sb_subscriber], |
|
10 |
|
|
COUNT([sb_id]) AS [s_has_books] |
11 |
|
FROM |
[subscriptions] |
12 |
|
WHERE |
[sb_is_active] = 'Y' |
13 |
|
GROUP |
BY [sb_subscriber]) AS [prepared_data] |
14 |
|
ON [s id] = [sb subscriber]; |
|
Логика работы триггеров в MS SQL Server будет иной, т.к. эта СУБД не поддерживает триггеры уровня записи, и нам придётся за один раз обрабатывать все произведённые изменения.
С INSERT- и DELETE-триггерами, как и в случае с MySQL, будет более-менее
просто — нужно выяснить идентификаторы читателей, получивших (или вернувших) книги, количество таких книг по каждому читателю, а затем увеличить или уменьшить счётчики выданных книг соответствующим читателям на соответствующие величины.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 315/545
Пример 32: обеспечение консистентности данных
MS SQL Решение 4.1.2.a |
для |
1-- Реакция на добавление выдачи книги:
2CREATE TRIGGER [s has books on subscriptions ins]
3ON [subscriptions]
4AFTER INSERT
5AS
6UPDATE [subscribers]
7 |
SET |
[s books] = [s books] + [s new books] |
8 |
FROM |
[subscribers] |
9 |
|
JOIN (SELECT [sb subscriber], |
10 |
|
COUNT |
[sb id]) AS [s new books] |
11 |
FROM |
[inserted] |
|
12 |
|
WHERE |
[sb is active] = 'Y' |
13 |
GROUP |
BY [sb subscriber] AS [prepared data] |
|
14ON [s id] = [sb subscriber]
15GO
16
17-- Реакция на удаление выдачи книги:
18CREATE TRIGGER [s has books on subscriptions del]
19ON [subscriptions]
20AFTER DELETE
21AS
22UPDATE [subscribers]
23 |
SET |
[s books] = [s books] - [s old books] |
|
24 |
FROM |
[subscribers] |
|
25 |
|
JOIN (SELECT [sb subscriber], |
|
26 |
|
|
COUNT [sb id]) AS [s old books] |
27 |
|
FROM |
[deleted] |
28 |
|
|
WHERE [sb is active] = 'Y' |
29 |
|
GROUP |
BY [sb subscriber] AS [prepared data] |
30 |
|
ON [s id] = [sb subscriber] |
|
31 |
|
|
|
32 |
GO |
|
|
UPDATE-триггер получится чуть более сложным, но не настолько, как в
MySQL: здесь мы можем посчитать количество сданных и возвращённых книг на основе информации из псевдотаблиц deleted и inserted, и при этом нам не важно изменение состояния выдач книг — мы лишь считаем (независимо) количество сданных и полученных книг для каждого читателя и изменяем его счётчик книг на эти две величины.
Иными словами, нам не важно, из какого в какое состояние (и от какого к какому читателю) переключается выдача — нас интересует только «у кого удалились активные выдачи», и «кому добавились активные выдачи».
Потому UPDATE-триггер будет просто содержать в себе код из INSERT- триггера и DELETE-триггера. Чуть более элегантным решением была бы реализация
в теле триггера такой логики, при которой было бы достаточно выполнить только одну операцию обновления таблицы subscribers — в этом и будет состоять задание 4.1.2.TSK.C{314}.
Для проверки работоспособности полученного решения можно использовать запросы, представленные после кода UPDATE-триггера (их общая логика и ожидае-
мая реакция СУБД пояснены в решении для MySQL, но обратите внимание, что здесь мы оперируем чуть большим количеством данных в каждом запросе).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 316/545
Пример 32: обеспечение консистентности данных
MS SQL I Решение 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] |
9FROM [subscribers]
10JOIN (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] |
19FROM [subscribers]
20JOIN (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 I Решение 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 Стр: 317/545
Пример 32: обеспечение консистентности данных
MS |
SQL |
Решение 4.1.2.a (проверка работоспособности) (продолжение) |
|
|
||||
36 |
-- Удалим добавленные выдачи: |
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
37 |
|
DELETE FROM [subscriptions] |
|
|
|
|||
|
|
|
|
|
|
|
|
|
38 |
|
WHERE [sb_id] |
IN (200, 201 |
202, 203); |
|
|
||
|
|
|
|
|
|
|
|
|
39 |
|
-- |
|
|
Проверим реакцию на обновление |
выдач |
||
40 |
|
|
|
|||||
книг. |
|
|
|
|
|
|
||
41 |
|
|
|
|
|
|
||
42 |
|
-- |
|
Сначала добавим две выдачи: |
|
|
||
|
INSERT 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', 'Y'); |
|
|
|
|||
60 |
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
61 |
|
-- Не |
меняя |
идентификатор |
читателя |
сделаем |
выдачи |
|
62 |
|
|||||||
|
неактивными: |
|
|
|
|
|
||
63 |
|
|
|
|
|
|
||
|
UPDATE |
[subscriptions] |
|
|
|
|
||
64 |
|
|
|
|
|
|||
|
SET |
[sb_is_active] = |
'N' |
|
|
|
||
65 |
|
|
|
|
||||
66 |
|
WHERE |
[sb_id] IN (300, |
301); |
|
|
|
|
|
|
|
|
|
|
|
|
|
67 |
|
— Не |
меняя |
идентификатор |
читателя |
сделаем |
выдачи |
|
68 |
|
|||||||
|
снова активными: |
|
|
|
|
|
||
69 |
|
|
|
|
|
|
||
|
UPDATE |
[subscriptions] |
|
|
|
|
||
70 |
|
|
|
|
|
|||
|
SET |
[sb_is_active] = |
'Y' |
|
|
|
||
71 |
|
|
|
|
||||
|
WHERE |
[sb_id] IN (300, |
301); |
|
|
|
||
72 |
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
73 |
|
— |
Изменим |
идентификатор |
читателя, не меняя |
состояние |
||
74 |
|
|||||||
активности |
выдач: |
|
|
|
|
|
||
75 |
|
|
|
|
|
|||
76 |
|
UPDATE |
[subscriptions] |
|
|
|
|
|
|
SET |
[sb_subscriber] = 2 |
|
|
|
|||
77 |
|
|
|
|
||||
|
WHERE |
[sb_id] IN (300, |
301); |
|
|
|
||
78 |
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
79 |
|
— |
Изменим |
идентификатор |
читателя |
и сделаем |
выдачи |
|
80 |
|
|||||||
неактивными: |
|
|
|
|
|
|
||
81 |
|
UPDATE |
[subscriptions] |
|
|
|
|
|
82 |
|
|
|
|
|
|||
|
SET |
[sb_subscriber] |
= 1, |
|
|
|
||
83 |
|
|
|
|
||||
|
[sb_is_active] = 'N' |
|
|
|
|
|||
84 |
|
|
|
|
|
|||
|
WHERE |
[sb_id] IN (300, |
301); |
|
|
|
||
85 |
|
|
|
|
||||
|
|
|
|
|
|
|
|
|
86— Изменим идентификатор читателя и сделаем выдачи активными:
87UPDATE [subscriptions]
88SET [sb_subscriber] = 2,
89 |
[sb_is_active] = 'Y' |
|
|
90 |
|
||
WHERE [sb_id] IN (300, |
301); |
||
91 |
|||
|
|
92— Удаление книги с идентификатором 1 (выдана по одному экземпляру
93-- Петрову и обоим Сидоровым):
94DELETE FROM [books]
WHERE [b_id] = 1
SET IDENTITY INSERT [subscriptions] OFF;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 318/545
Пример 32: обеспечение консистентности данных
Переходим к решению поставленной задачи для Oracle. Модифицируем таблицу subscribers и проинициализируем добавленное поле данными.
MS SQL Решение 4.1.2.a (модификация таблицы и инициализация данных)
1-- Модификация таблицы:
2ALTER TABLE "subscribers"
3 |
ADD "s_books" INT DEFAULT 0 NOT NULL); |
4 |
|
5-- Инициализация данных:
6UPDATE "subscribers"
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 |
для |
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 |
10 |
WHERE |
"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 |
23 |
WHERE |
"s id" = old "sb subscriber"; |
24END IF;
25END;
В UPDATE-триггере мы также используем один в один тот же самый код, ко-
торый был использован в решении для MySQL (там же были рассмотрены и показаны графически все ситуации, которые должен учитывать данный триггер).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 319/545