Как создать триггеры в MySQL

Эта статья была написана в 2011 году и остается одной из наших самых популярных публикаций. Если вы хотите больше узнать о MySQL, вы можете найти эту недавнюю статью об администрировании MySQL очень интересной.

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

План нашей базы данных

Мы создадим небольшую базу данных для блогового приложения. Требуются две таблицы:

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

Следующий SQL создает `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, который ссылается на 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;

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

Теперь нам нужны два триггера:

• Когда запись вставляется в таблицу блогов, мы хотим добавить новую запись в таблицу аудита, содержащую идентификатор блога и тип «NEW» (или «DELETE», если она была немедленно удалена).
• Когда запись ОБНОВЛЯЕТСЯ в таблице блога, мы хотим добавить новую запись в таблицу аудита, содержащую идентификатор блога и тип «РЕДАКТИРОВАТЬ» или «УДАЛИТЬ», если установлен флаг удаления.

Обратите внимание, что поле changetime будет автоматически установлено на текущее время.

Каждый триггер требует:

1. Уникальное имя Я предпочитаю использовать имя, которое описывает таблицу и действие, например, blog_before_insert или blog_after_update.
2. Таблица, которая вызывает событие. Один триггер может контролировать только одну таблицу.
3. Когда срабатывает триггер . Это может быть ДО или ПОСЛЕ ВСТАВКИ, ОБНОВЛЕНИЯ или УДАЛЕНИЯ. Триггер ДО ДОЛЖЕН использоваться, если вам нужно изменить входящие данные. Триггер AFTER должен использоваться, если вы хотите сослаться на новую / измененную запись в качестве внешнего ключа для записи в другой таблице.
4. Триггерное тело ; набор команд SQL для запуска. Обратите внимание, что вы можете ссылаться на столбцы в предметной таблице, используя OLD.col_name (предыдущее значение) или NEW.col_name (новое значение). Значение NEW.col_name можно изменить в триггерах BEFORE INSERT и UPDATE.

Основной синтаксис триггера:

 
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 в таблице блогов. Нет необходимости определять триггер DELETE, поскольку сообщение помечается как удаленное, если для его удаленного поля установлено значение true.

Первая команда MySQL, которую мы выпустим, немного необычна:

 
DELIMITER $$

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

Наш триггер AFTER INSERT теперь может быть определен. Он определяет, установлен ли удаленный флаг, соответственно устанавливает переменную @changetype и вставляет новую запись в таблицу аудита:

 
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 ;

Триггер 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 ('Article One', 'Initial text.');

Новая запись появится в таблице `blog`, как и следовало ожидать:

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

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

 
UPDATE blog SET content = 'Edited text' WHERE id = 1;

Наряду с изменением сообщения в таблице `audit` появляется новая запись:

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

 
UPDATE blog SET deleted = 1 WHERE id = 1;

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

Это простой пример, но я надеюсь, что он дал некоторое представление о мощи триггеров MySQL. В моем следующем посте мы реализуем запланированное событие для архивирования удаленных сообщений.

Если вам понравилось читать этот пост, вы полюбите Learnable ; место, чтобы узнать новые навыки и приемы у мастеров. Участники получают мгновенный доступ ко всем электронным книгам и интерактивным онлайн-курсам SitePoint, таким как PHP и MySQL для веб-разработчиков для начинающих .