Материал: Using_MySql,_MS_SQL_Server_and_Oracle

Скачать

Заказать новую работу

Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам

Пример 32: обеспечение консистентности данных

MySQL

Решение 4.1.2.a (модификация таблицы и инициализация данных)

1

-- Модификация таблицы:

2

ALTER TABLE 'subscribers'

3

 

ADD COLUMN 's_books' INT 11 NOT NULL DEFAULT 0 AFTER 's_name'

5-- Инициализация данных:

6UPDATE 'subscribers'

 

 

JOIN (SELECT

'sb_subscriber',

8

 

 

COUNT('sb_id') AS 's_has_books'

9

 

FROM

'subscriptions'

10

 

WHERE

'sb_is_active' = 'Y'

11

 

GROUP BY 'sb_subscriber') AS 'prepared_data'

12

 

ON 's_id' =

'sb_subscriber'

13

SET

's books' = 's has books';

Как видно из инициализирующего запроса, всю необходимую информацию для формирования значения поля s_books мы можем взять из таблицы subscriptions. На ней мы и будем создавать триггеры.

С INSERT- и DELETE-триггерами всё просто: если добавляется или удаляется «активная» выдача (поле sb_is_active равно Y), нужно увеличить или уменьшить на единицу значение счётчика выданных книг у соответствующего читателя.

MySQL I Решение 4.1.2.a (триггеры для таблицы subscriptions) |

1 DELIMITER $$

2

3-- Реакция на добавление выдачи книги:

4CREATE TRIGGER 's_has_books_on_subscriptions_ins'

5AFTER INSERT

6ON 'subscriptions'

7FOR EACH ROW

8BEGIN

9IF (NEW.'sb is active' = 'Y') THEN

10UPDATE 'subscribers'

11

SET

's books' = 's books' + 1

12WHERE 's id' = NEW.'sb subscriber';

13END IF;

14END;

15$$

16

17-- Реакция на удаление выдачи книги:

18CREATE TRIGGER 's_has_books_on_subscriptions_del'

19AFTER DELETE

20ON 'subscriptions'

21FOR EACH ROW

22BEGIN

23IF (OLD.'sb is active' = 'Y') THEN

24UPDATE 'subscribers'

25

SET

's books' = 's books' - 1

26WHERE 's id' = OLD.'sb subscriber';

27END IF;

28END;

29$$

30

31 DELIMITER ;

С UPDATE-триггером ситуация будет более сложной, т.к. у нас есть два пара-

метра, которые могут как измениться, так и остаться неизменными — идентификатор читателя и состояние выдачи.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 310/545

Пример 32: обеспечение консистентности данных

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

Решение 4.1.2.a (триггеры для таблицы subscriptions)

DELIMITER ....................... $$

-- Реакция на обновление выдачи книги:

CREATE TRIGGER 's_has_books_on_subscriptions_upd' AFTER UPDATE

ON 'subscriptions'

 

FOR EACH ROW

 

BEGIN

-- A) Читатель тот же, Y -> N

IF

((OLD.'sb_subscriber' = NEW.'sb_subscriber') AND

 

(OLD.'sb_is_active' = 'Y') AND

 

(NEW.'sb_is_active' = 'N')) THEN

UPDATE 'subscribers'

SET

's_books' = 's_books' - 1

WHERE

's_id' = OLD.'sb_subscriber';

END IF;

 

— B) Читатель тот же, N -> Y

IF

((OLD.'sb_subscriber' = NEW.'sb_subscriber') AND

 

(OLD.'sb_is_active' = 'N') AND

(NEW.'sb_is_active' = 'Y')) THEN

UPDATE 'subscribers'

SET

's_books' = 's_books' + 1

WHERE

's_id' = OLD.'sb_subscriber';

END IF;

 

— C) Читатели разные, Y -> Y

IF ((OLD.'sb_subscriber' ! = NEW.'sb_subscriber') AND

(OLD.'sb_is_active' = 'Y') AND

(NEW.'sb_is_active' = 'Y')) THEN

UPDATE 'subscribers'

SET

's_books' = 's_books' - 1

WHERE

's_id' = OLD.'sb_subscriber';

UPDATE 'subscribers'

SET

's_books' = 's_books' + 1

WHERE

's_id' = NEW.'sb_subscriber';

