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

Скачать

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

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

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

 

Oracl

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

|

e

 

 

 

 

 

1

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

 

2

CREATE OR REPLACE TRIGGER "s has books on sbps upd"

3

AFTER UPDATE

 

 

 

4

ON "subscriptions"

 

 

 

5

FOR EACH ROW

 

 

 

6

BEGIN

 

 

 

 

7

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

 

8

IF (( old "sb subscriber" =

new "sb subscriber") AND

9

( old "sb is active" = 'Y') AND

 

10

( new "sb is active" = 'N')) THEN

 

11

UPDATE "subscribers"

 

 

12

SET

"s books" = "s books" - 1

 

13

WHERE

"s id" =

old "sb subscriber";

14

END IF;

 

 

 

 

15

 

 

 

 

 

16

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

 

17

IF (( old "sb subscriber" =

new "sb subscriber") AND

18

( old "sb is active" = 'N') AND

 

19

( new "sb is active" = 'Y')) THEN

 

20

UPDATE "subscribers"

 

 

21

SET

"s books" = "s books" + 1

 

22

WHERE

"s id" =

old "sb subscriber";

23

END 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

31

WHERE

"s id" =

old "sb subscriber";

32

UPDATE "subscribers"

33

SET

"s books" = "s books" + 1

34

WHERE

"s id" =

new "sb subscriber";

35

END 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

43

WHERE

"s id" = old "sb subscriber";

44

END 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

52

WHERE

"s id" = new "sb subscriber";

53END IF;

54END;

Витоге код триггеров для Oracle получился полностью идентичным коду триггеров для MySQL, потому и запросы для проверки работоспособности полученного решения также совпадают для обеих СУБД.

См. код самих запросов ниже, а логика их работы с пояснением и демонстрацией изменения содержимого таблицы subscribers представлена в решении для

MySQL.

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

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

 

Oracl

і

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

|

e

 

 

 

 

 

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"

21

WHERE "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 Стр: 321/545

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

Решение 4.1.2. b{292}.

Как и в решении{292} задачи 4.1.2.a{292} здесь нужно будет выполнить те же самые действия — модифицировать таблицу, проинициализировать данные, создать триггеры. И даже код триггеров будет чем-то похож на рассмотренные ранее решения.

Традиционно начинаем с решения для MySQL: модифицируем таблицу и проинициализируем данные.

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

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

2ALTER TABLE 'genres'

3ADD COLUMN 'g_books' INT(11) NOT NULL DEFAULT 0 AFTER 'g_name';

4

5

6

7

8

9

10

11

12

