DOC.PROTOTYPES.RU

Краткая сводка по триггерам в MySQL 5.1

Что это такое триггер?

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

• До или после вставки записи (INSERT);
• До или после обновления записи (UPDATE);
• До или после удаления записи (DELETE);

Синтаксис триггера

SQL код (1)

CREATE DEFINER = CURRENT_USER TRIGGER `[ имя триггера ]` [ BEFORE | AFTER ] [ INSERT | UPDATE | DELETE ] ON `[ имя таблицы ]` FOR EACH ROW BEGIN [ Тело триггера ] END;

Собственно, и все, ничего лишнего. Переменные не передаем, обратно ничего не возвращаем, кстати да, RETURN нет по определению, зато есть NEW и OLD, правда, не всегда

Подсмотреть созданный триггер можно следующим запросом:

SQL код (2)

SHOW TRIGGERS [ { FROM | IN } [database] ] [ LIKE 'pattern' | WHERE { условие } ];

Как ни странно LIKE сам по себе, а не в условии WHERE, впрочем, LIKE работает только для поиска соответсвия по именам таблиц в которых есть триггер. Уловие WHERE же позволяет искать практически по любым параметрам, а параметров много:

• Trigger - имя триггера;
• Event - событие, собственно это: INSERT, UDPATE или DELETE;
• Table - имя таблицы;
• Statement - Как бе говорит нам активирован ли триггер или нет, кстати, деактивировать я его без удаления так и не смог :-(;
• Timing - когда отрабатывает триггер до (BEFORE) или после (AFTER);
• ... - остальное, в общем-то, системные значения;

Удаление триггера:

SQL код (3)

DROP TRIGGER [ IF EXISTS ] [schema_name.]trigger_name

Обновление триггера ведется через DROP + CREATE.

До или после?

Теперь самое интересное, что и когда у нас есть во время исполнения триггера, а так же какие возможности или "не возможности" это дает:

• BEFORE INSERT;
• • NEW - ДА - в наличии;
  • • Возможность изменения - ДА - во время выполнения триггера можно менять значения полей вставляемой записи;
    • Значения полей по умолчанию - ДА - если во время вставки, значения поля не было установлено, но при этом имеет умолчательное значение, то оно будет изветно;
    • Автоинкрементные (обновляемые) поля - НЕТ - эти поля нам не известны, и вычислить их никак нельзя. MAX(id) + 1 - не всегда даст правильный ответ;
    • Целостность внешних ключей - ДА - учитывается, триггер не будет исполнен, если какая-то ошибка, но это, естественно, для InnoDB;
  • OLD - НЕТ - что логично;
  • Отменить вставку - ДА - можно остановить вставку записи, только очень аккуратно;
• AFTER INSERT;
• • NEW - ДА - в наличии;
  • • Возможность изменения - НЕТ - во время выполнения триггера менять значения полей уже нельзя, запись вставлена;
    • Значения полей по умолчанию - ДА - мы уже вставили запись, все поля нам известны;
    • Автоинкрементные (обновляемые) поля - ДА - эти поля нам тоже известны;
    • Целостность внешних ключей - ДА - учитывается, но, естественно, для InnoDB;
  • OLD - НЕТ - что логично;
  • Отменить вставку - НЕТ - запись уже вставлена и удалить мы её уже не можем;
• BEFORE UPDATE;
• • NEW - ДА - в наличии;
  • • Возможность изменения - ДА - во время выполнения триггера можно менять значения полей вставляемой записи;
    • Значения не переданых полей- ДА - если во время обновления, значение поля не было установлено, то подставляется значение из OLD;
    • Автоинкрементные (обновляемые) поля - НЕТ - тот же TIMESTAMP еще не известен;
    • Целостность внешних ключей - ДА - учитывается, триггер не будет исполнен, если какая-то ошибка, но это, естественно, для InnoDB;
  • OLD - ДА - в наличии;
  • Отменить обновление - ДА - можно остановить обновление записи, только очень аккуратно;
• AFTER UPDATE;
• • NEW - ДА - в наличии;
  • • Возможность изменения - НЕТ - Во время выполнения триггера менять значения полей уже нельзя, запись вставлена;
    • Значения не переданых полей- ДА - Если во время обновления, значение поля не было установлено, то подставляется значение из OLD;
    • Автоинкрементные (обновляемые) поля - ДА - Это поле нам уже известно;
  • OLD - ДА - в наличии;
  • Отменить обновление - НЕТ - запись уже обновлена и вернуть мы её уже не можем;
• BEFORE DELETE;
• • NEW - НЕТ - передавать в общем-то нечего;
  • OLD - ДА - в наличии;
  • • Значения полей- ДА - мы знаем, что удаляем;
  • Отменить удаление - ДА - можно остановить удаление записи;
• AFTER DELETE;
• • NEW - НЕТ - передавать в общем-то нечего;
  • OLD - ДА - в наличии;
  • Откатить запрос - НЕТ - запись уже удалена и вернуть мы её уже не можем;

Грабли

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

• Что бы создать триггер пользователь должен быть с привелегией Super, что приводит к определенным сложностям на стандартных хостинговых площадках;
• Во время выполнения триггера, таблица, к которой он привязан, залочена, соответсвенно изменения в эту таблицу вносить никак нельзя. Что само по себе несколько странно, даже при использовании InnoDB, казалось бы, транзакция есть же. Обойти это возможно, если использовать MyISAM таблицу в связке с MERGE таблицей (хотя я считаю это багом);
• В триггерах нет безусловного возврата (RETURN), то есть мы не можем в любой момент штатно остановить выполнение триггера, только если внедрим неявную ошибку, например: SET NEW = NULL;, но это тоже не совсем правильно;
• Я так и не нашел, как выводить сообщения в журнал клиента. Отладка триггера, буквально сложный процесс;

Резюме: на данном этапе использовать треггеры в MySQL желательно только для тривиальных задач, к более сложным задачам система не готова, слишком много ошибок и нестыковок.