END IF;

 

— D) Читатели разные, Y -> N

IF ((OLD.'sb_subscriber' != NEW.'sb_subscriber') AND

(OLD.'sb_is_active' = 'Y') AND

(NEW.'sb_is_active' = 'N')) THEN

UPDATE 'subscribers'

SET 's_books' = 's_books' - 1

WHERE 's_id' = OLD.'sb_subscriber';

END IF;

48— E) Читатели разные, N -> Y

49IF ((OLD.'sb_subscriber' != NEW.'sb_subscriber') AND (OLD.'sb_is_active'

50

51

52

53

54

55

56

57

58

59

60

61

 

 

= 'N') AND

(NEW.'sb_is_active' = 'Y')) THEN

UPDATE 'subscribers'

SET

's_books' =

's_books' + 1

WHERE

's_id' = NEW

'sb_subscriber';

END IF;

 

 

END;

$$

DELIMITER ;

Изобразим рассмотренные ситуации графически.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 311/545

Пример 32: обеспечение консистентности данных

 

Старое значение

Новое значение

Действие

Код действия

 

sb_is_active

sb_is_active

 

 

 

 

 

 

 

Идентификатор

Y

Y

-

 

Y

N

OLD-1

A

читателя остался

N

Y

OLD+1

B

неизменным

N

N

-

 

 

 

Идентификатор

Y

Y

OLD-1, NEW+1

C

Y

N

OLD-1

D

читателя изме-

N

Y

NEW+1

E

нился

N

N

-

 

 

 

И, наконец, здесь мы приведём решение проблемы, описанной в заданиях

3.2.1. TSK.D<24°}, 4.1.1.TSK.D<291\ 4.1.1.TSK.E<291J. Напомним, что MySQL не активирует триггеры каскадными операциями, потому удаление книг (которое приведёт к удалению всех записей о выдачах этих книг) активирует «незаметное» для DELETE - триггера на таблице subscriptions удаление данных.

Чтобы учесть этот эффект, мы создадим дополнительный триггер на таблице books, реагирующий на удаление книг (вставка или обновление данных в таблице books не влияет на распределение уже имеющихся выдач книг по тем или иным читателям, потому здесь достаточно создать только DELETE-триггер).

Обратите внимание: здесь мы создаём BEFORE-триггер, т.к. в момент активации AFTER-триггера искомая информация в таблице subscriptions уже будет удалена.

MySQL і Решение 4.1.2.a (триггер для таблицы books)

1 DELIMITER $$

2

3-- Реакция на удаление книги:

4CREATE TRIGGER 's has books on books del'

5BEFORE DELETE

6ON 'books'

7FOR EACH ROW

8BEGIN

9UPDATE 'subscribers'

10

JOIN (SELECT

'sb subscriber',

11

 

COUNT('sb book') AS 'delta'

12

FROM

'subscriptions'

13

WHERE

'sb book' = OLD 'b id'

14

AND 'sb is active' = 'Y'

15GROUP BY 'sb subscriber') AS 'prepared data'

16ON 's id' = 'sb subscriber'

17

SET

's books' = 's books' - 'delta';

18

END;

 

19

$$

 

20

 

 

21

DELIMITER ;

Проверим работоспособность полученного решения. Будем изменять данные в таблицах books и subscriptions и отслеживать изменения данных в таблице subscribers.

Исходное состояние таблицы subscribers таково:

s_id

s_name

s_books

1

Иванов И.И.

0

2

Петров П.П.

0

3

Сидоров С.С.

3

4

Сидоров С.С.

2

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 312/545

Пример 32: обеспечение консистентности данных

Проверим реакцию на добавление и удаление выдач книг. Добавим Иванову И.И. активную выдачу, а Петрову П.П. неактивную:

 

MySQL

 

і

Решение 4.1.2.a (проверка работоспособности)

|

 

 

INSERT INTO 'subscriptions'

 

 

2

VALUES

(200

 

 

 

 

3

 

 

 

1,

 

 

 

 

4

 

 

 

1,

 

 

 

 

5

 

 

 

'2011-01-12',

 

 

 

6

 

 

 

'2011-02-12',

 

 

 

7

 

 

 

'Y'),

 

 

8

 

 

 

