Как использовать триггеры в MySQL

Автор выбрал программу Write for DOnations.

Введение

При работе с реляционными базами данных и UPDATE.

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

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

Предпосылки

Чтобы следовать этому руководству, вам понадобится компьютер с системой управления реляционными базами данных (RDBMS) на основе SQL. Инструкции и примеры в этом руководстве были проверены с использованием следующей среды:

• Сервер под управлением Ubuntu 20.04, пользователь без полномочий root с правами администратора и брандмауэр, настроенный с помощью UFW, как описано в нашем руководстве по первоначальной настройке сервера для Ubuntu 20.04.
• MySQL установлен и защищен на сервере, как описано в шаге 3.
• Основные знания о выполнении запросов SELECT, INSERT, UPDATE и DELETE для управления данными в базе данных как описано в наших руководствах по удалению данных в SQL.
• Основные сведения об использовании вложенных запросов, как описано в нашем руководстве «Использование вложенных запросов в SQL».
• Основные знания об использовании агрегатных математических функций, как описано в нашем руководстве «Использование математических выражений и агрегатных функций в SQL».

Примечание. Многие СУБД используют собственную реализацию SQL. Хотя триггеры упоминаются как часть стандарта SQL, стандарт не навязывает их синтаксис или строгий способ их реализации. В результате их реализация различается в разных базах данных. Команды, описанные в этом руководстве, используют синтаксис для базы данных MySQL и могут не работать в других ядрах баз данных.

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

Подключение к MySQL и настройка образца базы данных

В этом разделе вы подключитесь к серверу MySQL и создадите образец базы данных, чтобы следовать примерам в следующих разделах.

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

Если ваша система базы данных SQL работает на удаленном сервере, подключитесь к серверу по SSH с локального компьютера:

ssh sammy@your_server_ip

Затем откройте приглашение сервера MySQL, заменив sammy именем вашей учетной записи пользователя MySQL:

mysql -u sammy -p

Создайте базу данных с именем collectibles:

CREATE DATABASE collectibles;

Если база данных была создана успешно, вы получите такой вывод:

Output
Query OK, 1 row affected (0.01 sec)

Чтобы выбрать базу данных collectibles, выполните следующую инструкцию USE:

USE collectibles;

Вы получите следующий вывод:

Output
Database changed

После выбора базы данных вы можете создать в ней образцы таблиц. Таблица collectibles будет содержать упрощенные данные о предметах коллекционирования в базе данных. Он будет содержать следующие столбцы:

• name: в этом столбце содержится название каждого предмета коллекционирования, выраженное с использованием типа данных varchar с максимальным количеством 50 символов.
• value: в этом столбце хранится рыночная стоимость предмета коллекционирования с использованием типа данных decimal с максимальным значением 5 до десятичной точки и 2 значения после него.

Создайте образец таблицы с помощью следующей команды:

CREATE TABLE collectibles (
    name varchar(50),
    value decimal(5, 2)
);

Если выводятся следующие выходные данные, таблица создана:

Output
Query OK, 0 rows affected (0.00 sec)

Следующая таблица будет называться collectibles_stats и будет использоваться для отслеживания накопленной стоимости всех предметов коллекционирования в коллекции. Он будет содержать одну строку данных со следующими столбцами:

• count: в этом столбце содержится количество принадлежащих вам предметов коллекционирования, выраженное с использованием типа данных int.
• value: в этом столбце хранится совокупная стоимость всех предметов коллекционирования с использованием типа данных decimal с максимальным значением 5 до десятичной точки. и значения 2 после него.

Создайте образец таблицы с помощью следующей команды:

CREATE TABLE collectibles_stats (
    count int,
    value decimal(5, 2)
);

Если выводятся следующие выходные данные, таблица создана:

Output
Query OK, 0 rows affected (0.00 sec)

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

• name: в этом столбце содержится имя каждого удаленного предмета коллекционирования, выраженное с использованием типа данных varchar с максимальным числом символов 50.< /li>
• value: в этом столбце хранится рыночная стоимость предмета коллекционирования на момент удаления с использованием типа данных decimal с максимальным значением 5 до десятичная точка и значения 2 после нее.
• removed_on: в этом столбце хранится дата и время удаления каждого заархивированного предмета коллекционирования с использованием типа данных timestamp со значением по умолчанию NOW(), что означает текущую дату всякий раз, когда в эту таблицу вставляется новая строка.

