Триггеры в MySQL

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

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

- 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 триггер. Тут мы вводим переменную @changetype, которая будет хранить значение из 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, как и следовало ожидать.

Кроме того, в нашей таблице audit появляется новая запись.

Давайте обновим наш текст в блоге:

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

Помимо изменения поста, в таблице audit появляется новая запись.

Наконец, давайте отметим пост как удаленный:

UPDATE blog SET deleted = 1 WHERE id = 2;

Соответственно обновляется таблица audit, и у нас есть запись о произошедших изменениях.

Вот так. Все работает и триггеры отлично справляются со своей задачей.