Триггеры в 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 требуются полномочия суперпользователя.
    Share post
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 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
                      самый большой косяк триггеров — если затык в базе фиг определишь что дело в триггерах, но точнее определить очень и очень сложно.
                      • UFO just landed and posted this here
                          0
                          а вот тут только на опыте, а не примере.
                      • UFO just landed and posted this here
                        • UFO just landed and posted this here
                            0
                            INSERT INTO log Set msg = 'insert', row_id = NEW.id;
                            Здесь под NEW подразумевается строка, которая будет вставлена.
                            INSERT INTO backup Set row_id = OLD.id, content = OLD.content;
                            OLD строка, к которой еще не применены каие-либо действия
                            • UFO just landed and posted this here
                                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.

                                                            Only users with full accounts can post comments. Log in, please.