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

Пример 30: модификация данных с использованием триггеров на представлениях

13-- Запрос 4 (нулевое количество совпадений):

14DELETE FROM "subscriptions_with_text"

15WHERE "sb_book" = '2';

16

17-- Запрос 5 (возможно удаление одноимённых, но

18-- при этом разных книг):

19DELETE FROM "subscriptions_with_text"

20WHERE "sb_book" = N'Евгений Онегин';

Решение 3.2.2.b{257}.

Логика выборки данных в этом представлении является упрощённым вариантом решения{76} задачи 2.2.2.b{71}, а сами триггеры в сравнении с предыдущим примером — в разы более простыми.

Т.к. MySQL не позволяет создавать триггеры на представлениях, максимум, что мы можем сделать, это создать само представление, но данные через него модифицировать не получится:

MySQL Решение 3.2.2.b (создание представления)

1CREATE VIEW `books_with_genres`

2AS

3SELECT `b_id`,

4`b_name`,

5GROUP_CONCAT(`g_name`) AS `genres`

6 FROM `books`

7JOIN `m2m_books_genres` USING(`b_id`)

8JOIN `genres` USING(`g_id`)

9GROUP BY `b_id`

ВMS SQL Server код самого представления выглядит следующим образом (см. пояснения относительно логики получения нужного результата в решении{76} задачи 2.2.2.b{71}):

MS SQL Решение 3.2.2.b (создание представления)

1CREATE VIEW [books_with_genres]

2AS

3WITH [prepared_data]

4AS (SELECT [books].[b_id],

12)

13SELECT [outer].[b_id],

14[outer].[b_name],

15STUFF ((SELECT DISTINCT ',' + [inner].[g_name]

23GROUP BY [outer].[b_id],

24[outer].[b_name]

Создадим триггер, позволяющий реализовать операцию вставки данных.

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

Пример 30: модификация данных с использованием триггеров на представлениях

MS SQL Решение 3.2.2.b (создание триггера для реализации операции вставки)

1CREATE TRIGGER [books_with_genres_ins]

2ON [books_with_genres]

3INSTEAD OF INSERT

4AS

5INSERT INTO [genres]

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

Переходим к решению для Oracle. Создадим представление (см. пояснения относительно логики получения нужного результата в решении{76} задачи 2.2.2.b{71}):

Oracle Решение 3.2.2.b (создание представления)

1CREATE VIEW "books_with_genres"

2AS

3SELECT "b_id", "b_name",

4UTL_RAW.CAST_TO_NVARCHAR2

5(

10)

11WITHIN GROUP (ORDER BY "g_name")

12)

13AS "genres"

14FROM "books"

15JOIN "m2m_books_genres" USING ("b_id")

16JOIN "genres" USING ("g_id")

17GROUP BY "b_id",

Создадим триггер, позволяющий реализовать операцию вставки данных.

Oracle Решение 3.2.2.b (создание триггера для реализации операции вставки)

1CREATE OR REPLACE TRIGGER "books_with_genres_ins"

2INSTEAD OF INSERT ON "books_with_genres"

3FOR EACH ROW

4BEGIN

5INSERT INTO "genres"

Как видно из кода триггера, решение для Oracle получилось столь же примитивным в силу причин, описанных выше в решении для MS SQL Server.

Задание 3.2.2.TSK.A: создать представление, извлекающее из таблицы m2m_books_authors человекочитаемую (с названиями книг и именами авторов вместо идентификаторов) информацию, и при этом позволяющее модифицировать данные в таблице m2m_books_authors (в случае неуникальности названий книг и имён авторов в обоих случаях использовать запись с минимальным значением первичного ключа).

Задание 3.2.2.TSK.B: создать представление, показывающее список книг с их авторами, и при этом позволяющее добавлять новых авторов.

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

Пример 31: обновление кэширующих таблиц и полей

Раздел 4: Использование триггеров

4.1. Агрегация данных с использованием триггеров

4.1.1. Пример 31: обновление кэширующих таблиц и полей

Дополнительным материалом к данному примеру служат решения задач, представленных в примерах 27{215}, 29{245} и 30{257}.

Задача 4.1.1.a{272}: модифицировать схему базы данных «Библиотека» таким образом, чтобы таблица subscribers хранила актуальную информацию о дате последнего визита читателя в библиотеку.

Задача 4.1.1.b{281}: создать кэширующую таблицу averages, содержащую в любой момент времени следующую актуальную информацию:

а) сколько в среднем книг находится на руках у читателя; б) за сколько в среднем по времени (в днях) читатель прочитывает книгу; в) сколько в среднем книг прочитал читатель.

