Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 30: модификация данных с использованием триггеров на представлениях
Oracle |
Решение 3.2.2.a (проверка работоспособности операции удаления) (продолжение) |
|
|
||
13 |
-- Запрос 4 (нулевое количество совпадений): |
|
14 |
DELETE FROM "subscriptions_with_text" |
|
15 |
WHERE "sb_book" = '2'; |
|
16 |
|
|
17-- Запрос 5 (возможно удаление одноимённых, но
18-- при этом разных книг):
19DELETE FROM "subscriptions_with_text"
20WHERE "sb book" = Ы'Евгений Онегин';
'IT Решение 3.2.2. b{257}.
Логика выборки данных в этом представлении является упрощённым вариантом решения{76} задачи 2.2.2. b{71}, а сами триггеры в сравнении с предыдущим примером — в разы более простыми.
Т.к. MySQL не позволяет создавать триггеры на представлениях, максимум, что мы можем сделать, это создать само представление, но данные через него модифицировать не получится:
MySQL і Решение 3.2.2.b (создание представления)
1 |
CREATE VIEW 'books with genres' |
|
2 |
AS |
|
3 |
SELECT 'b id' , |
|
4 |
|
'b name', |
5 |
|
GROUP CONCAT('g name') AS 'genres' |
6 |
FROM |
'books' |
7 |
|
JOIN 'm2m books genres' USING('b id') |
8 |
|
JOIN 'genres' USING('g id') |
9 |
GROUP |
BY 'b_id' |
В MS SQL Server код самого представления выглядит следующим образом (см. пояснения относительно логики получения нужного результата в решении{76} задачи 2.2.2. b{71}):
MS SQL Решение 3.2.2.b (создание представления)
CREATE VIEW [books_with_genres]
2 |
AS |
|
3 |
WITH [prepared_data] |
|
4 |
AS (SELECT [books] [b_id], |
|
5 |
|
[b_name], |
6 |
|
[g_name] |
|
FROM |
[books] |
8 |
|
JOIN [m2m_books_genres] |
9 |
|
ON [books] [b_id] = [m2m_books_genres] [b_id] |
10 |
|
|
JOIN [genres] |
|
|
11 |
|
|
ON |
[m2m_books_genres] |
[g_id] |
= [genres] [g_id] |
|
|
|
||
12 |
|
) |
|
|
|
13 |
SELECT [outer] [b_id], |
|
|
||
14 |
|
[outer] [b_name] |
|
|
|
15 |
|
STUFF ((SELECT DISTINCT ',' + [inner] [g_name] |
|
||
16 |
|
FROM |
[prepared_data] AS [inner] |
|
|
17 |
|
WHERE |
[outer] [b_id] = [inner] [b_id] |
|
|
18 |
|
ORDER BY ',' |
+ [inner] [g_name] |
|
|
19 |
|
FOR XML PATH(''), TYPE).value '.', 'nvarchar(max)'), |
|
||
20 |
|
1 1 |
'') |
|
|
21 |
|
AS [genres] |
|
|
|
22 |
FROM |
[prepared_data] AS [outer] |
|
||
23 |
GROUP |
BY [outer] [b_id], |
|
|
|
Создадим триггер, позволяющий реализовать операцию вставки данных.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 285/545
Пример 30: модификация данных с использованием триггеров на представлениях
|
CREATE TRIGGER [books_with_genres_ins] |
||
|
MS SQL Решение 3.2.2.b (создание триггера для реализации операции вставки) |
||
2 |
ON [books_with_genres] |
||
3 |
INSTEAD OF INSERT |
||
4 |
AS |
|
|
5 |
INSERT INTO [genres] |
||
6 |
|
([g_name] |
|
7 |
SELECT [genres] |
||
8 |
FROM |
[inserted]; |
|
9 |
GO |
|
|
Да, это — всё. Через такое представление не удастся передать идентификатор жанра (в представлении нет соответствующего поля), равно как по той же причине не удастся реализовать добавление новых книг. А добавление нового жанра действительно реализуется настолько примитивно.
Oracl |
і Решение 3.2.2.b (создание представления) | |
||
e |
|||
|
|
||
1 |
CREATE VIEW "books with genres" |
||
2 |
AS |
|
|
3 |
SELECT "b id", "b name" |
||
4 |
UTL RAW.CAST TO NVARCHAR2 |
||
5 |
( |
|
|
6 |
LISTAGG |
||
7 |
( |
|
|
8 |
|
UTL_RAW.CAST_TO_RAW "g_name" , |
|
9 |
|
UTL RAW.CAST TO RAW(N',') |
|
10 |
) |
|
|
11 |
WITHIN GROUP (ORDER BY "g name" |
||
12 |
) |
|
|
13 |
AS "genres" |
||
14 |
FROM "books" |
||
15 |
JOIN "m2m books genres" USING ("b id") |
||
16 |
JOIN "genres" USING ("g id" |
||
17 |
GROUP |
BY "b id" |
|
18 |
|
"b name" |
|
4Переходим к решению для Oracle. Создадим представление (см.
5пояснения относительно логики получения нужного результата в решении^76*
6задачи 2.2.2.b{71}):
7 |
|
|
|
8 |
CREATE OR REPLACE TRIGGER "books_with_genres_ins" |
||
|
2 |
INSTEAD OF INSERT |
ON "books_with_genres" |
3 |
FOR EACH ROW |
|
|
|
BEGIN INSERT INTO "genres" ("g_name") |
|
|
|
VALUES |
(:new."genres"); |
|
|
END; |
|
|
Как видно из кода триггера, решение для Oracle получилось столь же примитивным в силу причин, описанных выше в решении для MS SQL Server.
Задание 3.2.2.TSK.A: создать представление, извлекающее из таблицы m2m_books_authors человекочитаемую (с названиями книг и именами авторов вместо идентификаторов) информацию, и при этом позволяющее модифицировать данные в таблице m2m_books_authors (в случае
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 286/545
Создадим триггер, позволяющий реализовать операцию вставки данных.
Oracle Решение 3.2.2.b (создание триггера для реализации операции вставки)
Пример 30: модификация данных с использованием триггеров на представлениях
неуникальности названий книг и имён авторов в обоих случаях использовать запись с минимальным значением первичного ключа).
& Задание 3.2.2.TSK.B: создать представление, показывающее список книг с их авторами, и при этом позволяющее добавлять новых авторов.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 287/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 содержит дополнительное поле, хранящее актуальную информацию о дате последнего визита читателя в библиотеку:
s_id |
s_name |
s_last_visit |
1 |
Иванов И.И. |
2015-10-07 |
2 |
Петров П.П. |
NULL |
3 |
Сидоров С.С. |
2014-08-03 |
4 |
Сидоров С.С. |
2015-10-08 |
Ожидаемый результат 4.1.1.b.
Таблица averages содержит следующую актуальную в любой момент времени информацию:
books_taken days_to_read books_returned |
||
1.2500 |
46.0000 |
1.5000 |
уЦ7
Решение 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 Стр: 288/545
Пример 31: обновление кэширующих таблиц и полей
Для MySQL первые два шага выполняются с помощью следующих запросов.
MySQL I |
Решение 4.1.1.a (модификация таблицы и инициализация данных) |
| |
||
1 |
-- Модификация таблицы: |
|
|
|
2 |
ALTER TABLE 'subscribers ' |
|
||
3 |
ADD COLUMN 's_last_visit' DATE NULL DEFAULT NULL AFTER 's_name'; |
|||
5 |
-- Инициализация данных: |
|
|
|
6 |
UPDATE 'subscribers' |
|
|
|
7 |
|
LEFT JOIN (SELECT'sb subscriber', |
|
|
8 |
|
|
MAX('sb start') AS 'last visit' |
|
9 |
|
FROM |
'subscriptions' |
|
10 |
|
GROUPBY 'sb subscriber') AS 'prepared data' |
||
11 |
|
ON 's id' |
'sb subscriber' |
|
12 |
SET |
's last visit' = |
'last visit'; |
|
|
|
|
|
|
Поскольку значение даты последнего визита читателя зависит от информации, представленной в таблице subscriptions (конкретно — от значения поля sb_start), именно на этой таблице нам и придётся создавать триггеры.
В принципе, во всех трёх триггерах можно было использовать однотипное решение (UPDATE на основе JOIN), но для разнообразия в INSERT-триггере мы реализуем более простой вариант: проверим, оказалась ли дата добавляемой выдачи больше, чем сохранённая дата последнего визита и, если это так, обновим дату последнего визита.
В UPDATE- и DELETE-триггерах такое решение не годится: если в результате этих операций окажется, что читатель ни разу не был в библиотеке, значением даты его последнего визита должен стать NULL. Такой результат проще всего достигается с помощью UPDATE на основе JOIN.
Для операций UPDATE и DELETE критически важно использовать именно AF- TER-триггеры, т.к. BEFORE-триггеры будут работать со «старыми» данными, что приведёт к некорректному определению искомой даты.
Также обратите внимание, что в UPDATE-триггере мы должны обновлять дату
последнего визита для двух потенциально разных читателей, т.к. при внесении изменения выдача может быть «передана» от одного читателя к другому (мы рассмотрим эту ситуацию в процессе проверки работоспособности полученного решения).
MySQL |
Решение 4.1.1.а (создание триггеров) |
|
1 |
-- Удаление старых версий триггеров |
|
2 |
-- (удобно в процессе разработки и отладки): |
|
DROP TRIGGER 'last_visit_on_subscriptions_ins';
4DROP TRIGGER 'last_visit_on_subscriptions_upd';
5DROP TRIGGER 'last_visit_on_subscriptions_del';
7-- Переключение разделителя завершения запроса,
8-- т.к. сейчас запросом будет создание триггера,
9-- внутри которого есть свои, классические запросы:
10DELIMITER $$
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 289/545