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

Статьи на тему программирования под веб, используя 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 в фирменной упаковке и сделать хороший подарок родным и близким, Вы можете там же.

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

рубрика: MySQL

13 комментариев в “Работа с trigger (триггерами) в MySQL”

  1. Avatar
    Yuriy пишет:

    клево.. попробуем)


  2. Avatar
    adw0rd пишет:

    Как приостановить выполнения SQL после триггера?
    То есть, при определенных условиях, описанных в триггере надо остановить выполнения SQL который и вызвал этот триггер.

    Единственное что у меня получилось, это выполнять ошибочный SQL-код, тем самым останавливать выполнение SQL кода, который выполнится после триггера.

    delimiter //
    DROP TRIGGER IF EXISTS `customers_update_textstatus`//
    CREATE TRIGGER `customers_update_textstatus`
    BEFORE UPDATE ON `customers` FOR EACH ROW BEGIN 
    	IF new.text_status < old.text_status THEN BEGIN
    		INSERT INTO `customers` SET old.text_status = "STOP_SQL_HACK";
    	END;	
    	END IF;
    END//
    delimiter ;
    

    text_status — у меня enum и у него нет значения STOP_SQL_HACK, поэтому происходит ошибка и SQL не выполняется.


  3. Avatar
    Andrey пишет:

    Вставлю свои 5 коппек. Триггеры будут доступны для просмотра только юзерам с привелегиями SUPER.
    Пруфлинк: http://tokarchuk.ru/2010/07/mysql-triggers-invisi


  4. Avatar
    maxnag пишет:

    Вы наверное не внимательно читали мою статью. В разделе «Что необходимо для работы!» так это и написано.
    Вот еще ссылки на официальную документацию http://dev.mysql.com/doc/refman/5.1/en/triggers.h

    PS Этот каммент только ради кросс-ссылки?


  5. Avatar
    Andrey пишет:

    Сорри, пропустил. Можете удалить.


  6. Avatar
    Сергей пишет:

    Здрасте Макс. Не нашол в гугле свободной связи с Вами… всюду нужна регистрация.
    Нужно добавить триггер или.. к плагину коментариев Вордпресс, тоесть чтоб пользователь обязан был задать время через которое коментарий удалится автоматически. Сергей 0507089682


  7. Avatar
    Vlad пишет:

    А можно ли делать вставку данных в с помощью тригера в другую таблицу?
    Т.е. мне нужно после INSERT INTO `A` values (1,'test','test');
    SET @id := LAST_INSERT_ID();
    добавить запись в другую таблицу b но при этом использовать @id из таблицы A .


  8. Avatar
    maxnag пишет:

    Пробуйте! Узнаете много нового.


  9. Avatar
    Paymer-Soft пишет:

    В Вашем примере создаются 2 триггера с одинаковым исполнительным кодом на события BEFORE INSERT и BEFORE UPDATE, а нельзя ли во втором случае (BEFORE UPDATE) вместо создания функциональной копии сослаться на уже готовый триггер под BEFORE INSERT?


  10. Avatar
    maxnag пишет:

    Сослаться нельзя, но можно сделать процедуру в которую будите передавать значения, а в процедуре уже пропишите запросы на обновление данных. Но в данном примере проще сделать так как я писал, т.к. о процедурах в рамках данной статьи я не рассказывал.


  11. Avatar
    Cool пишет:

    А где писать исходный код триггера, где хранится? Например я использую программу NAvicat для доступа к базе


  12. Avatar
    maxnag пишет:

    Привет! Сool, ты очень плохо прочитал статью, там всё написано, как создавать, удалять триггер. Если у тебя вопрос по NAvicat, то читай справку по этой программе, я ей не пользуюсь.


  13. Avatar
    Oleg пишет:

    действительно хорошая и доступная статья. спасибо!


не публикуется

пример

Оставить комментарий или два:

  

Облако тегов

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  

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