-- Инициализация данных: UPDATE 'genres' JOIN (SELECT

FROM

'g_id',

GROUP

COUNT(' b_id') AS

 

USING

'g_has_books'

('g_id') SET'g

'm2m_books_genres'

books'

= 'g has

 

books';

Код всех трёх триггеров будет предельно прост: в INSERT-триггере мы увеличиваем счётчик книг у соответствующего жанра, в DELETE-триггере — уменьшаем, в UPDATE-триггере уменьшаем «старому» жанру и увеличиваем «новому» жанру. Никаких дополнительных проверок и ухищрений здесь не требуется.

MySQL I

Решение 4.1.2.b (триггеры для таблицы m2m books genres)

|

1

DELIMITER $$

 

2

 

 

 

 

3

-- Реакция на добавление связи между книгами и жанрами:

4

CREATE TRIGGER 'g_has_books_on m2m_b_g ins'

 

5

AFTER INSERT

 

6

ON 'm2m_books_genres'

 

7

 

FOR EACH ROW

 

8

 

BEGIN

 

 

9

 

UPDATE 'genres'

 

10

 

SET

'g books' = 'g books ' + 1

 

11

 

WHERE

'g id' = NEW. 'g id';

 

12END;

13$$

14 15 -- Реакция на обновление связи между книгами и жанрами:

16CREATE TRIGGER 'g_has_books_on m2m_b_g upd'

17AFTER UPDATE

18ON 'm2m_books_genres'

19FOR EACH ROW

20BEGIN

21UPDATE 'genres'

22

SET

'g books' = 'g books ' - 1

23WHERE 'g id' = OLD.'g id';

24UPDATE 'genres'

25

SET

'g books' = 'g books ' + 1

26WHERE 'g id' = NEW.'g id';

27END;

28$$

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

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

MySQL I

Решение 4.1.2.b (триггеры для таблицы m2m books genres) (продолжение)

|

29-- Реакция на удаление связи между книгами и жанрами:

30CREATE TRIGGER 'g_has_books_on_m2m_b_g_del'

31AFTER DELETE

32ON 'm2m_books_genres'

33FOR EACH ROW

34BEGIN

35UPDATE 'genres'

36

SET

'g books' = 'g books' - 1

37WHERE 'g id' = OLD.'g id';

38END;

39$$

40

41 DELIMITER ;

Поскольку в MySQL триггеры не активируются каскадными операциями, удаление книги (которое приведёт к удалению всех её связей со всеми жанрами) останется незаметным для триггеров на таблице m2m_books_genres. Потому мы должны создать триггер на таблице books, учитывающий соответствующую ситуацию.

Каждая книга связана с каждым жанром не более одного раза, потому при удалении любой книги нужно на единицу уменьшить счётчик книг у каждого из жанров, с которыми она связана.

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

1 DELIMITER $$

2

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

4CREATE TRIGGER 'g has books on books del'

5BEFORE DELETE

6ON 'books'

7FOR EACH ROW

8BEGIN

9UPDATE 'genres'

10

SET

'g books' = 'g books' - 1

11

WHERE

'g id' IN (SELECT 'g id'

12

 

FROM

'm2m books genres'

13

 

WHERE

'b id' = OLD.'b id');

14END;

15$$

16

17 DELIMITER ;

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

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

g_id

g_name

g books

1

Поэзия

2

2

Программирование

3

3

Психология

1

4

Наука

0

5

Классика

4

6

Фантастика

1

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

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

Добавим две связи к жанру «Наука» (идентификатор жанра равен 4):

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

 

1

INSERT INTO 'm2m books genres'

 

2

 

('b id',

 

 

 

3

 

'g id')

 

 

 

4

VALUES

(1, 4),

 

 

 

5

 

(2, 4)

 

 

 

 

 

 

 

 

 

 

 

 

 

g_id

g_name

g_books

 

 

1

Поэзия

 

2

 

 

2

Программирование

3

 

 

3

Психология

 

1

 

 

4

Наука

 

2

 

 

5

Классика

 

4

 

 

6

Фантастика

 

1

 

Изменим в этих связях значения идентификаторов книг, не меняя значения идентификаторов жанров:

 

MySQL I

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

1

UPDATE 'm2m books

genres'

2

SET

'b id' = 3

 

3WHERE 'b id' = 1

4AND 'g id' = 4;

6

UPDATE 'm2m books genres'

7

SET

'b id' = 4

 

 

8

WHERE

'b id' = 2

 

 

9

AND 'g id' = 4;

 

 

 

 

 

 

 

 

 

 

 

 

g_id

 

g_name

g_books

 

1

Поэзия

2

 

2

Программирование

3

 

3

Психология

1

 

4

Наука

 

2

 

5

Классика

4

 

6

Фантастика

1

 

Изменим в этих связях значения идентификаторов жанров, не меняя значения идентификаторов книг:

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

 

1

UPDATE

'm2m books genres'

 

2

SET

'g id' =

5

 

 

 

3

WHERE

'b id' =

3

 

 

 

4

AND 'g id' =

4;

 

 

 

5

 

 

 

 

 

 

6

UPDATE 'm2m books genres'

 

7

SET

'g id' =

5

 

 

 

8

WHERE

'b id' =

4

 

 

 

9

AND 'g id' =

4;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

g id

 

g_name

 

g books

 

 

1

Поэзия

 

2

 

 

2

Программирование

3

 

 

3

Психология

 

1

 

 

4

Наука

 

 

0

 

 

5

Классика

 

6

 

 

6

Фантастика

 

1

 

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

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

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

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