Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 32: обеспечение консистентности данных |
|
||||
Oracl |
і |
Решение 4.1.2.b (проверка работоспособности) |
| |
||
e |
|||||
|
|
|
|
||
9 |
-- Изменение в добавленных связях значения идентификаторов книг, |
||||
10 |
-- без изменения значения идентификаторов жанров: |
||||
11 |
UPDATE "m2m books genres" |
|
|||
12 |
SET |
|
"b id" = 3 |
|
|
13 |
WHERE |
"b id" = 1 |
|
||
14 |
|
AND "g_id" = 4; |
|
||
15 |
|
|
|
|
|
16 |
UPDATE "m2m books genres" |
|
|||
17 |
SET |
|
"b id" = 4 |
|
|
18WHERE "b id" = 2
19AND "g_id" = 4;
21-- Изменение в добавленных связях значения идентификаторов жанров,
22-- без изменения значения идентификаторов книг:
23UPDATE "m2m books genres"
24 |
SET |
"g id" = 5 |
25WHERE "b id" = 3
26AND "g_id" = 4;
28 |
UPDATE "m2m books |
genres" |
|
29 |
SET |
"g id" = 5 |
|
30WHERE "b id" = 4
31AND "g_id" = 4;
33-- Изменение в добавленных связях значения идентификаторов жанров,
34-- и идентификаторов книг одновременно:
35UPDATE "m2m books genres"
36 |
SET |
"b id" = 1, |
37"g id" = 4
38WHERE "b id" = 3
39AND "g_id" = 5;
41 |
UPDATE "m2m books genres" |
|
42 |
SET |
"b id" = 2, |
43"g id" = 4
44WHERE "b id" = 4
45AND "g_id" = 5;
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 Стр: 330/545
Пример 32: обеспечение консистентности данных
{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.A: доработать триггеры из решений ,
сколько раз читатель брал в библиотеке книги (этот счётчик должен инкрементироваться каждый раз, когда читателю выдаётся книга; уменьшение значения этого счётчика не предусмотрено).
& Задание 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 Стр: 331/545
Пример 33: контроль операций модификации данных
4.2.Контроль операций с данными с использованием триггеров
4.2.1. Пример 33: контроль операций модификации данных
'. Задача 4.2.1.a{315}: создать триггер, не позволяющий добавить в базу данных информацию о выдаче книги, если выполняется хотя бы одно из условий:
•дата выдачи находится в будущем;
•дата возврата находится в прошлом (только для вставки данных);
•дата возврата меньше даты выдачи.
;Задача 4.2.1.b{328}: создать триггер, не позволяющий выдать книгу читателю,
укоторого на руках находится десять и более книг.
'; Задача 4.2.1.c335: создать триггер, не позволяющий изменять значение поля sb_is_active таблицы subscriptions со значения N на значение Y.
Ожидаемый результат 4.2.1.a.
При попытке внести в базу данных изменения, противоречащие условию задачи, операция (транзакция) должна быть отменена. Также должно быть выведено сообщение об ошибке, наглядно поясняющее суть проблемы, например:
• |
“Date 2038.01.12 for subscription 145 |
activation isin the future”. |
• |
“Date 1983.01.12 for subscription 155 |
deactivationis in the past”. |
• |
“Date 2000.01.12 for subscription 165 |
deactivationis less than date for its activa |
|
tion (2015.01.12)”. |
|
|
Ожидаемый результат 4.2.1.b. |
|
При попытке внести в базу данных изменения, противоречащие условию задачи, операция (транзакция) должна быть отменена. Также должно быть выведено сообщение об ошибке, наглядно поясняющее суть проблемы, например: “Subscriber
Иванов И.И. (id = 1) already has 23 books out of 10 allowed.”
Ожидаемый результат 4.2.1.c.
При попытке внести в базу данных изменения, противоречащие условию задачи, операция (транзакция) должна быть отменена. Также должно быть выведено сообщение об ошибке, наглядно поясняющее суть проблемы, например: “It is prohibited to activate previously deactivated subscriptions (rule violated for subscriptions 34,
89, 12).”
xA?
Решение 4.2.1 .a{315}.
Для решения данной задачи нам понадобятся только INSERT- и UPDATE -
триггеры, т.к. в процессе удаления данных невозможно нарушить ни одно из контролируемых условий.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 332/545
Пример 33: контроль операций модификации данных
Код iNSERT-триггера для MySQL выглядит следующим образом.
|
MySQL і Решение 4.2.1.a (триггеры для таблицы subscriptions) |
[ |
I |
DELIMITER. $$ .............................. |
‘ .............................. |
2 |
|
|
CREATE TRIGGER 'subscriptions_control_ins'
4AFTER INSERT
5ON 'subscriptions'
6FOR EACH ROW
7BEGIN 8
9-- Блокировка выдач книг с датой выдачи в будущем.
10IF NEW.'sb_start' > CURDATE()
II |
THEN |
12 |
SET @msg = CONCAT('Date ', NEW.'sb_start', ' for subscription ', |
13 |
NEW.'sb_id', ' activation is in the future.'); |
14SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @msg MYSQL_ERRNO = 1001
15END IF; 16
17-- Блокировка выдач книг с датой возврата в прошлом.
18IF NEW.'sb_finish' < CURDATE()
19THEN
20SET @msg = CONCAT('Date ', NEW.'sb_finish', ' for subscription ',
21 |
NEW.'sb_id', ' deactivation is in the past.'); |
22SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = @msg MYSQL_ERRNO = 1002
23END IF;
24
25-- Блокировка выдач книг с датой возврата меньшей, чем дата выдачи.
26IF NEW.'sb_finish' < NEW.'sb_start'
27THEN
28SET @msg = CONCAT('Date ', NEW.'sb_finish', ' for subscription ',
29 |
NEW.'sb_id', |
30 |
' deactivation is less than the date for its activation (', |
31 |
NEW.'sb_start', ').'); |
32SIGNAL SQLSTATE '45003' SET MESSAGE_TEXT = @msg MYSQL_ERRNO = 1003
33END IF; 34
35END;
36$$ 37
38DELIMITER ;
Сточки зрения функциональности в данном случае можно было бы использовать и BEFORE-триггер, но в случае с AFTER-триггером сообщение об ошибке по-
лучается более информативным, т.к. уже содержит в себе корректное значение автоинкрементируемого первичного ключа (в BEFORE-триггере это значение не опре-
делено, и потому в сообщении об ошибке превращается в 0).
Код в строках 25-33 в настоящий момент не нужен — две предшествующих проверки не допустят возникновения проверяемой этим кодом ситуации. Но если в будущем эти проверки будут модифицированы или убраны, код в строках 25-33 будет срабатывать.
Поскольку в MySQL триггер не может явно отменить транзакцию, мы порождаем исключительную ситуацию (строки 14, 22, 23), при возникновении которой отменяется транзакция, активировавшая срабатывание триггера.
Код UPDATE-триггера будет даже чуть более простым, т.к. в нём по условию
задачи нет необходимости проверять, находится ли дата возврата в прошлом. При этом код в строках 17-25 (ранее отмеченный как бесполезный для IN-
SERT-триггера) здесь будет срабатывать, т.к. по условию задачи при выполнении операции обновления допускается установка в поле sb_finish даты из прошлого, что позволяет нарушить третье условие задачи.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 333/545
Пример 33: контроль операций модификации данных
MySQL I |
Решение 4.2.1.a (триггеры для таблицы subscriptions) | |
1 DELIMITER $$
2
3CREATE TRIGGER 'subscriptions_control_upd'
4AFTER UPDATE
5ON 'subscriptions'
6FOR EACH ROW
7BEGIN
8
9-- Блокировка выдач книг с датой выдачи в будущем.
10IF NEW.'sb start' > CURDATE()
11THEN
12SET @msg = CONCAT('Date ', NEW.'sb start', ' for subscription ',
13 |
NEW.'sb id', ' activation is in the future.'); |
14SIGNAL SQLSTATE '45001' SET MESSAGE TEXT = @msg, MYSQL ERRNO = 1001;
15END IF;
16
17-- Блокировка выдач книг с датой возврата меньшей, чем дата выдачи.
18IF NEW.'sb finish' < NEW.'sb start'
19THEN
20SET @msg = CONCAT('Date ', NEW.'sb finish', ' for subscription ',
21 |
NEW. 'sb id', |
22 |
' deactivation is less than the date for its activation (', |
23 |
NEW.'sb start', ').'); |
24SIGNAL SQLSTATE '45003' SET MESSAGE TEXT = @msg, MYSQL ERRNO = 1003;
25END IF;
26
27END;
28$$
29
30 DELIMITER ;
Проверим работоспособность полученного решения. Будем выполнять следующие запросы и следить за реакцией СУБД.
Попытаемся добавить выдачу книги с датой активации в будущем:
|
MySQL |
Решение 4.2.1 .а (проверка |
|
|
|
|
|
1 |
|
работоспособности) |
|
INSERT INTO |
|
||
2 |
VALUES |
(500, |
|
3 |
|
|
1, |
4 |
|
|
1, |
5 |
|
|
'2020-01-12', |
6 |
|
|
'2020-02-12', |
7 |
|
|
'N') |
|
|
|
|
СУБД запретит эту операцию, вернув следующее сообщение об ошибке:
Error Code: 1001. Date 2020-01-12 for subscription 500 activation is in the future.
Попытаемся добавить выдачу книги с датой активации в будущем, при этом не указав значение первичного ключа:
MySQL I |
’ешение 4.2.1 .a (проверка работоспособности) |
і |
|
1 |
INSERT |
INTO 'subscriptions' |
|
2 |
|
('sb id', |
|
3 |
|
'sb subscriber', |
|
4 |
|
'sb book' |
|
5 |
|
'sb start', |
|
6 |
|
'sb finish', |
|
7 |
|
'sb is active') |
|
8 |
VALUES |
(NULL, |
|
9 |
|
3, |
|
10 |
|
3, |
|
11 |
|
'2020-01-12', |
|
12 |
|
'2020-02-12', |
|
13 |
|
'N'); |
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 334/545