Создайте образец таблицы с помощью следующей команды:

CREATE TABLE collectibles_archive (
    name varchar(50),
    value decimal(5, 2),
    removed_on timestamp DEFAULT CURRENT_TIMESTAMP
);

Если выводятся следующие выходные данные, таблица создана:

Output
Query OK, 0 rows affected (0.00 sec)

После этого загрузите таблицу collectibles_stats с начальным состоянием для пустой коллекции предметов коллекционирования, выполнив следующую операцию INSERT INTO:

INSERT INTO collectibles_stats SELECT COUNT(name), SUM(value) FROM collectibles;

Операция INSERT INTO добавит одну строку в таблицу collectibles_stats со значениями, рассчитанными с использованием агрегатных функций для подсчета всех строк в таблице collectibles и чтобы суммировать стоимость всех предметов коллекционирования, используя столбец value и функцию SUM. Следующий вывод показывает, что строка была добавлена:

Output
Query OK, 1 row affected (0.002 sec)
Records: 1  Duplicates: 0  Warnings: 0

Вы можете убедиться в этом, выполнив оператор SELECT для таблицы:

SELECT * FROM collectibles_stats;

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

Output
+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

После этого вы готовы следовать остальной части руководства и начать использовать триггеры в MySQL.

Понимание триггеров

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

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

• Событие базы данных: триггер может выполняться, когда в таблице выполняются операторы INSERT, UPDATE или DELETE.
• Время события. Кроме того, триггеры могут выполняться ДО или ПОСЛЕ рассматриваемого оператора.

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

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

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

Данные для использования в теле триггера представлены в записи OLD для данных, находящихся в настоящее время в базе данных, и в записи NEW для данных, которые запрос сохранит. Вы можете ссылаться на отдельные столбцы, используя синтаксис OLD.имя_столбца и NEW.имя_столбца.

В следующем примере показан общий синтаксис инструкции SQL, используемой для создания нового триггера:

CREATE TRIGGER trigger_name trigger_condition
ON table_name
FOR EACH ROW
trigger_actions;

Давайте разберем синтаксис на более мелкие части:

• CREATE TRIGGER – это имя оператора SQL, используемого для создания нового триггера в базе данных.
• имя_триггера – это определяемое пользователем имя триггера, используемое для описания его роли, аналогично тому, как имена таблиц и столбцов используются для описания их значения.< /li>
• ON table_name сообщает базе данных, что триггер должен отслеживать события, происходящие в таблице table_name.
• trigger_condition — это один из шести возможных вариантов, определяющих, когда должен запускаться триггер, например, BEFORE INSERT.
• ДЛЯ КАЖДОЙ СТРОКИ указывает базе данных, что триггер должен запускаться для каждой строки, затронутой событием триггера. Некоторые базы данных поддерживают дополнительные шаблоны выполнения, отличные от FOR EACH ROW; однако в случае с MySQL запуск операторов из тела триггера для каждой строки, затронутой оператором, вызвавшим выполнение триггера, является единственным вариантом.
• trigger_actions — это тело триггера, определяющее, что происходит при выполнении триггера. Обычно это один допустимый оператор SQL. В тело триггера можно включить несколько операторов для выполнения сложных операций с данными, используя ключевые слова BEGIN и END, чтобы заключить список операторов в блок. Однако это выходит за рамки данного руководства. Ознакомьтесь с официальной документацией по триггерам, чтобы узнать больше о синтаксисе, используемом для определения триггеров.

В следующем разделе вы создадите триггеры, которые обрабатывают данные перед операциями INSERT и UPDATE.

Управление данными с помощью триггеров BEFORE INSERT и BEFORE UPDATE

В этом разделе вы будете использовать триггеры для управления данными перед выполнением операторов INSERT и UPDATE.

В этом примере вы будете использовать триггеры, чтобы убедиться, что все предметы коллекционирования в базе данных используют имена в верхнем регистре для согласованности. Без использования триггеров вам пришлось бы не забывать использовать собираемые имена в верхнем регистре для каждого оператора INSERT и UPDATE. Если вы забудете, база данных сохранит данные как есть, что может привести к ошибкам в наборе данных.

Вы начнете с вставки примера коллекционного предмета под названием модель космического корабля стоимостью 12,50 долларов США. Имя элемента будет написано строчными буквами, чтобы проиллюстрировать проблему. Выполните следующую инструкцию:

INSERT INTO collectibles VALUES ('spaceship model', 12.50);

Следующее сообщение подтверждает, что элемент был добавлен:

Output
Query OK, 1 row affected (0.009 sec)

Вы можете убедиться, что строка была вставлена, выполнив запрос SELECT:

SELECT * FROM collectibles;

Следующий вывод будет напечатан на экране:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
+-----------------+-------+
1 row in set (0.000 sec)

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

Чтобы гарантировать, что все будущие предметы коллекционирования всегда будут написаны в верхнем регистре, вы создадите триггер BEFORE INSERT. Использование триггера, который выполняется до запуска триггерного оператора, позволяет вам манипулировать данными, которые будут переданы в базу данных до того, как это произойдет.

Запустите следующую инструкцию:

CREATE TRIGGER uppercase_before_insert BEFORE INSERT
ON collectibles
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);

Эта команда создает триггер с именем uppercase_before_insert, который будет выполняться ДО всех операторов INSERT в таблице с именем collectibles.

Оператор в триггере SET NEW.name=UPPER(NEW.name) будет выполняться для каждой вставленной строки. Команда SQL SET присваивает значение с правой стороны левой стороне. В этом случае NEW.name представляет значение столбца name, которое будет сохранено оператором вставки. Применяя функцию UPPER к коллекционному имени и присваивая его обратно значению столбца, вы преобразуете регистр букв значения, которое будет сохранено в базе данных.

Примечание. При выполнении команды CREATE TRIGGER может появиться сообщение об ошибке, аналогичное ОШИБКЕ 1419 (HY000): У вас нет привилегии SUPER, а ведение двоичного журнала включено (вы * хотите использовать менее безопасную переменную log_bin_trust_function_creators).

Начиная с MySQL 8, в механизме базы данных MySQL по умолчанию включено ведение двоичного журнала, если только локальная конфигурация установки не отменяет это. Двоичный журнал отслеживает все операторы SQL, которые изменяют содержимое базы данных, в виде сохраненных событий, описывающих изменения. Эти журналы используются при репликации базы данных для синхронизации реплик базы данных и во время восстановления данных на определенный момент времени.

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

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

Чтобы переопределить настройку по умолчанию для двоичного ведения журнала, войдите в MySQL как root и выполните следующую команду:

SET GLOBAL log_bin_trust_function_creators = 1;

Параметр log_bin_trust_function_creators определяет, можно ли доверять пользователям, создающим триггеры и хранимые функции, не создавать триггеры, приводящие к записи небезопасных событий в двоичный журнал. По умолчанию значение параметра равно 0, что позволяет только суперпользователям создавать триггеры в среде с включенным двоичным ведением журнала. Изменив значение на 1, любой пользователь, выполняющий операторы CREATE TRIGGER, будет уверен, что поймет последствия.

После обновления параметра выйдите из системы как пользователь root, снова войдите в систему как пользователь и повторно запустите оператор CREATE TRIGGER.

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

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

Примечание. В зависимости от ваших прав пользователя MySQL вы можете получить сообщение об ошибке при выполнении команды CREATE TRIGGER: ОШИБКА 1142 (42000): команда TRIGGER запрещена для пользователя user@host для коллекционных предметов. Чтобы предоставить разрешения TRIGGER своему пользователю, войдите в MySQL как пользователь root и выполните следующие команды, при необходимости заменив имя пользователя и хост MySQL:

GRANT TRIGGER on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

После обновления разрешений пользователя выйдите из системы как пользователь root, снова войдите в систему как пользователь и повторно запустите оператор CREATE TRIGGER.

MySQL напечатает следующее сообщение, подтверждающее успешное создание триггера:

Output
Query OK, 1 row affected (0.009 sec)

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

INSERT INTO collectibles VALUES ('aircraft model', 10.00);

И еще раз проверьте получившиеся строки в таблице collectibles:

SELECT * FROM collectibles;

Следующий вывод будет напечатан на экране:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
| AIRCRAFT MODEL  | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

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

Все новые строки теперь защищены триггером, чтобы гарантировать, что имена будут сохранены в верхнем регистре. Однако по-прежнему можно сохранять неограниченные данные с помощью операторов UPDATE. Чтобы защитить операторы UPDATE с тем же эффектом, создайте еще один триггер:

CREATE TRIGGER uppercase_before_update BEFORE UPDATE
ON collectibles
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);

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

