Пользователь
0,0
рейтинг
21 августа 2008 в 20:06

Разработка → Триггеры в MySQL

Триггер — это хранимая процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события ( вставка, удаление, обновление строки ).
Поддержка триггеров в MySQL началась с версии 5.0.2

Синтаксис создания триггера:


CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
* This source code was highlighted with Source Code Highlighter.

trigger_name — название триггера
trigger_time — Время срабатывания триггера. BEFORE — перед событием. AFTER — после события.
trigger_event — Событие:
insert — событие возбуждается операторами insert, data load, replace
update — событие возбуждается оператором update
delete — событие возбуждается операторами delete, replace. Операторы DROP TABLE и TRUNCATE не активируют выполнение триггера
tbl_name — название таблицы
trigger_stmt выражение, которое выполняется при активации триггера

Применение


Лог


Исходные данные:
— таблица, за которой мы будем следить
CREATE TABLE `test` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`content` TEXT NOT NULL
) ENGINE = MYISAM
— лог
CREATE TABLE `log` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`msg` VARCHAR( 255 ) NOT NULL,
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`row_id` INT( 11 ) NOT NULL
) ENGINE = MYISAM
— триггер
DELIMITER |
CREATE TRIGGER `update_test` AFTER INSERT ON `test`
FOR EACH ROW BEGIN
   INSERT INTO log Set msg = 'insert', row_id = NEW.id;
END;
* This source code was highlighted with Source Code Highlighter.

Теперь добавьте запись в таблицу test. В таблице log тоже появится запись, обратите внимание на поле row_id, в нем хранится id вставленной вами строки.

Расширенный лог:


Исходные данные:
— Удаляем триггер
DROP TRIGGER `update_test`;
— Cоздадим еще одну таблицу,
— в которой будут храниться резервные копии строк из таблицы test
CREATE TABLE `testing`.`backup` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`row_id` INT( 11 ) UNSIGNED NOT NULL,
`content` TEXT NOT NULL
) ENGINE = MYISAM
— триггеры
DELIMITER |
CREATE TRIGGER `update_test` before update ON `test`
FOR EACH ROW BEGIN
  INSERT INTO backup Set row_id = OLD.id, content = OLD.content;
END;

CREATE TRIGGER `delete_test` before delete ON `test`
FOR EACH ROW BEGIN
  INSERT INTO backup Set row_id = OLD.id, content = OLD.content;
END
* This source code was highlighted with Source Code Highlighter.

Теперь если мы отредактируем или удалим строку из test она скопируется в backup.

зы: надеюсь статья была интересной и полезной
UPD: для создания триггеров в версии до 5.1.6 требуются полномочия суперпользователя.
Michael Zhalevich @Pokoinik
карма
52,0
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама

Самое читаемое Разработка

