Триггеры в MySQL

Тема в разделе "Базы данных", создана пользователем Slayter, 20 июн 2009.

?

Вы используете триггеры?

  1. Да

    6 голосов
    37,5%
  2. Нет

    5 голосов
    31,3%
  3. Я не знаю что это

    5 голосов
    31,3%
Статус темы:
Закрыта.
Модераторы: latteo
  1. Slayter

    Slayter

    Регистр.:
    8 апр 2006
    Сообщения:
    232
    Симпатии:
    52
    Триггеры в MySQL

    Введение

    Триггер — это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных.

    События к которым можно привязывать триггеры в MySQL: INSERT, UPDATE, DELETE.

    Время, в которое будет исполнен триггер может быть: BEFORE (до наступления заданного события) или AFTER (после него).

    Ограничения (не все)

    Во-первых, триггеры появились в MySQL 5.0.2.

    Во-вторых, триггеры в MySQL могут создаваться только пользователем с привилегией SUPER, т.е. если Ваше приложение использует триггеры, то ни на одном, даже платном, хостинге оно работать не будет.

    Во-третьих, нельзя редактировать таблицу уже открытую для чтения или записи условием инициализации триггера. В этом случае просто используйте оператор SET для нужных полей.

    Код:
    CREATE TRIGGER `artists_litera_autoupdate` AFTER UPDATE ON `cms_artists`
       FOR EACH ROW BEGIN
          UPDATE `cms_artists` SET `litera` = UPPER(SUBSTRING(NEW.`name`, 1, 1)) WHERE `id` = OLD.`id`;
       END;
    В противном случае Вы получите такую ошибку:
    #1442 - Can't update table 'tablename' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.​
    Кроме того в показанном выше запросе есть ещё одна ошибка, которая после исправления предидущей обязательно появится:
    ERROR 1362 (HY000:( Updating of NEW row is not allowed in after trigger​
    В-четвёртых, значения надо менять до обновления.

    В конечном счёте правильный вариант:

    Код:
    CREATE TRIGGER `artists_litera_autoupdate` BEFORE UPDATE ON `cms_artists`
        FOR EACH ROW BEGIN
           SET NEW.`litera`= UPPER(SUBSTRING(NEW.`name`, 1, 1));
        END;
    В-пятых, при очищении таблицы (TRUNCATE) триггер, повешенный на событие DELETE, исполняться не будет.

    Синтаксис


    CREATE TRIGGER — даём понять MySQL'ю что мы от него хотим.
    `artists_litera_autoupdate` — имя создаваемого триггера.
    BEFORE — время его исполнения.
    UPDATE — событие, по наступлению которого, в указанное время, исполняется триггер.
    ON `cms_artists` — таблица, к которой привязывается триггер.
    FOR EACH ROW BEGIN — определяет выражения (до END;), которые будут применены к каждому ряду затронутому событием, к которому привязан триггер. Т.е. при запросе:

    Код:
    UPDATE `news` SET `views` = `views` + 1 WHERE `id` IN (1,2,3)
    Триггер, повешенный на обновление этой таблицы будет выполнен три раза, если количество записей, удовлетворяющих WHERE равно трём. Тоже самое верно и для множественных вставок:

    Код:
    INSERT INTO `posts` (`date`, `title`) VALUES (NOW(), 'Проверка раз'), (NOW(), 'Проверка два')
    В этом случае триггер, повешенный на INSERT для таблицы `posts` будет выполнен два раза.

    Пример из жизни


    Задача: чтобы максимально упростить SELECT запросы при выводе информации о записях из базы необходимо значение среднего рейтинга и количества голосов хранить в этой же таблице. При этом, как Вы сами прекрасно понимаете, за этими полями надо как-то следить, поддерживать информацию в них в актуальном состоянии.

    Как это делается обычно?

    Усложенение SELECT'а путём постоянного пересчёта количества голосов из таблицы `cms_videos_ratings` с помощью COUNT(), JOIN и GROUP BY.

    Но есть и другой путь — после INSERT'а в таблицу с рейтингом делают дополнительный запрос для получения нового значения среднего рейтинга. Затем поле `rating` в таблицу`videos` обновляют на новое значение, а поле `votes` = `votes` + 1. А ведь есть ещё и удаление рейтинга и его обновление, что добавит не одну лишнюю строчку кода в Ваше приложение. В этом случае происходит усложнение бизнес-логики приложения а так же создаётся лишняя нагрузка. Кто-то, конечно же, скажет, что это экономия на спичках, и опровергнуть я это не смогу, т.к. у меня нет никаких статистических данных а получать и анализировать мне просто некогда.

    Давайте просто вернёмся к началу статьи, к определению триггеров. Поскольку это комплириуемая SQL-процедура, то она будет исполняться быстрее интерпретируемого SQL-запроса, который до исполнения ещё пройдёт через Вашу обёртку для работы с базами данных (если таковая имеется), а затем через обёртку для работы с MySQL из php (например).

    Зачем вообще об этом задумываться, если это можно поручить MySQL?

    Код:
    DELIMITER $$
    CREATE TRIGGER `videos_rating_autoupdate` AFTER INSERT ON `cms_videos_ratings`
        FOR EACH ROW BEGIN
            SET @new_vid = NEW.`video_id`;
            SET @total_votes = (SELECT COUNT(`id`) FROM `cms_videos_ratings` WHERE `video_id` = @new_vid);
            SET @current_rating = (SELECT SUM(`rating`) / @total_votes FROM `cms_videos_ratings` WHERE `video_id` = @new_vid);
            UPDATE `cms_videos` SET `votes` = @total_votes, `rating` = @current_rating WHERE `id` = @new_vid;
        END;
    $$
    Аналогичное применение триггерам можно найти и для подсчета количества материалов, добавленных пользователем на ресурс и для подсчёта количества его комментариев, элементов в избранном и многих-многих других вещей.

    При использовании метода в лоб Ваше приложение покроется десятками лишних строк и здесь уже плевать на производительность — значительно усложняется восприятие Вашего кода.

    Важно знать


    Как получить список всех триггеров существующих в текущей базе данных?

    Код:
    SHOW TRIGGERS;

    Как удалить триггер из текущей базы данных?


    Код:
    DELETE TRIGGER `name`;
    После удаления таблицы, к которой привязан триггер, не стоит пытаться удалить его (триггер) — MySQL сделает это автоматически.

    У меня не создаётся триггер, я использую phpMyAdmin

    Сам столкнулся с такой проблемой. Искать причину её появления не было времени, поэтому рекомендую создавать через консоль:

    Код:
    root@Ubuntu-804:~# mysql -uПОЛЬЗОВАТЕЛЬ -pПАРОЛЬ ИМЯ_БАЗЫ_ДАННЫХ
    mysql> DELIMITER $$
    mysql> CREATE TRIGGER .....
        -> .....
        -> $$
    


    MySQL.com


    Кросспост из моего блога.
     
    SavaWSS, sultan347, ilyuzhko и ещё 1-му нравится это.
  2. djeklu

    djeklu Создатель

    Регистр.:
    12 янв 2007
    Сообщения:
    16
    Симпатии:
    0
    тригеры использую наряду с процедурами.
    у меня после внедрения тригер+процедура скорость обработки информации увеличилась в сотни раз.
    более того, я уверен, что можно еще тюнинговать как код так и мускуль для поднятия скорости, но это уже другая песочница.
     
  3. Slayter

    Slayter

    Регистр.:
    8 апр 2006
    Сообщения:
    232
    Симпатии:
    52
    Ага, только непонятно, например, как работать если сервера баз данных два мастер и слэйв (сам ни разу не сталкивался, возможно само синхронизироваться будет).
     
  4. Kloster

    Kloster

    Регистр.:
    22 июн 2009
    Сообщения:
    216
    Симпатии:
    12
    А у Вас уже есть опыт их реального практического применения для mysql?
    Я использовал триггеры в mssql, там это бывало очень полезно, а вот про mysql слышу впервые, и читая такие ограничения, начинаю сомневаться, а как их вообще использовать?
     
  5. PHP_Master

    PHP_Master

    Регистр.:
    3 фев 2008
    Сообщения:
    2.647
    Симпатии:
    590
    Точно также как и в MSSQL.
    Только сам ты их сможешь создавать только на собственном железе или VPS.
    В случае хостинга (хотя не на каждом пойдёт) необходимо будет обращаться в саппорт для их создания.
     
  6. Slayter

    Slayter

    Регистр.:
    8 апр 2006
    Сообщения:
    232
    Симпатии:
    52
    Перечитайте мою статью, там четко сказано где это стоит использовать, где использовал я.

    Ограничения же эти мне не совсем понятны - ведь для use dbname; необходимы определённые полномочия, в этом случае никаких соображений безопасности в данном ограничении я не вижу (хотя они разумеется есть, иначе бы этого органичения не было).

    В целом же мои разработки уже давно не запускают на хостингах, потому что, как правило, таких мелких заказов у меня нет, так что для меня этого ограничения не существует. Да и вообще железо стоит в десятки раз меньше труда квалифицированного специалиста, так что с людьми, экономящими даже на пятикопеечных VPS'ах лучше не связываться - они мудаки и пидересты.
     
  7. PHP_Master

    PHP_Master

    Регистр.:
    3 фев 2008
    Сообщения:
    2.647
    Симпатии:
    590
    На шареде есть проблемы и с вьюхами (что вообще мне непонятно), что уж тут говорить о триггерах и процедурах.
     
  8. black.cat

    black.cat

    Регистр.:
    13 ноя 2008
    Сообщения:
    203
    Симпатии:
    32
    подскажите можно ли с помощью триггеров решить следующую проблему многопользовательской работы с mysql:
    Есть большая таблица, необходимо организовать
    одновременное редактирование разных строк пользователями без потерь данных (т.е. блокировка на уровне строк)
    Но с этим типом блокировки не все так красиво - если
    пользователь заблокировал строку а потом не разблокировав
    ее АВАРИЙНО(!) завершил соединение не сделав disconnect
    то получается лажа в виде навечно (до перрезагрузки демона)
    заблоченой строчки.
    Приходится както пинговать клиентов что влечет за собой кучу гемора (поконцовке проект вобще заглох именно изза этого голшовняка)
     
  9. sultan347

    sultan347

    Регистр.:
    25 апр 2009
    Сообщения:
    230
    Симпатии:
    7
    Не получается мне создать ознокомительный триггер, зашел под рутом, вот:
    Код:
    mysql> CREATE TRIGGER `trigger` AFTER INSERT ON `table1` FOR EACH ROW INSERT INTO `table2` SET `sid`='1';
    ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation
    
    В чем дело, настройки сервера?
     
  10. gentoouser

    gentoouser

    Регистр.:
    8 окт 2007
    Сообщения:
    187
    Симпатии:
    63
    эти всё сказано. mysql пользователь которым ты пытаешься создать триггер не имеет на это прав
     
Статус темы:
Закрыта.