Триггеры в MySQL

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

  • Да

    Голосов: 6 37,5%

  • Нет

    Голосов: 5 31,3%

  • Я не знаю что это

    Голосов: 5 31,3%
  • Всего проголосовало
    16
Статус
В этой теме нельзя размещать новые ответы.
S

Slayter

Гуру форума
Регистрация
8 Апр 2006
Сообщения
231
Реакции
55
Триггеры в 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

Для просмотра ссылки Войди или Зарегистрируйся.
 
тригеры использую наряду с процедурами.
у меня после внедрения тригер+процедура скорость обработки информации увеличилась в сотни раз.
более того, я уверен, что можно еще тюнинговать как код так и мускуль для поднятия скорости, но это уже другая песочница.
 
djeklu написал(а):
тригеры использую наряду с процедурами.
у меня после внедрения тригер+процедура скорость обработки информации увеличилась в сотни раз.
более того, я уверен, что можно еще тюнинговать как код так и мускуль для поднятия скорости, но это уже другая песочница.
Нажмите для раскрытия...

Ага, только непонятно, например, как работать если сервера баз данных два мастер и слэйв (сам ни разу не сталкивался, возможно само синхронизироваться будет).
 
Slayter написал(а):
Во-вторых, триггеры в MySQL могут создаваться только пользователем с привилегией SUPER, т.е. если Ваше приложение использует триггеры, то ни на одном, даже платном, хостинге оно работать не будет.
Нажмите для раскрытия...
А у Вас уже есть опыт их реального практического применения для mysql?
Я использовал триггеры в mssql, там это бывало очень полезно, а вот про mysql слышу впервые, и читая такие ограничения, начинаю сомневаться, а как их вообще использовать?
 
Точно также как и в MSSQL.
Только сам ты их сможешь создавать только на собственном железе или VPS.
В случае хостинга (хотя не на каждом пойдёт) необходимо будет обращаться в саппорт для их создания.
 
Kloster написал(а):
А у Вас уже есть опыт их реального практического применения для mysql?
Я использовал триггеры в mssql, там это бывало очень полезно, а вот про mysql слышу впервые, и читая такие ограничения, начинаю сомневаться, а как их вообще использовать?
Нажмите для раскрытия...

Перечитайте мою статью, там четко сказано где это стоит использовать, где использовал я.

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

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