Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

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

38-- Создание триггера, реагирующего на

39-- изменение данных о книгах:

40CREATE TRIGGER `upd_sbs_rdy_on_books_upd`

41AFTER UPDATE

42ON `books`

43FOR EACH ROW

44BEGIN

45IF (OLD.`b_name` != NEW.`b_name`)

46THEN

47DELETE FROM `subscriptions_ready`;

48INSERT INTO `subscriptions_ready`

66END IF;

67END;

68$$

69

70-- Восстановление разделителя завершения запросов:

71DELIMITER ;

Вся логика проверки работы таких триггеров подробно показана в решении{216} задачи 3.1.2.a{215}. Вы можете самостоятельно провести эксперимент, изменяя произвольным образом данные в таблице books и отслеживая соответствующие изменения в таблице subscriptions_ready.

Триггеры на таблице subscribers отличаются от триггеров на таблице books только своими именами, названиями своих таблиц и именем поля, изменение значения которого проверяется для определения необходимости обновления закэшированных данных (в коде выше проверяется поле b_name, в коде ниже — s_name).

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

1-- Удаление старых версий триггеров

2-- (удобно в процессе разработки и отладки):

3DROP TRIGGER `upd_sbs_rdy_on_subscribers_del`;

4DROP TRIGGER `upd_sbs_rdy_on_subscribers_upd`;

5

6-- Переключение разделителя завершения запроса,

7-- т.к. сейчас запросом будет создание триггера,

8-- внутри которого есть свои, классические запросы:

9DELIMITER $$

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

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

MySQL Решение 3.1.2.b (триггеры для таблицы subscribers) (продолжение)

10-- Создание триггера, реагирующего на удаление книг:

11CREATE TRIGGER `upd_sbs_rdy_on_subscribers_del`

12AFTER DELETE

13ON `subscribers`

14FOR EACH ROW

15BEGIN

16DELETE FROM `subscriptions_ready`;

17INSERT INTO `subscriptions_ready`

35END;

36$$

37

38-- Создание триггера, реагирующего на

39-- изменение данных о книгах:

40CREATE TRIGGER `upd_sbs_rdy_on_subscribers_upd`

41AFTER UPDATE

42ON `subscribers`

43FOR EACH ROW

44BEGIN

45IF (OLD.`s_name` != NEW.`s_name`)

46THEN

47DELETE FROM `subscriptions_ready`;

48INSERT INTO `subscriptions_ready`

66END IF;

67END;

68$$

69

70-- Восстановление разделителя завершения запросов:

71DELIMITER ;

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

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

На таблице subscriptions придётся создавать все три триггера (INSERT, UPDATE и DELETE), т.к. каждая из этих операций может повлиять на содержимое кэширующей таблицы subscriptions_ready. И код всех этих трёх триггеров будет сильно различаться.

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

1-- Удаление старых версий триггеров

2-- (удобно в процессе разработки и отладки):

3DROP TRIGGER `upd_sbs_rdy_on_subscriptions_ins`;

4DROP TRIGGER `upd_sbs_rdy_on_subscriptions_del`;

5DROP TRIGGER `upd_sbs_rdy_on_subscriptions_upd`;

6

7-- Переключение разделителя завершения запроса,

8-- т.к. сейчас запросом будет создание триггера,

9-- внутри которого есть свои, классические запросы:

10DELIMITER $$

11

12-- Создание триггера, реагирующего на добавление выдачи книг:

13CREATE TRIGGER `upd_sbs_rdy_on_subscriptions_ins`

14AFTER INSERT

15ON `subscriptions`

16FOR EACH ROW

17BEGIN

18INSERT INTO `subscriptions_ready`

38END;

39$$

40

41-- Создание триггера, реагирующего на удаление выдачи книг:

42CREATE TRIGGER `upd_sbs_rdy_on_subscriptions_del`

43AFTER DELETE

44ON `subscriptions`

45FOR EACH ROW

46BEGIN

47DELETE FROM `subscriptions_ready`

48WHERE `subscriptions_ready`.`sb_id` = OLD.`sb_id`;

49END;

50$$

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

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

MySQL Решение 3.1.2.b (триггеры для таблицы subscriptions) (продолжение)

51-- Создание триггера, реагирующего на обновление выдачи книг:

52CREATE TRIGGER `upd_sbs_rdy_on_subscriptions_upd`

53AFTER UPDATE

54ON `subscriptions`

55FOR EACH ROW

56BEGIN

57UPDATE `subscriptions_ready`

