Работа с trigger (триггерами) в MySQL

Предисловие

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

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

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

Что необходимо для работы

Cервер БД версии 5.0.2 и выше
привилегии на использование этой ф-ции (TRIGGER) начиная с версии 5.1.6, до этого было SUPER, но т.к. все локально используют пользователя root, то опасаться нечего

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

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

Задание

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

Решение

mysql> DELIMITER //
mysql> 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//
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;

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

mysql> INSERT  INTO `user` SET `fam`='Нагайченко', `name`='Максим', `otch` = 'Валерьевич', `pass` = 'password', `login` = 'maxnag';
Query OK, 1 row affected (0.00 sec)

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

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

mysql> DELIMITER //
mysql> 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//
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;

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

mysql> UPDATE `user` SET `fam`='Иванов', `name`='Иван', `otch` = 'Иванович', `pass` = 'пароль', `login` = 'ivan' WHERE id=1;
Query OK, 1 row affected (0.00 sec)

Итог

mysql> SELECT * FROM `user`;
+----+-------------+------+------+----------------------------------+--------+
| id | fam         | name | otch | pass                             | login  |
+----+-------------+------+------+----------------------------------+--------+
|  1 | Иванов      | И    | И    | e242f36f4f95f12966da8fa2efd59992 | ivan   |
+----+-------------+------+------+----------------------------------+--------+

1 row in set (0.00 sec)

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

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

Изменение триггера

А вот команды по изменению триггера я не нашел и был удивлен, почитал форумы, так и есть — её просто нет ((

Alter trigger

Удаление триггера

Для удаления триггера используется, как обычно оператор DROP, пример

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

где
schema_name — название БД,
trigger_name — название триггера

Список созданных триггеров

Показать триггер можно с помощью команды

SHOW TRIGGERS [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

Это полная часть команды, в основном пользуются командами:

SHOW TRIGGERS [FROM db_name] [LIKE 'pattern']
или
SHOW TRIGGERS

Это полная часть команды, в основном пользуются командами:

SHOW TRIGGERS [FROM db_name] [LIKE 'pattern']
или
SHOW TRIGGERS

Вот что мы увидем, когда выполним команду

mysql> SHOW TRIGGERS\G;
*************************** 1. row ***************************
             Trigger: test_user_pass
               Event: INSERT
               Table: user
           Statement: BEGIN
                          SET NEW.name = LEFT(NEW.name,1);
                          SET NEW.otch = LEFT(NEW.otch,1);
                          SET NEW.pass = md5(NEW.pass);
                      END
              Timing: BEFORE
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.01 sec)

Статья просмотренна 179287 раз, зашло посетителей 80056