MySQL выведет подтверждение того, что триггер был успешно создан:

Output
Query OK, 0 row affected (0.009 sec)

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

UPDATE collectibles SET value = 15.00 WHERE name = 'spaceship model';

Предложение WHERE фильтрует обновляемую строку по имени, а предложение SET изменяет значение на 15.00.

Вы получите следующий вывод, подтверждающий, что инструкция изменила одну строку:

Output
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Проверьте полученные строки в таблице collectibles:

SELECT * FROM collectibles;

Следующий вывод будет напечатан на экране:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
| AIRCRAFT MODEL  | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Теперь, в дополнение к обновлению цены до 15.00 выполненным оператором, в названии теперь написано МОДЕЛЬ КОСМИЧЕСКОГО КОРАБЛЯ. Когда вы запускали оператор UPDATE, срабатывал триггер, влияющий на значения в обновленной строке. Перед сохранением столбец имени был преобразован в верхний регистр.

В этом разделе вы создали два триггера, работающих перед запросами INSERT и перед запросами UPDATE для согласования данных перед их сохранением в базе данных. В следующем разделе вы будете использовать триггеры BEFORE DELETE для копирования удаленных строк в отдельную таблицу для архивирования.

Использование триггеров BEFORE DELETE для выполнения действий перед удалением строк

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

Проверьте, полностью ли пуста архивная таблица, выполнив следующую инструкцию:

SELECT * FROM collectibles_archive;

На экран будет выведен следующий вывод, подтверждающий, что таблица collectibles_archive пуста:

Output
Empty set (0.000 sec)

Теперь, если вы выполните запрос DELETE для таблицы collectibles, любая строка из таблицы может быть удалена без следа.

Чтобы исправить это, вы создадите триггер, который будет выполняться перед всеми запросами DELETE в таблице collectibles. Цель этого триггера — сохранить копию удаленного объекта в архивной таблице до того, как произойдет удаление.

Выполните следующую команду:

CREATE TRIGGER archive_before_delete BEFORE DELETE
ON collectibles
FOR EACH ROW
INSERT INTO collectibles_archive (name, value) VALUES (OLD.name, OLD.value);

Триггер называется archive_before_delete и срабатывает ДО любых запросов DELETE в таблице collectibles . Для каждой удаляемой строки будет выполняться оператор INSERT. В свою очередь, оператор INSERT вставляет новую строку в таблицу collectibles_archive со значениями данных, взятыми из записи OLD, которая предназначена для удаление: OLD.name становится столбцом name, а OLD.value становится столбцом value.

База данных подтвердит создание триггера:

Output
Query OK, 0 row affected (0.009 sec)

С помощью триггера попробуйте удалить предмет коллекционирования из основной таблицы предметов коллекционирования:

DELETE FROM collectibles WHERE name = 'SPACESHIP MODEL';

Вывод подтверждает, что запрос выполнен успешно:

Output
Query OK, 1 row affected (0.004 sec)

Теперь перечислите все предметы коллекционирования:

SELECT * FROM collectibles;

Следующий вывод будет напечатан на экране:

Output
+----------------+-------+
| name           | value |
+----------------+-------+
| AIRCRAFT MODEL | 10.00 |
+----------------+-------+
1 row in set (0.000 sec)

Теперь остается только МОДЕЛЬ САМОЛЕТА; МОДЕЛЬ КОСМИЧЕСКОГО КОРАБЛЯ был удален и больше не находится в таблице. Однако при ранее созданном триггере это удаление должно быть зарегистрировано в таблице collectibles_archive. Давайте проверим это.

Выполните другой запрос:

SELECT * FROM collectibles_archive;

Следующий вывод будет напечатан на экране:

Output
+-----------------+-------+---------------------+
| name            | value | removed_on          |
+-----------------+-------+---------------------+
| SPACESHIP MODEL | 15.00 | 2022-11-20 11:32:01 |
+-----------------+-------+---------------------+
1 row in set (0.000 sec)

Удаление было автоматически отмечено в этой таблице триггером. Столбцы name и value заполнены данными из удаленной строки. Третий столбец, removed_on, не задается явным образом с помощью определенного триггера, поэтому он принимает значение по умолчанию, заданное при создании таблицы: дату создания любой новой строки. Из-за этого каждая запись, добавленная с помощью триггера, всегда будет аннотирована датой удаления.

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

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

