За чистый и ясный код!

Статьи на тему программирования под веб, используя PHP, MySQL, Jquery и многое другое

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

Декабрь2

Предисловие

Триггеры это особые процедуры которые срабатывают при изменении данных в таблицы операторами 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)
Хороший ассортимент выбора книг, электроники, бытовой техники, посуды, подарки, сувениры и много другое Вы можете сделать на сайте компании E5.RU. Купить фильмы на dvd в фирменной упаковке и сделать хороший подарок родным и близким, Вы можете там же.

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

  

Облако тегов

cli csv dump events form Kohana locale models MySQL mysqldump orm PHP tools trigger validate газ газовый счетчик итоги кеширование переменные

Облако тегов плагина WP Cumulus для WordPress требует для просмотра Flash Player 9 или выше.

Я на твиттере!

  • у твиттера тоже бывают перерывы...

Календарь

Декабрь 2009
Пн Вт Ср Чт Пт Сб Вс
« Ноя   Янв »
 123456
78910111213
14151617181920
21222324252627
28293031  

Сейчас на сайте