MySQL и триггеры. Тонкости и особенности.

Давайте поговорим о триггерах в MySQL. Это сложная и в некотором роде больная тема в MySQL. Они появились, начиная с версии 5.0.2, и пока еще не все с ними “устаканилось”. У триггеров тут есть несколько тонкостей и о них стоит помнить каждый раз, когда мы пишем очередной триггер.

Триггер можно создавать всего на 3 события INSERT, UPDATE, DELETE. Для каждого из этих событий есть возможность управлять моментом срабатывания триггера: “до” события, “после” события. Итого, со всевозможными комбинациями получаем 6 типов триггеров.

Создание триггера.

Синтаксис довольно прост и незамысловат.

 

В DEFINER мы можем указать пользователя-создателя, а в остальном, я думаю, комментариев достаточно. Рассмотрим, что же можно делать внутри тела триггера. В целом там можно писать на SQL различные конструкции, условия, циклы и т.д. Кроме того, код между BEGIN и END выполнится для всех записей, затронутых операцией, на которую срабатывает триггер. Но важно помнить несколько особенностей триггеров в MySQL.

Первое: супер ты или не супер?

Да-да. Глупость казалось бы, но все же. Для того, чтобы создать триггер, вы должны обладать правами суперпользователя, иначе получите ошибку и ничего не добьетесь. Это создает некоторые проблемы, ведь большинство хостинговых площадок не дает таких прав своим клиентам.

Второе: lock – это серьезно!

Часто причиной ошибок и затруднений при создании триггера в MySQL является  тот факт, что во время срабатывания триггера та таблица, на которую он повешен, становится залоченой. Таким образом, мы не можем производить никакие операции с данной таблицей. Есть, конечно, возможности обойти эти запреты, но это уже костыли.

Третье: никаких return’ов!

Да. Это тоже минус. Мы не можем вернуть никакие данные из триггера. То есть, представьте себе ряд условий, выполнение которых необходимо для продолжения работы триггера. По хорошему, мы могли бы прервать тут исполнение триггера конструкцией типа “RETURN;”, но ничего не выйдет. Придется опять выдумывать костыли и генерировать ошибку во время исполнения (например, SET NEW=NULL;).

Но не все так плохо как кажется. В нашем распоряжении есть такие значения как NEW и OLD. Они позволяют обращаться к новой (или вставляемой) записи или же к старой (содержащейся в таблице) соответственно.

Как работать?

Несмотря на все это, с триггерами можно работать, и иногда они оказываются полезными. Мы можем слегка обогнуть lock. Если нам нужно обновить текущую таблицу, просто используем SET (например, SET NEW.<имя_поля>=1;). Как видим в примере, OLD и NEW очень удобны. Но надо помнить, когда они доступны, а когда нет.

  • BEFORE INSERT
    • - NEWдоступен.
      • – Доступна возможность изменения его полей.
      • – Доступна возможность получить значения полей и полей по умолчанию (которые явно в INSERT’е не фигурировали).
      • – Не доступен автоинкремент.
    • - OLDне доступен (нет ничего прежнего во время вставки).
    • - Внутри триггера можно отменить операцию.
  • AFTER INSERT
    • - NEWдоступен.
      • – Не доступна возможность изменения полей. Они уже вставлены.
      • – Доступна возможность получить значения полей и полей по умолчанию.
      • – Доступна возможность получить автоинкременты.
    • - OLDне доступен (по тем же причинам).
    • - Внутри триггера нельзя отменить операцию.
  • BEFORE UPDATE
    • - NEW - доступен.
      • – Доступна возможность изменения его полей.
      • – Доступна возможность получить значения полей и полей по умолчанию.
      • – Не доступен автоинкремент.
    • - OLDдоступен.
    • - Внутри триггера можно отменить операцию.
  • AFTER UPDATE
    • - NEWдоступен.
      • – Не доступна возможность изменения полей. Они уже вставлены.
      • – Доступна возможность получить значения полей и полей по умолчанию.
      • – Доступна возможность получить автоинкременты.
    • - OLDдоступен.
    • - Внутри триггера нельзя отменить операцию.
  • BEFORE DELETE
    • - NEWне доступен (нет ничего нового).
    • - OLDдоступен.
      • – Доступна возможность получить значения полей.
    • - Внутри триггера можно отменить операцию.
  • AFTER DELETE
    • - NEWне доступен (по той же причине).
    • - OLD - доступен.
    • - Внутри триггера нельзя отменить операцию.