58JOIN (SELECT `sb_id`,

70`subscriptions_ready`.`sb_subscriber` = `new_data`.`s_name`,

71`subscriptions_ready`.`sb_book` = `new_data`.`b_name`,

72`subscriptions_ready`.`sb_start` = NEW.`sb_start`,

73`subscriptions_ready`.`sb_finish` = NEW.`sb_finish`,

74`subscriptions_ready`.`sb_is_active` = NEW.`sb_is_active`

75WHERE `subscriptions_ready`.`sb_id` = OLD.`sb_id`;

76END;

77$$

78

79-- Восстановление разделителя завершения запросов:

80DELIMITER ;

Код INSERT-триггера (строки 12-39) очень похож на код аналогичного триггера на таблице books. Отличие состоит в том, что в данном случае мы знаем идентификаторы читателя и книги, и это избавляет нас от необходимости формировать полную выборку.

Код DELETE-триггера (строки 41-50) получаемся самым компактным: нам нужно просто удалить из таблицы subscriptions_ready записи, идентификаторы которых совпадают с идентификаторами записей, удаляемых из таблицы subscriptions.

Код UPDATE-триггера (строки 51-77) — самый нетривиальный. Сам по себе синтаксис обновления на основе выборки выглядит непривычно (мы вынуждены объединять результаты выборки из обновляемой таблицы и выборки-источника — строки 57-68).

Условия в строках 66-67 позволяют сократить количество выбираемых рядов, а условие в строке 68 гарантирует, что мы получим данные о новой записи таблицы subscriptions, даже если у неё изменился первичный ключ. Следуя этой же логике, мы не указываем условие объединения `subscriptions_ready` JOIN ... `new_data`, т.к. единственным здравым условием объединения здесь может быть совпадение значений sb_id, но если первичный ключ записи в таблице subscriptions поменялся, то такого совпадения не будет, т.к. в таблице subscriptions_ready всё ещё хранится старое значение первичного ключа обновляемой записи.

В строках 69-74 новые данные для обновления полей мы берём из двух источников: из явно переданных данных через ключевое слово NEW (все значения, которые мы можем получить напрямую) и из результатов выборки new_data (имя читателя и название книги, т.к. их нет и не может быть в явно переданных данных, доступных через ключевое слово NEW).

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

Пример 27: выборка данных с использованием кэширующих представлений и таблиц

Условие в строке 75 гарантирует, что мы обновим нужную запись: здесь необходимо сравнение идентификатора обновляемой записи именно с OLD.`sb_id`, а не с NEW.`sb_id`, т.к. у обновляемой записи в таблице subscriptions мог поменяться первичный ключ, и нам нужно найти его старое значение в таблице subscriptions_ready (благодаря выражению в строке 69 это значение тоже обновится, если оно изменилось).

Вся логика проверки работы таких триггеров подробно показана в решении{216} задачи 3.1.2.a{215}. Вы можете самостоятельно провести эксперимент, изменяя произвольным образом данные в таблице books, subscribers и subscriptions и отслеживая соответствующие изменения в таблице subscriptions_ready.

По сравнению с решением для MySQL, решения для MS SQL Server и Oracle предельно просты. В их основе лежит обычный запрос на выборку, который можно выполнить и сам по себе.

MS SQL Решение 3.1.2.b

1-- Удаление старой версии индексированного представления

2-- (удобно при разработке и отладке):

3DROP VIEW [subscriptions_ready];

4

5-- Создание представления:

6CREATE VIEW [subscriptions_ready]

7WITH SCHEMABINDING

8AS

9SELECT [sb_id],

10[s_name] AS [sb_subscriber],

11[b_name] AS [sb_book],

12[sb_start],

13[sb_finish],

14[sb_is_active]

15 FROM [dbo].[books]

16JOIN [dbo].[subscriptions]

17ON [b_id] = [sb_book]

18JOIN [dbo].[subscribers]

19ON [sb_subscriber] = [s_id];

20

21-- Создание уникального кластерного индекса на представлении.

22-- Именно эта операция "включает" автоматическое обновление

23-- представления при изменении данных в таблицах,

24-- на которых оно построено:

25CREATE UNIQUE CLUSTERED INDEX [idx_subscriptions_ready]

26ON [subscriptions_ready] ([sb_id]);

Чтобы данные в представлении subscriptions_ready обновлялись автоматически, его нужно сделать индексированным, т.е. создать на нём уникальный кластерный индекс (строки 25-26).

Необходимым условием создания такого индекса на представлении является привязка представления к схеме базы данных (строка 7), указывающая СУБД на необходимость установить и отслеживать соответствие между использованием в коде представления объектов базы данных и реальным состоянием таких объектов (их существованием, доступностью и т.д.) не только в момент создания представления, но и в момент любой модификации объектов базы данных, на которые ссылается представление.

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