Комментарии (49)

  • –1
    во! то что искал, и самое обидное-то знал про это, но напроч из головы вылетело!
  • 0
    А вот для меня это новенькая вещь… спасибо!.. применений найдется не мало… но вот как насчет производительности?
    • 0
      Производительность будет слабая. К примеру на Oracle триггеры являются главной затычкой во многих системах, потому к их использованию нужно подходить очень аккуратно.
      • 0
        В общем случае не согласен. К примеру, обновить вновь вставляемую запись в триггере INSERT BEFORE через алиас NEW будет быстрее и производительнее, нежели делать UPDATE сразу после вставки (или использования INSERT AFTER :)). В общем, возможны варианты :)
  • +10
    А можно подобную статейку, только про View?
    • +13
      если актуально — напишу…
      • 0
        Смотрю мой комментарий в минус не ушел, значит актуально (на момент написания +3 +1 (я))
      • 0
        Конечно.
      • 0
        очень актуально! ждем статью ;)
      • 0
        я думаю статья про вьюхи будет очень полезна и актуальна
    • +1
      будьте аккуратны с View — MySQL VIEW as performance troublemaker.
      • 0
        Как обычно, в комментах куча всего полезного :) спасибо за ссылку
  • +3
    А упомянуть, что триггеры добавляются только при наличии привилегии SUPER? Именно этот факт и делает их столь неудобными.
    • +1
      …в MySQL ветки 5.0.*. В 5.1 несколько по-другому. И более правильно.
      • +1
        Вот когда 5.1 из RC выберется, тогда и будем использовать.
  • +1
    самый большой косяк триггеров — если затык в базе фиг определишь что дело в триггерах, но точнее определить очень и очень сложно.
    • НЛО прилетело и опубликовало эту надпись здесь
      • 0
        а вот тут только на опыте, а не примере.
  • НЛО прилетело и опубликовало эту надпись здесь
  • НЛО прилетело и опубликовало эту надпись здесь
    • 0
      INSERT INTO log Set msg = 'insert', row_id = NEW.id;
      Здесь под NEW подразумевается строка, которая будет вставлена.
      INSERT INTO backup Set row_id = OLD.id, content = OLD.content;
      OLD строка, к которой еще не применены каие-либо действия
      • НЛО прилетело и опубликовало эту надпись здесь
        • 0
          насколько мне известно — нет.
  • –1
    Скажите, в чём суть статьи, если Вы просто кратко пересказали и без того короткую главу мануала?
    • 0
      может быть Вы наконец научите меня писать статьи? расскажите как это делается?
      а плюс хотя бы в том, что статья написана ( и обсуждается ) на русском языке…
      если Вам кажется, что такая статья не нужна хабру — смело ставьте минус… а лучше напишите такую, которая на Ваш взгяд будет лучше…
      охотно изучу…
      • +1
        Учить не буду, ибо сам не умею писать :)

        Могу показать несколько моментов, которые лично мне не понравились. Навкидку:

        Не написано, а для чего, собственно, они вообще нужны? Новичкам (а ведь именно на них рассчитывалась статья, так ведь?) надо показать, что триггеры — это удобно круто и модно. Пару красивых аргументов привести, а не слова а-ля «не вызывается непосредственно».
        Где хоть слово про то, что в mysql триггеры можно создавать только с суперпривилегиями?
        Почему не указано, что мускул не умеет полностью работать с той таблицей, на которую вешается триггер (об этом в мануале написано очень вскользь, понять без практики, IMHO, довольно сложно)?
        Чуть выше в комментариях спрашивалось про алиасы OLD и NEW. Вот Вы их просто указали в примере. А где объясление, что это такое, для чего?

        В общем, статья — не более чем вольный и урезанный перевод страницы мануала, в котором, несмотря на всю его скудность, информации побольше.

        Не обессудьте, но я бы на Вашем месте оформил бы этот топик в виде ссылки на официальную документацию.

        • +1
          постараюсь учесть Ваши пожелания в будущем…
        • 0
          Главная полезность для меня как для тотального новичка заключается в том, что можно совмещать INSERT и UPDATE для определённых случаев. Я прав?
  • 0
    А как насчет прав? Недавно пришлось столкнуться с тем, что, оказывается, для того, чтобы создать триггер, нужны права суперпользователя (мануал говорит, что до версии 5.1.6), а на шаред-хостинге такого счастья тебе никто не позволит.
    • 0
      начнем с того, что на большинстве шаред-хостингов стоит еще mysql 4й ветки…
      мне попадались адекватные хостеры, в саппорт высылался sql-код нужных триггеров, они сами его выполняли…
      все зависит от постановки работы с клиентом… люди, которым важны клиенты, тебе по запросу поставят 5й mysql
      • +2
        все понятно, просто об этом стоило бы упомянуть в статье. иначе все ломанутся их использовать в разработке, а потом при деплойменте проблем не оберешься.
        • 0
          добавил, спасибо…
      • 0
        Мастерхост мне жестко сказал, что такой возможности мне не дадут…
        Пришлось логику организовывать на стороне, а так идеально было бы все в мускуле оставить =(
  • 0
    Спасибо за статью, кратко и ёмко) Если бы было чем — послал бы симпафки)
  • +1
    С точки зрения простоты триггеры — не очень удачное решение, поскольку запутывают структуру и логику базы данных. Если требуется сделать нечто больше, чем update поля, лучше использовать хранимую процедуру. В идеале, любое изменяющее действие над базой должно проходить через процедуру, а не напрямую в таблицы. Таким образом контролируется все валидные действия бизнес тайера и обеспечивается целостность.
    • 0
      Ну тут главное правильно их применять. Если бы они были бы ненужными и неэфективными, то не дашагали бы от других СУБД до мускла… А по поводу логики БД, тоесли триггер к месту, то лигика не запутается.
    • 0
      Хе-хе. Триггер суть реакция на изменение состояния таблицы. Читайте — событие.

      А уж что в его теле происходит — логируется запрос, происходит какое-то обновление или фиг знает что ещё — дело десятое. Можно и хранимую процедуру позвать, дабы не нарушать целостность базы.

  • –4
    А мы это в универе проходили )
  • +2
    Куда нажать чтобы добавить в избранное?
  • +1
    подробности и примеры в официальном доке MySQL 5.0 Triggers (инглиш, пдф, 114 КБ)
  • 0
    Большое спасибо за статью с хорошими примерами.
  • 0
    вопрос, как триггера будут отрабатывать при больших объемах:
    скажем таблица ~3000000 записей, 10-30 инсертов в минуту + периодическая нагрузка селектами на таблицу.
  • 0
    Если не следить должным образом за триггерами, то они могут стать тайным хранилищем бэкдоров
  • 0
    Спасибо!!!
  • 0
    Не могу найти в спеке, может кто подскажет :)
    Можно в одном триггере иметь несколько евентов?
    Что-то типа
    CREATE TRIGGER `xxx` AFTER INSERT OR UPDATE ON `yyy`…
    • 0
      нет, это должны быть два разных триггера
  • 0
    посмотри здесь про регистры
  • 0
    Интересная статья, а можно ли применить триггер если:
    1. Мы имеем запись в базе данных (пользователя) с полем email.
    2. При записи новой строки данных (пользователя) проверять, есть ли такой пользователь (с этим email) в базе данных с помощью триггера.
    • 0
      Это можно и без триггера реализовать.

      Например, устанавливаем email как PRIMARY KEY или UNIQUE, и далее делаем INSERT IGNORE или REPLACE.

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.