(201

 

 

 

 

9

 

 

 

2,

 

 

 

 

10

 

 

 

1,

 

 

 

 

11

 

 

 

'2011-01-12',

 

 

 

12

 

 

 

'2011-02-12',

 

 

 

13

 

 

 

'N')

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

s_id

 

 

s_name

s_books

 

 

 

1

 

Иванов И.И.

1

 

 

 

2

 

Петров П.П.

0

 

 

 

3

 

Сидоров С.С.

3

 

 

 

4

 

Сидоров С.С.

2

 

 

 

 

Удалим добавленные выдачи:

 

 

 

 

 

 

 

 

MySQL

 

і

Решение 4.1.2.a (проверка работоспособности)

і

1DELETE FROM 'subscriptions'

2WHERE 'sb id' IN ( 200 201 )

s_id

s_name

s_books

1

Иванов И.И.

0

2

Петров П.П.

0

3

Сидоров С.С.

3

4

Сидоров С.С.

2

Проверим реакцию на обновление выдач книг. Сначала добавим выдачу:

MySQL

 

і Решение 4.1.2.a (проверка работоспособности) |

 

INSERT INTO 'subscriptions'

2

VALUES

(300

 

 

3

 

 

1,

 

 

4

 

 

1,

 

 

5

 

 

'2011-01-12',

 

6

 

 

'2011-02-12',

 

7

 

 

'Y')

 

 

 

 

 

 

 

 

 

 

s_id

 

s_name

s_books

 

1

 

Иванов И.И.

1

 

2

 

Петров П.П.

0

 

3

 

Сидоров С.С.

3

 

4

 

Сидоров С.С.

2

 

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 313/545

Пример 32: обеспечение консистентности данных

Не меняя идентификатор читателя сделаем выдачу неактивной:

MySQL I Решение 4.1.2.a (проверка работоспособности)

1— A

2UPDATE 'subscriptions'

3

SET

'sb is active' = 'N'

4

WHERE

'sb id' = 300

 

 

 

 

 

 

 

 

s_id

s_name

s_books

 

1

Иванов И.И.

0

 

2

Петров П.П.

0

 

3

Сидоров С.С.

3

 

4

Сидоров С.С.

2

 

Не меняя идентификатор читателя сделаем выдачу снова активной:

MySQL Решение 4.1.2.a (проверка работоспособности)

1— B

2UPDATE 'subscriptions'

3

SET

'sb is active' = 'Y'

4

WHERE

'sb id' = 300

 

 

 

 

 

 

 

 

 

s_id

s_name

s_books

 

1

Иванов И.И.

1

 

2

Петров П.П.

0

 

3

Сидоров С.С.

3

 

4

Сидоров С.С.

2

 

Изменим идентификатор читателя, не меняя состояние активности выдачи:

Решение 4.1.2.a

1— C

2UPDATE 'subscriptions'

3

SET

'sb_subscriber' = 2

 

4

WHERE

'sb id' = 300

 

 

 

 

 

 

s_id

 

s_name

s_books

 

1

 

Иванов И.И.

0

 

2

 

Петров П.П.

1

 

3

 

Сидоров С.С.

3

 

4

 

Сидоров С.С.

2

 

Изменим идентификатор читателя и сделаем выдачу неактивной:

MySQL Решение 4.1.2.a (проверка работоспособности)

1— D

2UPDATE 'subscriptions'

3

SET

'sb subscriber' = 1

4'sb is active' = N'

5WHERE 'sb id' = 300

s_id

s_name

s_books

1

Иванов И.И.

0

2

Петров П.П.

0

3

Сидоров С.С.

3

4

Сидоров С.С.

2

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 314/545

Источник: https://studfile.net/preview/16420333/

Смотрите также:

_-___-~1
_3 тема - Диффузия
_Роджерс Карл, Брак и его альтернативы
'Заповит' Рейнгольда Глиэра: стилистические особенности
'Создание и продвижение интернет - платформы реферальной системы' на примере компании Бартер–кард
[БИЛЕТЫ] IMK №3
[ТЕСТЫ] ИМК №1 по физиологии (база 1 семестр и на экз)
[ТЕСТЫ] Спланхнология
[ТЕСТЫ] ЦНС - ЛУЧШИЕ 2020
+ Проблемы криминологии. Курс лекций