Использование триггеров AFTER INSERT, AFTER UPDATE и AFTER DELETE для выполнения действий после обработки данных

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

Начните с изучения таблицы collectibles_stats:

SELECT * FROM collectibles_stats;

Поскольку вы еще не добавили информацию в эту таблицу, количество имеющихся коллекционных предметов равно 0, и, таким образом, накопленное значение равно NULL:

Output
+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

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

Цель состоит в том, чтобы установить значения в одной строке в таблице collectibles_stats, чтобы представить актуальную информацию о количестве и общей стоимости предметов коллекционирования. Вы хотите, чтобы содержимое таблицы обновлялось после каждой операции INSERT, UPDATE или DELETE.

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

CREATE TRIGGER stats_after_insert AFTER INSERT
ON collectibles
FOR EACH ROW
UPDATE collectibles_stats
SET count = (
    SELECT COUNT(name) FROM collectibles
), value = (
    SELECT SUM(value) FROM collectibles
);

Триггер называется stats_after_insert и будет выполняться ПОСЛЕ каждого запроса INSERT к предметам коллекционирования. таблица, выполняющая оператор UPDATE в теле триггера. Запрос UPDATE влияет на collectibles_stats и устанавливает для столбцов count и value значения, возвращаемые вложенными запросами:

• ВЫБРАТЬ СЧЕТЧИК(имя) ИЗ предметов коллекционирования получит количество предметов коллекционирования.
• ВЫБРАТЬ СУММУ(значение) ИЗ предметов коллекционирования будет получена общая стоимость всех предметов коллекционирования.

База данных подтвердит создание триггера:

Output
Query OK, 0 row affected (0.009 sec)

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

INSERT INTO collectibles VALUES ('spaceship model', 15.00);

База данных напечатает следующее сообщение об успешном завершении:

Output
Query OK, 1 row affected (0.009 sec)

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

SELECT * FROM collectibles;

Следующий вывод будет напечатан на экране:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| AIRCRAFT MODEL  | 10.00 |
| SPACESHIP MODEL | 15.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Всего есть два коллекционных предмета стоимостью 25.00. Чтобы просмотреть сводную таблицу после вновь вставленного элемента, выполните следующий запрос:

SELECT * FROM collectibles_stats;

На этот раз в сводной таблице будет указано количество всех находящихся в собственности коллекционных предметов как 2 и накопленное значение как 25,00, что соответствует предыдущему результату:

Output
+-------+-------+
| count | value |
+-------+-------+
|     2 | 25.00 |
+-------+-------+
1 row in set (0.000 sec)

Триггер stats_after_insert запускается после запроса INSERT и обновляет таблицу collectibles_stats текущими данными (count и value) о коллекции. Статистика собирается по всему содержимому коллекции, а не только по последней вставке. Поскольку коллекция теперь содержит два элемента (модели самолетов и космических кораблей), в сводной таблице перечислены два элемента и их суммарная стоимость. На этом этапе добавление любого нового предмета коллекционирования в таблицу предметов коллекционирования обновит сводную таблицу правильными значениями.

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

CREATE TRIGGER stats_after_update AFTER UPDATE
ON collectibles
FOR EACH ROW
UPDATE collectibles_stats
SET count = (
    SELECT COUNT(name) FROM collectibles
), value = (
    SELECT SUM(value) FROM collectibles
);

CREATE TRIGGER stats_after_delete AFTER DELETE
ON collectibles
FOR EACH ROW
UPDATE collectibles_stats
SET count = (
    SELECT COUNT(name) FROM collectibles
), value = (
    SELECT SUM(value) FROM collectibles
);

Вы создали два новых триггера: stats_after_update и stats_after_delete. Оба триггера будут запускаться в таблице collectible_stats всякий раз, когда вы запускаете инструкцию UPDATE или DELETE в таблице collectibles.

При успешном создании этих триггеров будет напечатан следующий вывод:

Output
Query OK, 0 row affected (0.009 sec)

Теперь обновите значение цены для одного из предметов коллекционирования:

UPDATE collectibles SET value = 25.00 WHERE name = 'AIRCRAFT MODEL';

Предложение WHERE фильтрует обновляемую строку по имени, а предложение SET изменяет значение на 25,00.

Вывод подтверждает, что инструкция изменила одну строку:

Output
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Еще раз проверьте содержимое сводной таблицы после обновления:

SELECT * FROM collectibles_stats;

В value теперь указано 40,00, что является правильным значением после обновления:

Output
+-------+-------+
| count | value |
+-------+-------+
|     2 | 40.00 |
+-------+-------+
1 row in set (0.000 sec)

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

DELETE FROM collectibles WHERE name = 'AIRCRAFT MODEL';

Следующий вывод подтверждает успешное выполнение запроса:

Output
Query OK, 1 row affected (0.004 sec)

Теперь перечислите все предметы коллекционирования:

SELECT * FROM collectibles;

Следующий вывод будет напечатан на экране:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
+-----------------+-------+
1 row in set (0.000 sec)

Теперь остается только МОДЕЛЯ КОСМИЧЕСКОГО КОРАБЛЯ. Далее проверяем значения в сводной таблице:

SELECT * FROM collectibles_stats;

Будет напечатан следующий вывод:

Output
+-------+-------+
| count | value |
+-------+-------+
|     1 | 15.00 |
+-------+-------+
1 row in set (0.000 sec)

В столбце count теперь отображается только один предмет коллекционирования в основной таблице. Общее значение равно 15,00, что соответствует значению МОДЕЛИ КОСМИЧЕСКОГО КОРАБЛЯ.

Эти три триггера работают вместе после запросов INSERT, UPDATE и DELETE, чтобы синхронизировать сводную таблицу с полным списком предметов коллекционирования.

В следующем разделе вы узнаете, как манипулировать существующими триггерами в базе данных.

Список и удаление триггеров

В предыдущих разделах вы создали новые триггеры. Поскольку триггеры — это именованные объекты, определенные в базе данных, как и таблицы, вы также можете перечислить их и манипулировать ими при необходимости.

Чтобы вывести список всех триггеров, выполните оператор SHOW TRIGGERS:

SHOW TRIGGERS;

Вывод будет включать в себя все триггеры, включая их имена, событие запуска с указанием времени (ДО или ПОСЛЕ выполнения оператора), а также операторы, которые являются частью тела триггера и другие подробные сведения об определении триггера:

Output, simplified for readability
+-------------------------+--------+--------------+--------(...)+--------+(...)
| Trigger                 | Event  | Table        | Statement   | Timing |(...)
+-------------------------+--------+--------------+--------(...)+--------+(...)
| uppercase_before_insert | INSERT | collectibles | SET    (...)| BEFORE |(...)
| stats_after_insert      | INSERT | collectibles | UPDATE (...)| AFTER  |(...)
| uppercase_before_update | UPDATE | collectibles | SET    (...)| BEFORE |(...)
| stats_after_update      | UPDATE | collectibles | UPDATE (...)| AFTER  |(...)
| archive_before_delete   | DELETE | collectibles | INSERT (...)| BEFORE |(...)
| stats_after_delete      | DELETE | collectibles | UPDATE (...)| AFTER  |(...)
+-------------------------+--------+--------------+--------(...)+--------+(...)
6 rows in set (0.001 sec)

Чтобы удалить существующие триггеры, вы можете использовать операторы SQL DROP TRIGGER. Возможно, вы больше не хотите использовать прописные буквы для коллекционных имен, поэтому uppercase_before_insert и uppercase_before_update больше не нужны. Выполните следующие команды, чтобы удалить эти два триггера:

DROP TRIGGER uppercase_before_insert;
DROP TRIGGER uppercase_before_update;

Для обеих команд MySQL ответит сообщением об успехе:

Output
Query OK, 0 rows affected (0.004 sec)

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

INSERT INTO collectibles VALUES ('ship model', 10.00);

База подтвердит вставку:

Output
Query OK, 1 row affected (0.009 sec)

Вы можете убедиться, что строка была вставлена, выполнив запрос SELECT:

SELECT * FROM collectibles;

Следующий вывод будет напечатан на экране:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
| ship model      | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

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

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

Заключение

Следуя этому руководству, вы узнали, что такое триггеры SQL и как использовать их в MySQL для управления данными перед запросами INSERT и UPDATE. Вы узнали, как использовать триггер BEFORE DELETE для архивирования удаленной строки в отдельную таблицу, а также как использовать триггеры операторов AFTER для постоянного обновления сводок.

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

Если вы хотите узнать больше о различных концепциях языка SQL и работе с ним, мы рекомендуем вам ознакомиться с другими руководствами из серии «Как использовать SQL».