Работа с 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)
Статья просмотренна 179078 раз, зашло посетителей 79974
клево.. попробуем)
Как приостановить выполнения SQL после триггера?
То есть, при определенных условиях, описанных в триггере надо остановить выполнения SQL который и вызвал этот триггер.
Единственное что у меня получилось, это выполнять ошибочный SQL-код, тем самым останавливать выполнение SQL кода, который выполнится после триггера.
text_status — у меня enum и у него нет значения STOP_SQL_HACK, поэтому происходит ошибка и SQL не выполняется.
Вставлю свои 5 коппек. Триггеры будут доступны для просмотра только юзерам с привелегиями SUPER.
Пруфлинк: http://tokarchuk.ru/2010/07/mysql-triggers-invisi…
Вы наверное не внимательно читали мою статью. В разделе «Что необходимо для работы!» так это и написано.
Вот еще ссылки на официальную документацию http://dev.mysql.com/doc/refman/5.1/en/triggers.h…
PS Этот каммент только ради кросс-ссылки?
Сорри, пропустил. Можете удалить.
Здрасте Макс. Не нашол в гугле свободной связи с Вами… всюду нужна регистрация.
Нужно добавить триггер или.. к плагину коментариев Вордпресс, тоесть чтоб пользователь обязан был задать время через которое коментарий удалится автоматически. Сергей 0507089682
А можно ли делать вставку данных в с помощью тригера в другую таблицу?
Т.е. мне нужно после INSERT INTO `A` values (1,'test','test');
SET @id := LAST_INSERT_ID();
добавить запись в другую таблицу b но при этом использовать @id из таблицы A .
Пробуйте! Узнаете много нового.
В Вашем примере создаются 2 триггера с одинаковым исполнительным кодом на события BEFORE INSERT и BEFORE UPDATE, а нельзя ли во втором случае (BEFORE UPDATE) вместо создания функциональной копии сослаться на уже готовый триггер под BEFORE INSERT?
Сослаться нельзя, но можно сделать процедуру в которую будите передавать значения, а в процедуре уже пропишите запросы на обновление данных. Но в данном примере проще сделать так как я писал, т.к. о процедурах в рамках данной статьи я не рассказывал.
А где писать исходный код триггера, где хранится? Например я использую программу NAvicat для доступа к базе
Привет! Сool, ты очень плохо прочитал статью, там всё написано, как создавать, удалять триггер. Если у тебя вопрос по NAvicat, то читай справку по этой программе, я ей не пользуюсь.
действительно хорошая и доступная статья. спасибо!
Целиком строить на триггерах так же глупо как и целиком строить на коде. К сожалению пример сстроками малость сбил с правильных мыслей, но поверьте мне, всестрок триггера или хранимки придуманы и написаны не одним человеком, а группой специалистов которые разобрали все плюсы и минусы и понимают всю долю ответственности за свое решение.