Вот такой вот список получился. Он, в принципе, логичен и понятен. Руководствуясь им, можно определить, где и когда можно использовать OLD и NEW, а главное как.

Когда мы написали триггер, то есть несколько операций, которые мы можем выполнить:

 

Тут мы удаляем триггер, если он есть. Далее:

 

Тут мы можем просмотреть набор триггеров для данной базы данных. Причем можно выбирать как по шаблону (LIKE), так и при помощи WHERE (внутри можно указать параметры: Trigger-имя триггера, Event-событие триггера, Table-имя таблицы, Statement-активирован триггер или нет, Timing-время наступления события).

Итак, мы рассмотрели основные тонкости и особенности при работе с триггерами в MySQL. Помните, что с ними нужно быть очень осторожными.

12 комментария MySQL и триггеры. Тонкости и особенности.

  1. natylis

    Очень интересная статья. А главное – все понятно. И написано простым языком!

  2. cava150

    добрый день!
    Спасибо автору за статью интерестно.
    Но хотел спросить у автора-выв много раз использовали тригер в мускуле?
    тем более,что по сравнению с mssql,мускул не все “скопировал”=)

  3. MUTOgenMUTOgen

    Да, отчасти вы правы. Триггеры в mysql не так хороши как хотелось бы (это упоминается в статье). В mssql куда богаче в этом плане функционал и возможности. Но для небольших простеньких локальных задачек этих возможностей хватит, тем более, что богатого выбора в плане СУБД в сети сейчас нет и большинство хост-площадок по умолчанию предоставляют именно mysql. Хотя в последнее время postgreSQL хорошую конкуренцию составляет.

  4. Дмитрий

    Добрый день!
    Спасибо Вам за статью!
    прошу Вашей помощи в написании триггера.
    mySQL 5.1.39
    база – test
    таблица – power
    нужно, чтобы после 100 записи в базу, при добавлении новой, последняя стиралась…. и так по кругу.
    Заранее огромное Вам спасибо.

  5. АнтонАнтон

    Дмитрий, я бы на вашем месте использовал AFTER INSERT и в тело триггера вставил некоторую условную логику, где проверял бы (NEW.id – 1) на кратность 100, и если это условие выполняется, то вычислял бы целое от этого выражения, а затем не трудно вытащить нужный id для DELETE.
    К сожалению, сейчас проверить эту логику на практике не могу, но это первое что приходит на ум. Напишите удалось ли решить проблему.

  6. Дмитрий

    Антон. я пока начинающий, и триггер мне с нуля проблематично написать, вот и прошу у Вас помощи.
    Я хочу довести до конца свой проект, пока у меня получилось вот это:
    http://duvarov.dyndns.org
    Чтобы с датчтика тока (замер и запись раз в 2 секунды) база mySQL не забивалась значениями, которые по суди не нужны через 100 значений, вот и наткнулся на ваш сайт, где прочитал про триггеры…. и понял, что им можно решить свою проблему

  7. АнтонАнтон

    Дмитрий, написал вам на ваш контактный email

  8. Дмитрий

    Антон, ответил Вам на почту
    продублирую тут
    да, каждую 100 запись чтобы стиралась.
    тем самым у меня на графиках всегда будет “история” из 100 последних значений.

  9. АнтонАнтон

    Дмитрий, написал вам на почту. Письма от вас не доходят почему-то, поэтому выслал еще свой скайп.

  10. AleXandR

    Как откатить в mysql втавку значения в триггере before insert?

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">