Работа с 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
Изменение триггера
А вот команды по изменению триггера я не нашел и был удивлен, почитал форумы, так и есть — её просто нет ((
Удаление триггера
Для удаления триггера используется, как обычно оператор 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)
Статья просмотренна 178423 раз, зашло посетителей 79681