Триггеры в mysql примеры

Триггеры в mysql примеры

Триггеры — это хранимые процедуры, которые выполняются при наступлении определённого события. Под событием подразумевается отправка определённого запроса. Например, при добавлении записи в таблицу триггер может быть вызван и будет выполнен определённый код.

Чтобы проверить, как работают триггеры, давайте создадим с Вами 2 таблицы. Первая таблица — это страны (countries):

  • id — уникальный идентификатор.
  • title — название страны.

Таблица с городами (cities):

  • id — уникальный идентификатор.
  • title — название города.
  • country_idid той страны, которой принадлежит данный город.

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

Такой триггер создаётся в MySQL следующим образом:

DELIMITER //
CREATE TRIGGER `delete_cities` BEFORE DELETE ON `countries`
FOR EACH ROW BEGIN
DELETE FROM `cities` WHERE `country_id`=OLD.`id`;
END

После создания триггера при удалении страны автоматически удалятся из другой таблицы все города, принадлежащие этой стране. Теперь разберём подробнее синтаксис создания триггера:

  • DELIMITER — разделитель, который необходимо указать, иначе на ";" будет выдаваться ошибка.
  • `delete_cities` — это просто имя триггера.
  • BEFORE DELETE — данная команда означает, что триггер будет срабатывать непосредственно перед удалением записи. Вместо BEFORE может быть AFTER, а вместо DELETE другое событие, например, INSERT или UPDATE.
  • `countries` — название таблицы, на которую устанавливается триггер.
  • FOR EACH ROW — начало команды, выполняемой при срабатывании триггера.
  • BEGIN и END — отдельный блок выполняемой команды.
  • OLD.`id` — обращение к полю `id` удаляемой записи из таблицы `countries`.

Сразу скажу, что здесь я показал простой пример. В реальности, код после FOR EACH ROW может быть не 2 строчки, а очень и очень большим. Там так же можно писать циклы, условия, различные сравнения и многое-многое другое.

Вот таким образом создаются триггеры в MySQL.

Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!

Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.

Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления

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

Порекомендуйте эту статью друзьям:

Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):

