Создание триггеров в mysql на примере блога.
  • 2193

Как создавать и использовать триггеры в Mysql.

Автор: admin | 14 июня (Чт.) 2018г. в 12ч.10м.

Что такое триггеры в Mysql.

Триггер - это код SQL, который запускается непосредственно перед или сразу после того, как
событие INSERT, UPDATE или DELETE происходит в конкретной таблице базы данных.
Триггеры поддерживаются в MySQL начиная с версии 5.0.2.
Приступим к практическому примеру.

Планируем структуру базы данных.

Создадим для примера базу данных для блога. Нам понадобится две таблицы:

-`blog`: хранит уникальный идентификатор поста, заголовок, содержимое и флаг того, считается ли запись удаленной (на деле запись блога удаляться никогда не будет, а будет помечаться флагом, что запись считается удаленной или не удаленной).

-`audit`: хранит базовый набор исторических изменений с идентификатором записи,
идентификатором сообщения блога, типом изменения (NEW, EDIT или DELETE) и датой/временем этого изменения.

Создадим таблицу `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';​

Далее напишем 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;

Создание триггера.

Теперь мы создаем два триггера:
Первый - когда новая запись создается в таблице `blog`, создаем для нее запись в таблице `audit`
c `blog_id` соответствующей id новой записи блога и типом 'NEW' из набора enum('NEW','EDIT','DELETE').

Второй - когда запись в блоге обновляется, то добавляем запись в `audit` с типом 'EDIT' из набора enum('NEW','EDIT','DELETE').
Время при этом устанавливается автоматически, в момент создания записи.

Основной синтаксис триггера:
CREATE
    TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `database`.`table`
    FOR EACH ROW BEGIN
		-- trigger body
		-- this code is applied to every 
		-- inserted/updated/deleted row
    END;​

Итак, нам требуется два триггера AFTER INSERT и AFTER UPDATE на событие в таблице `blog`.
Нет необходимости определять триггер DELETE, поскольку пост помечен как удаленный,
установив для поля `deleted` значение true.

Первая команда MySQL, которую мы напишем, это разделитель:
 DELIMITER $$

Для нашего тела триггера требуется несколько команд SQL, разделенных точкой с запятой (;).
Чтобы создать полный код триггера, мы должны изменить разделитель на что-то другое, например $.

Создаем 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.

Наконец, мы устанавливаем разделитель обратно в точку с запятой:
 DELIMITER ;

Триггер 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 ;
Это выходит за рамки этой статьи, но вы можете рассмотреть возможность вызова единой хранимой
процедуры, которая обрабатывает оба триггера.

Проверка работы триггера.

Посмотрим, что произойдет, когда мы добавим новый столбец в нашу таблицу блога:
INSERT INTO blog (title, content) VALUES ('Первая запись', 'Текст записи');​

Новая запись появляется в таблице `blog`, как и следовало ожидать:
Создание таблицы постов в блоге mysql.
Кроме того, в нашей таблице `audit` появляется новая запись:
По триггеру создается запись в таблице audit.
Давайте обновим наш текст в блоге:

UPDATE blog SET content = 'Изменения текста' WHERE id = 2;

Помимо изменения поста, в таблице «audit» появляется новая запись:
При обновлении таблицы blog по триггеру создается запись в таблице audit.
Наконец, давайте отметим пост как удаленный:

UPDATE blog SET deleted = 1 WHERE id = 2;

Соответственно обновляется таблица "audit", и у нас есть запись о произошедших изменениях:
При установке флага delete=1 создается по триггеру запись в таблице audit.
Вот так. Все работает и триггеры отлично справляются со своей задачей.

В этой статье мы рассмотрели простой пример, который дает некоторое представление о возможностях триггеров в
MySQL.

Приветствую!

Меня зовут Сергей. Я - автор этого блога.

Если Вам был полезен материал на моем сайте, поддержите пожалуйста мой проект, чтобы о нем узнали другие люди - кликните plizz :) на иконку в соц. сети, чтобы поделиться материалом с другими.