СУБД/Практические занятия №2(2 семестр)

Ссылки сюда (1) →

Триггеры (MySQL)

• https://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

CREATE
    [DEFINER = { USER | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body
 
trigger_time: { BEFORE | AFTER }
 
trigger_event: { INSERT | UPDATE | DELETE }

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

Триггеры могут срабатывать как до BEFORE так и после AFTER изменения таблицы. Получается, что триггер может быть в 6 состояниях.

	INSERT (BEFORE | AFTER)
	UPDATE (BEFORE | AFTER)
	DELETE (BEFORE | AFTER)

Что необходимо для работы (ограничения, не все)

• Во-первых, триггеры появились в MySQL 5.0.2.
• Во-вторых, триггеры в MySQL могут создаваться только пользователем с привилегией SUPER, т.е. если Ваше приложение использует триггеры, то ни на одном, даже платном, хостинге оно работать не будет!(база для выполнения примеров и пользователь `студент` имеют эти права).
• В-третьих, нельзя редактировать таблицу уже открытую для чтения или записи условием инициализации триггера. В этом случае просто используйте оператор SET для нужных полей.

CREATE TRIGGER `artists_litera_autoupdate` AFTER UPDATE ON `cms_artists`
   FOR EACH ROW BEGIN
      UPDATE `cms_artists` SET `litera` = UPPER(SUBSTRING(NEW.`name`, 1, 1)) WHERE `id` = OLD.`id`;
   END;

В противном случае Вы получите такую ошибку: 
#1442 - Can't update table 'tablename' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

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

ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger

• В-четвёртых, значения надо менять до обновления. В конечном счёте правильный вариант:

CREATE TRIGGER `artists_litera_autoupdate` BEFORE UPDATE ON `cms_artists`
 FOR EACH ROW BEGIN
    SET NEW.`litera`= UPPER(SUBSTRING(NEW.`name`, 1, 1));
 END;

• В-пятых, при очищении таблицы (TRUNCATE) триггер, повешенный на событие DELETE, исполняться не будет.

Добавление триггера

Для того чтобы понять работу триггера рассмотрим пример.

Задание
Необходимо при добавлении записи в табл user, пароль преобразовывать в хеш md5(), также имя и отчество преобразовывать в инициалы.

• Решение

	 DELIMITER //
	 CREATE TRIGGER `test_user_pass` BEFORE INSERT ON `test`.`user`
	     FOR EACH ROW
	     BEGIN
	      SET NEW.name = LEFT(NEW.name,1);
	      SET NEW.otch = LEFT(NEW.otch,1);
	      SET NEW.pass = md5(NEW.pass);
	     END//
 DELIMITER ;

Теперь вставляем туда запись

INSERT  INTO `user` SET `fam`='Нагайченко', `name`='Максим', `otch` = 'Валерьевич', `pass` = 'password', `login` = 'maxnag';

Что теперь в таблице mysql>

SELECT * FROM `user`;

	+----+-------------+------+------+----------------------------------+--------+
	| id | fam         | name | otch | pass                             | login  |
	+----+-------------+------+------+----------------------------------+--------+
	|  1 | Нагайченко  | M    | В    | 5f4dcc3b5aa765d61d8327deb882cf99 | maxnag |
	+----+-------------+------+------+----------------------------------+--------+
 
	1 row in set (0.00 sec)

Как видно всего несколькими строчками можно убрать целые методы, которые мы использовали при регистрации нового пользователя. Теперь еще надо создать триггер на UPDATE таблицы, с таким же телом, чтобы пользователь не смог записать полное имя, отчество и пароль не в МД5();

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

	 DELIMITER //
	 CREATE TRIGGER `test_user_pass2` BEFORE UPDATE ON `test`.`user`
	    FOR EACH ROW
	     BEGIN
	      SET NEW.name = LEFT(NEW.name,1);
	      SET NEW.otch = LEFT(NEW.otch,1);
	      SET NEW.pass = md5(NEW.pass);
	     END//
	 DELIMITER ;

Обновление записи

UPDATE `user` SET `fam`='Иванов', `name`='Иван', `otch` = 'Иванович', `pass` = 'пароль', `login` = 'ivan' WHERE id=1;

Итог1

SELECT * FROM `user`;

2	+----+-------------+------+------+----------------------------------+--------+
3	| id | fam         | name | otch | pass                             | login  |
4	+----+-------------+------+------+----------------------------------+--------+
5	|  1 | Иванов      | И    | И    | e242f36f4f95f12966da8fa2efd59992 | ivan   |
6	+----+-------------+------+------+----------------------------------+--------+
7	 
8	1 row in set (0.00 sec)

Могут возникнуть вопросы, что такое NEW в теле триггера,

• NEW — для доступа к новым записям
• OLD — для доступа к старым записям

Например, если я обновил фамилию, то новое значение мне доступно через NEW.fam, а к старому OLD.fam

Список триггеров

Как получить список всех триггеров существующих в текущей базе данных?

SHOW TRIGGERS;

Как удалить триггер из текущей базы данных?

DROP TRIGGER `name`;

После удаления таблицы, к которой привязан триггер, не стоит пытаться удалить его (триггер) — MySQL сделает это автоматически.