Ожидаемый результат 4.1.1.a.

Таблица subscribers содержит дополнительное поле, хранящее актуальную информацию о дате последнего визита читателя в библиотеку:

Ожидаемый результат 4.1.1.b.

Таблица averages содержит следующую актуальную в любой момент времени информацию:

Решение 4.1.1.a{272}.

Для решения этой задачи нужно будет выполнить три шага:

•модифицировать таблицу subscribers (добавив туда поле для хранения даты последнего визита читателя);

•проинициализировать значения последних визитов для всех читателей;

•создать триггеры для поддержания этой информации в актуальном состоянии.

Важно! В MySQL триггеры не активируются каскадными операциями, потому изменения в таблице subscriptions, вызванные удалением книг, останутся «незаметными» для триггеров на этой таблице. В задании 4.1.1.TSK.D{291} вам предлагается доработать данное решение, устранив эту проблему.

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

Пример 31: обновление кэширующих таблиц и полей

Для MySQL первые два шага выполняются с помощью следующих запросов.

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

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

2ALTER TABLE `subscribers`

3ADD COLUMN `s_last_visit` DATE NULL DEFAULT NULL AFTER `s_name`;

4

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

6UPDATE `subscribers`

7LEFT JOIN (SELECT `sb_subscriber`,

Поскольку значение даты последнего визита читателя зависит от информации, представленной в таблице subscriptions (конкретно — от значения поля sb_start), именно на этой таблице нам и придётся создавать триггеры.

В принципе, во всех трёх триггерах можно было использовать однотипное решение (UPDATE на основе JOIN), но для разнообразия в INSERT-триггере мы реализуем более простой вариант: проверим, оказалась ли дата добавляемой выдачи больше, чем сохранённая дата последнего визита и, если это так, обновим дату последнего визита.

В UPDATE- и DELETE-триггерах такое решение не годится: если в результате этих операций окажется, что читатель ни разу не был в библиотеке, значением даты его последнего визита должен стать NULL. Такой результат проще всего достигается с помощью UPDATE на основе JOIN.

Для операций UPDATE и DELETE критически важно использовать именно AF- TER-триггеры, т.к. BEFORE-триггеры будут работать со «старыми» данными, что приведёт к некорректному определению искомой даты.

Также обратите внимание, что в UPDATE-триггере мы должны обновлять дату последнего визита для двух потенциально разных читателей, т.к. при внесении изменения выдача может быть «передана» от одного читателя к другому (мы рассмотрим эту ситуацию в процессе проверки работоспособности полученного решения).

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

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

3DROP TRIGGER `last_visit_on_subscriptions_ins`;

4DROP TRIGGER `last_visit_on_subscriptions_upd`;

5DROP TRIGGER `last_visit_on_subscriptions_del`;

6

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

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

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

10DELIMITER $$

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

Пример 31: обновление кэширующих таблиц и полей

MySQL Решение 4.1.1.a (создание триггеров) (продолжение)

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

12CREATE TRIGGER `last_visit_on_subscriptions_ins`

13AFTER INSERT

14ON `subscriptions`

15FOR EACH ROW

16BEGIN

17IF (SELECT IFNULL(`s_last_visit`, '1970-01-01')

18 FROM `subscribers`

19WHERE `s_id` = NEW.`sb_subscriber`) < NEW.`sb_start`

20THEN

21UPDATE `subscribers`

23WHERE `s_id` = NEW.`sb_subscriber`;

24END IF;

25END;

26$$

27

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

29CREATE TRIGGER `last_visit_on_subscriptions_upd`

30AFTER UPDATE

31ON `subscriptions`

32FOR EACH ROW

33BEGIN

34UPDATE `subscribers`

35LEFT JOIN (SELECT `sb_subscriber`,

41WHERE `s_id` IN (OLD.`sb_subscriber`, NEW.`sb_subscriber`);

42END;

43$$

44

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

46CREATE TRIGGER `last_visit_on_subscriptions_del`

47AFTER DELETE

48ON `subscriptions`

49FOR EACH ROW

50BEGIN

51UPDATE `subscribers`

52LEFT JOIN (SELECT `sb_subscriber`,

58WHERE `s_id` = OLD.`sb_subscriber`;

59END;

60$$

61

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

63DELIMITER ;

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

Для начала добавим выдачу книги читателю с идентификатором 2 (ранее он никогда не был в библиотеке):

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