Дополнительное задание (1). Задание 1 Теоретические сведения Триггер
 Скачать 24.36 Kb.
|
|
Работать на локальном сервере (созданном при установке MySQL Workbench). Задание 1 Теоретические сведения Триггер – эта та же хранимая процедура, но привязанная к событию изменения содержимого конкретной таблицы. Возможны три события, связанных с изменением содержимого таблицы, к которым можно привязать триггер: insert – вставка новых данных в таблицу; delete – удаление данных из таблицы; Update – обновление данных в таблице. Например, при оформлении нового заказа, т.е. при добавлении новой записи в таблицу orders, можно создать триггер, автоматически вычитающий число заказанных товарных позиций в таблице books. Создать новый триггер позволяет оператор: CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt; Оператор создает триггер с именем trigger_name, привязанный к таблице tbl_name. Не допускается привязка триггера к временной таблице или представлению. Конструкция trigger_time указывает момент выполнения триггера и может принимать два значения: before – действия триггера производятся до выполнения операции изменения таблицы; after – действия триггера производятся после выполнения операции изменения таблицы. Конструкция trigger_event показывает, на какое событие должен реагировать триггер, и может принимать три значения: insert – триггер привязан к событию вставки новой записи в таблицу; update – триггер привязан к событию обновления записи таблицы; delete – триггер привязан к событию удаления записей таблицы. Для таблицы tbl_name может быть создан только один триггер для каждого из событий trigger_event и момента trigger_time. Таким образом, для каждой из таблиц может быть создано всего шесть триггеров. Конструкция trigger_stmt представляет тело триггера – оператор, который необходимо выполнить при возникновении события trigger_event в таблице tbl_name. Удаление триггеров. Удалить существующий триггер позволяет оператор DROP TRIGGER trigger_name; Практическая часть 1. Создадим для примера базу данных для блога. Нам понадобится две таблицы: `blog`: хранит уникальный идентификатор поста, заголовок, содержимое и флаг того, считается ли запись удаленной (на деле запись блога удаляться никогда не будет, а будет помечаться флагом, что запись считается удаленной или не удаленной). `audit`: хранит базовый набор исторических изменений с идентификатором записи, идентификатором сообщения блога, типом изменения (NEW, EDIT или DELETE) и датой/временем этого изменения. 2. Создадим таблицу `blog`: CREATE TABLE `blog` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `title` text, `content` text, `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `ix_deleted` (`deleted`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog posts'; 3. Далее напишем sql код для создания таблицы `audit`. Нужно назначить для всех полей индексы и определить внешний ключ как audit.blog_id который ссылается на поле id из таблицы `blog`. Привязка по внешнему ключу позволит делать следующее: когда мы удаляем запись в блоге, также удаляется полная история аудита советующего поля по blog_id. CREATE TABLE `audit` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `blog_id` mediumint(8) unsigned NOT NULL, `changetype` enum('NEW','EDIT','DELETE') NOT NULL, `changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `ix_blog_id` (`blog_id`), KEY `ix_changetype` (`changetype`), KEY `ix_changetime` (`changetime`), CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 4. Теперь создаем два триггера: Первый - когда новая запись создается в таблице `blog`, создаем для нее запись в таблице `audit` c `blog_id` соответствующей id новой записи блога и типом 'NEW' из набора enum('NEW','EDIT','DELETE'). Второй - когда запись в блоге обновляется, то добавляем запись в `audit` с типом 'EDIT' из набора enum('NEW','EDIT','DELETE'). Время при этом устанавливается автоматически, в момент создания записи. Создаем AFTER INSERT триггер. Тут мы вводим переменную @changetyp, которая будет хранить значение из enum('NEW','EDIT','DELETE') в зависимости от условия, какое значение установлено в blog.deleted: DELIMITER $$ CREATE TRIGGER `blog_after_insert` AFTER INSERT ON `blog` FOR EACH ROW BEGIN IF NEW.deleted THEN SET @changetype = 'DELETE'; ELSE SET @changetype = 'NEW'; END IF; INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype); END$$ DELIMITER ; На столбцы таблицы, к которой привязан триггер (в данном случае `blog`) можно ссылаться с помощью псевдонимов OLD и NEW. NEW - это вновь созданная таблица. Поэтому NEW.deleted содержит значение этого столбца при создании записи в `blog`. OLD.col_name - указывает на данные стрлбца до удаления или изменения данных при соответствующих событиях триггеров UPDATE/DELETE. Триггер AFTER UPDATE: DELIMITER $$ CREATE TRIGGER `blog_after_update` AFTER UPDATE ON `blog` FOR EACH ROW BEGIN IF NEW.deleted THEN SET @changetype = 'DELETE'; ELSE SET @changetype = 'EDIT'; END IF; INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype); END$$ DELIMITER ; 5. Проверка работы триггера: Посмотрим, что произойдет, когда мы добавим новый столбец в нашу таблицу блога: INSERT INTO blog (title, content) VALUES ('Первая запись', 'Текст записи'); 6. Проверим появилась ли запись в таблицах. Обновим наш текст в блоге: UPDATE blog SET content = 'Изменения текста' WHERE id = 1; 7. После обновления просмотрите таблицу audit. Что там изменилось? Отметим пост как удаленный: UPDATE blog SET deleted = 1 WHERE id = 1; 8. Посмотрите, что произошло с таблицей audit. Задание 2 1. Изучить теоретические сведения в презентации (лежит в папке с этим файлом). 2. Открыть консоль MySQL Workbench. 3. Средствами языка SQL создайте две таблицы (на свой выбор). 4. Во всех таблицах создайте первичный ключ и свяжите их с помощью внешнего ключа. |