Она выглядит вот так:

  • BB-код ссылки для форумов (например, можете поставить её в подписи):
  • Комментарии ( 2 ):

    Спасибо, Михаил, всегда приятно узнавать что-нибудь новое.

    Здравствуйте Михаил Юрьевич! извините, может я невнимателен но есть ли статья про хранимые процедуры и функции вообще… и зачем они нужны? ЗЫ я вот разобрался в конечном счете — но попотеть пришлось 🙂 зато могу сам такую статью написать 😉

    Для добавления комментариев надо войти в систему.
    Если Вы ещё не зарегистрированы на сайте, то сначала зарегистрируйтесь.

    Copyright © 2010-2020 Русаков Михаил Юрьевич. Все права защищены.

    Очень может быть, что вы знаете, что такое триггер базы данных, хотя бы в общих терминах. Есть даже шанс, что вы знаете, что MySQL поддерживает триггеры и имеет практику работы с ними. Но скорее всего, что большинство из вас, даже вооруженные знаниями не представляют себе, какие преимущества скрывают триггеры MySQL. Этот инструмент должен быть у вас на вооружении, так как триггеры могут полностью изменить ваш способ работы с данными.

    Введение : что такое триггер

    “Не смотря на то, что приложения становятся все более и более сложными, мы можем абстрагировать уровень приложений для того, чтобы управлять ими и увеличивать удобство процесса разработки.”

    Для тех, кто не знает, триггер — это правило, которое помещается вами в таблицу, и при выполнении DELETE, UPDATE или INSERT совершает дополнительные действия. Например, мы можем делать запись в журнале об изменении. Но вместо написания двух отдельных запросов (один — для изменения данных, другой для внесения записи в журнал), можно написать триггер, который будет содержать правило: “Когда бы ни изменялась строка, создать новую строку в другой таблице, чтобы сообщить, что были сделаны изменения”. Такой подход создает некоторую избыточность в основном запросе, но теперь нет проходов двух разных пакетов до сервера вашей базы данных, чтобы выполнить два разных действия, что в целом способствует улучшению производительности.

    Читайте также:  Как обновить прошивку htc

    Триггеры были введены в MySQL начиная с версии 5.0.2. Синтаксис триггеров несколько чужероден. MySQL использует стандарт ANSI SQL:2003 для процедур и других функций. Если вы работаете с языками программирования, то понять его будет не сложно. Спецификация отсутствует в свободном доступе, поэтому мы постараемся использовать простые структуры и будем объяснять, что происходит в триггере. Будут использоваться такие же структуры, как и в любом языке программирования.

    Как уже упоминалось выше, триггеры выполняются как процедуры при событиях UPDATE, DELETE и INSERT. Они могут быть выполнены либо до либо после определения события. Таким образом Вы можете определить триггер, которые будет выполняться перед DELETE или после DELETE, и так далее. Это значит, что можно иметь один триггер, который выполнится до INSERT и совершенно другой, который выполнится после INSERT, что является весьма мощным инструментом.

    Начало: структура таблиц, инструменты и заметки

    В статье мы будем работать с выдуманной системой для корзины покупок, каждый элемент которой будет иметь цену. Структура данных будет проста, насколько это возможно с целью продемонстрировать процедуры работы с триггерами. Наименования таблиц и столбцов придуманы с целью облегчения понимания, а не для реальной работы. Также используется TIMESTAMPS для облегчения учебного процесса. Таблицы имеют имена carts, cart_items, cart_log, items, items_cost.

    Также будут использоваться очень простые запросы. Нет связи между переменными и не используется никакого ввода данных. Запросы подготавливались так, чтобы быть как можно более простыми и понятными для чтения.

    Для определения времени выполнения использовался Particle Tree PHP Quick Profiler. Для иллюстрации эффектов на базе данных использовался Chive. Chive предназначен только для MySQL 5+ и очень похож на PHPMyAdmin. Он имеет более выразительный интерфейс, но содержит значительно больше ошибок на текущий момент. Использование Chive обусловлено желанием представить более выразительные скрин шоты запросов.

    Вам также может понадобиться поменять разделитель MySQL при создании триггеров. Оригинальный разделитель MySQL — это ; , но так как мы будем использовать разделитель для добавленных запросов, то может понадобиться явно указать разделитель, чтобы создавать запросы из командной линии. При использование Chive нет необходимости менять разделитель.

    Чтобы изменить разделитель, нужно выполнить команду перед командой триггера:

    А после команды триггера надо ввести:

    Простой триггер : целостность данных

    Если Вы захотите выполнить даже незначительную нормализацию структуры базы данных, может получиться так, что нужно будет удалять источник основных данных, который имеет фрагменты, участвующие в общем потоке данных. Например, у вас может быть cart_id, который ссылается на две или три таблицы без внешних ключей, особенно при использовании механизма MyISAM, который их не поддерживает.

    Для такого случая раньше вы возможно выполняли следующие операции:

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

    Вернемся к триггерам. Создадим простой триггер, который при удалении корзины будет удалять все элементы корзины, которые имеют такой же cart_id:

    Очень простой синтаксис. Давайте разберем триггер подробно.

    Первая строка“CREATE TRIGGER `tutorial`.`before_delete_carts`”. Это команда для MySQL создать триггер для базы данных “tutorial”, который будет иметь имя “before_delete_carts”. Будем использовать схему имен для триггеров “Когда_Что_Таблица”.

    Вторая строка указывает для MySQL определение триггера “BEFORE DELETE ON `trigger_carts` FOR EACH ROW”. Мы говорим MySQL, что перед тем, как провести удаление из данной таблицы, для каждой строки нужно сделать что-то. Что нужно сделать, объясняется далее между BEGIN и END. “DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;” Для MySQL задается, что перед тем, как удалить из trigger_carts, нужно взять OLD.cart_id и также удалить из trigger_cart_items. Синтаксис OLD определяет переменную. Он будет обсуждаться в следующем разделе, где будут комбинироваться OLD и NEW.

    Преимущество использования триггера — целостность ваших данных перемещается с уровня логики на уровень данных, где она и должна быть. Также есть и некоторый прирост производительности системы.

    Один запрос с триггером:

    Как вы можете видеть, существует небольшой прирост производительности, которого следовало ожидать. База данных для примера использует тот же самый сервер, что и клиент. Но если сервер баз данных расположен в другом месте, то следует ожидать более значительной разницы, так как ко времени выполнения запросов добавится время передачи данных между серверами.Также нужно отметить, что первый раз триггер может выполняться значительно медленнее, чем в следующие разы.

    Читайте также:  Номер оператора теле2 тольятти

    Перемещение логики данных на уровень данных подобно тому, как задание стиля перемещается с уровня разметки на уровень презентации, что известно всему миру как CSS.

    Чудесный простой триггер : журналирование и аудит

    Следующий пример, который мы рассмотрим связан с журналированием событий. Например, мы хотим наблюдать за каждым товаром, который помещается в корзину. Возможно, мы хотим отслеживать рейтинг покупки товаров. Возможно, мы просто хотим иметь копию каждого товара, помещенного в корзину, не обязательно для продажи, а для анализа поведения покупателей. Какими бы ни были причины, давайте посмотрим на триггер INSERT, который открывает возможности для журналирования или аудита наших данных.

    До использования триггера, вероятно мы делали что-то похожее:

    Теперь мы можем создать очень простой триггер для процесса журналирования:

    Первая строка “CREATE TRIGGER `after_insert_cart_items`”. Для MySQL задается команда, создать триггер с именем “after_insert_cart_items”. Имя может быть “Foo”, или “BullWinkle” или какое-то другое, но лучше использовать ранее описанную схему имен триггера. Далее следует “AFTER INSERT ON `trigger_cart_items` FOR EACH ROW”. Снова мы говорим, что после того, как что-то будет вставлено в trigger_cart_items, для каждой строки нужно выполнить операции между BEGIN и END.

    Строка “INSERT INTO trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id);” является стандартным запросом с использованием двух переменных. Здесь используются значения NEW, которые вставляются в таблицу cart_items.

    Снова выполнение нашего запроса осуществляется быстрее:

    Для проверки, что триггер работает, посмотрим значения в таблице:

    Более сложный триггер : бизнес логика

    Начиная с этого момента мы перестанем рассматривать старый способ использования множественных запросов и их сравнение с техникой использования триггеров. Давайте рассмотрим несколько более продвинутых примеров использования триггеров.

    Бизнес логика — это место, где плодятся ошибки. Не смотря на осторожность и внимание к организации процесса, всегда что-то идет не так. Триггер для UPDATE позволяет несколько смягчить такое положение. У нас есть возможность в триггере вычислить значение OLD и установить значение NEW на основе оценки. Например, мы хотим всегда устанавливать цену на товар с 30% надбавкой к стоимости. Это приводит к тому, что когда мы изменяем (UPDATE) стоимость, мы должны изменить (UPDATE) цену. Давайте используем триггер.

    Мы изменяем таблицу товаров с ценами, основанными на NEW.cost * 1.3. Если ввести стоимость $50, то цена должна быть $65.

    Данный триггер работает отлично.

    Давайте рассмотрим более сложный пример. У нас уже есть правило, которое изменяет цену товара на основе стоимости. Теперь мы хотим установить некоторую ярусность в ценах. Если цена меньше $50, то актуальное значение будет $50. Если цена больше $50, но меньше $100, то актуальное значение будет $100.

    Для того, чтобы решить задачу, мы снова будем работать с UPDATE, но в этот раз триггер будет выполняться до выполнения запроса. Также будет использоваться выражение IF.

    Вот текст триггера:

    Это не запрос, а перекрытие значений. Если цена меньше $50, то устанавливаем ее $50. Если цена лежит между $50 и $100, то устанавливаем ее $100. Если она выше, то просто оставляем ее такой, какая она есть. Синтаксис не отличается от других серверных языков. Нужно закрыть выражение IF с помощью END IF.

    Проверим работу нашего триггера. Если ввести значение стоимости $30, то цена должна быть $50:

    Для значения стоимости $85:

    Для проверки того, что триггер AFTER UPDATE все еще работает, цена должна быть $130:

    Заключение

    В данной статье мы только слегка задели айсберг триггеров в MySQL. Они позволяют переносить правила работы с данными с уровня логики приложения на уровень данных.
    Может быть, использование триггеров на одностраничном сайте и является суетой, которая отнимает время и силы. Но сложные вэб приложения могут совершенно преобразиться при использовании триггеров.

    Данный урок подготовлен для вас командой сайта ruseller.com
    Источник урока: www.net.tutsplus.com
    Перевел: Сергей Фастунов
    Урок создан: 4 Июля 2010
    Просмотров: 128878
    Правила перепечатки

    5 последних уроков рубрики "Разное"

    Как разместить свой сайт на хостинге? Правильно выбранный хороший хостинг — это будущее Ваших сайтов

    Проект готов, Все проверено на локальном сервере OpenServer и можно переносить сайт на хостинг. Вот только какую компанию выбрать? Предлагаю рассмотреть хостинг fornex.com. Отличное место для твоего проекта с перспективами бурного роста.

    Читайте также:  Php запись в массив

    Разработка веб-сайтов с помощью онлайн платформы Wrike

    Создание вебсайта — процесс трудоёмкий, требующий слаженного взаимодействия между заказчиком и исполнителем, а также между всеми членами коллектива, вовлечёнными в проект. И в этом очень хорошее подспорье окажет онлайн платформа Wrike.

    20 ресурсов для прототипирования

    Подборка из нескольких десятков ресурсов для создания мокапов и прототипов.

    Топ 10 бесплатных хостингов

    Небольшая подборка провайдеров бесплатного хостинга с подробным описанием.

    Быстрая заметка: массовый UPDATE в MySQL

    Ни для кого не секрет как в MySQL реализовать массовый INSERT, а вот с UPDATE-ом могут возникнуть сложности. Чтобы не прибегать к манипуляциям события ON_DUPLICATE можно воспользоваться специальной конструкцией CASE … WHEN … THEN.

    Автор: admin | 14 июня (чт.) 2018г. в 12ч.10м.

    Что такое триггеры в Mysql.

    Планируем структуру базы данных.

    Создадим для примера базу данных для блога. Нам понадобится две таблицы:

    -`blog`: хранит уникальный идентификатор поста, заголовок, содержимое и флаг того, считается ли запись удаленной (на деле запись блога удаляться никогда не будет, а будет помечаться флагом, что запись считается удаленной или не удаленной).

    -`audit`: хранит базовый набор исторических изменений с идентификатором записи,
    идентификатором сообщения блога, типом изменения (NEW, EDIT или DELETE) и датой/временем этого изменения.

    Создадим таблицу `blog`:

    Далее напишем sql код для создания нашей таблицы `audit`. Нам нужно назначить для всех полей индексы и оприделить внешний ключ
    как audit.blog_id который ссылается на поле id из таблицы `blog`.

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

    Теперь мы создаем два триггера:
    Первый — когда новая запись создается в таблице `blog`, создаем для нее запись в таблице `audit`
    c `blog_id` соответствующей id новой записи блога и типом ‘NEW’ из набора enum(‘NEW’,’EDIT’,’DELETE’).

    Второй — когда запись в блоге обновляется, то добавляем запись в `audit` с типом ‘EDIT’ из набора enum(‘NEW’,’EDIT’,’DELETE’).
    Время при этом устанавливается автоматически, в момент создания записи.

    Основной синтаксис триггера:

    Итак, нам требуется два триггера AFTER INSERT и AFTER UPDATE на событие в таблице `blog`.
    Нет необходимости определять триггер DELETE, поскольку пост помечен как удаленный,
    установив для поля `deleted` значение true.

    Первая команда MySQL, которую мы напишем, это разделитель:
    DELIMITER $$

    Для нашего тела триггера требуется несколько команд SQL, разделенных точкой с запятой (;).
    Чтобы создать полный код триггера, мы должны изменить разделитель на что-то другое, например $.

    Создаем AFTER INSERT триггер. Тут мы вводим переменную @changetyp, которая будет хранить значение из
    enum(‘NEW’,’EDIT’,’DELETE’) в зависимости от условия, какое значение установлено в blog.deleted:

    На столбцы таблицы, к которой привязан триггер (в данном случае `blog`) можно ссылаться с помощью псевдонимов OLD и NEW.
    NEW — это вновь созданная таблица. Поэтому NEW.deleted содержит значение этого столбца при создании записи в `blog`.

    OLD.col_name — указывает на данные стрлбца до удаления или изменения данных при соответствующих событиях триггеров UPDATE/DELETE.

    Наконец, мы устанавливаем разделитель обратно в точку с запятой:
    DELIMITER ;

    Триггер AFTER UPDATE почти идентичен:

    Это выходит за рамки этой статьи, но вы можете рассмотреть возможность вызова единой хранимой
    процедуры, которая обрабатывает оба триггера.

    Проверка работы триггера.

    Новая запись появляется в таблице `blog`, как и следовало ожидать:

    Кроме того, в нашей таблице `audit` появляется новая запись:

    Давайте обновим наш текст в блоге:

    Помимо изменения поста, в таблице «audit» появляется новая запись:

    Наконец, давайте отметим пост как удаленный:

    Соответственно обновляется таблица "audit", и у нас есть запись о произошедших изменениях:

    Вот так. Все работает и триггеры отлично справляются со своей задачей.

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

    Приветствую!

    Меня зовут Сергей. Я — автор этого блога.

    Если Вам был полезен материал на моем сайте, поддержите пожалуйста мой проект, чтобы о нем узнали другие люди — кликните plizz 🙂 на иконку в соц. сети, чтобы поделиться материалом с другими.

    Ссылка на основную публикацию
    Тест для определения цвета волос
    Пожалуйста, не копируйте понравившиеся вам статьи незаконно. Мы предлагаем вам разместить активную ссылку на наш сайт в случае, если вы...
    Стим показывает что я не в сети
    Не редко пользователи Steam встречаются с проблемой, когда подключение к интернету есть, браузеры работают, но клиент Стим не грузит страницы...
    Стим саппорт украли аккаунт
    Если ваш аккаунт Steam украли или взломали, то до его восстановления вам необходимо выполнить действия, указанные ниже, иначе аккаунт может...
    Тест графики видеокарты 3dmark
    Наиболее известная программа тестирования производительности, ставшая де-факто стандартом и точкой отсчета в измерениях игровых возможностей видеокарт. Основную популярность программе обеспечило...
    Adblock detector