Как изменить триггер mysql - Исправление ошибок и поиск оптимальных решений проблем

In mysql I can create a trigger, and then show information about it like this:

mysql> show triggers like 'fooTrigger';

This command gives output that looks an awful lot like a select statement, with a row showing the matching trigger. Is it possible to update a column on the row it shows me?

For example, one column is named Statement, and it defines what happens when the trigger is activated. Is it possible to change the Statement field for fooTrigger so the trigger does something different? Or do I need to drop and re-create the trigger?

asked Jul 17, 2012 at 15:34

Cory KleinCory Klein

49k41 gold badges179 silver badges241 bronze badges

As of MySQL 5.5, you must drop and re-create the trigger.
It is not possible to update the Statement column without dropping the trigger.

Documentation: CREATE TRIGGER DROP TRIGGER

You may also access information about triggers using the INFORMATION_SCHEMA tables:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS

But, as these tables are actually views, you can’t use UPDATE on them.
It’s just a more convenient way to access and manipulate the information than using SHOW TRIGGERS.

Documentation: INFORMATION_SCHEMA.TRIGGERS

answered Jul 17, 2012 at 16:13

You may require, on Windows OS, Connector/Net Visual Studio Integration to view and edit existing database object. Limitation is that you can only edit trigger body within For each row ... loop.

Otherwise only option one has is drop and re create the trigger.
But make sure before dropping a trigger that the table associated with a trigger is locked and
and unlocked after trigger is re-created.

answered Jul 17, 2012 at 16:19

Ravinder ReddyRavinder Reddy

23.4k6 gold badges51 silver badges81 bronze badges

As @Jocelyn mentioned you can’t alter the trigger. But if you’re using MySql Workbench it will allow you to alter the trigger. Just right click on your table name and click Alter table option from there you can pick Trigger option and alter it. Although you cannot perform it from query.
Table Name —> Alter Table —> Triggers.

Dharman♦

29.3k21 gold badges80 silver badges131 bronze badges

answered Jun 23, 2021 at 0:24

Источник

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

Синтаксис создания триггера:

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt* This source code was highlighted with Source Code Highlighter.

trigger_name — название триггера
trigger_time — Время срабатывания триггера. BEFORE — перед событием. AFTER — после события.
trigger_event — Событие:
insert — событие возбуждается операторами insert, data load, replace
update — событие возбуждается оператором update
delete — событие возбуждается операторами delete, replace. Операторы DROP TABLE и TRUNCATE не активируют выполнение триггера
tbl_name — название таблицы
trigger_stmt выражение, которое выполняется при активации триггера

Применение

Лог

Исходные данные:

— таблица, за которой мы будем следить
CREATE TABLE `test` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`content` TEXT NOT NULL
) ENGINE = MYISAM
— лог
CREATE TABLE `log` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`msg` VARCHAR( 255 ) NOT NULL,
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`row_id` INT( 11 ) NOT NULL
) ENGINE = MYISAM
— триггер
DELIMITER |
CREATE TRIGGER `update_test` AFTER INSERT ON `test`
FOR EACH ROW BEGIN
INSERT INTO log Set msg = ‘insert’, row_id = NEW.id;
END;* This source code was highlighted with Source Code Highlighter.

Теперь добавьте запись в таблицу test. В таблице log тоже появится запись, обратите внимание на поле row_id, в нем хранится id вставленной вами строки.

Расширенный лог:

Исходные данные:

— Удаляем триггер
DROP TRIGGER `update_test`;
— Cоздадим еще одну таблицу,
— в которой будут храниться резервные копии строк из таблицы test
CREATE TABLE `testing`.`backup` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`row_id` INT( 11 ) UNSIGNED NOT NULL,
`content` TEXT NOT NULL
) ENGINE = MYISAM
— триггеры
DELIMITER |
CREATE TRIGGER `update_test` before update ON `test`
FOR EACH ROW BEGIN
INSERT INTO backup Set row_id = OLD.id, content = OLD.content;
END;CREATE TRIGGER `delete_test` before delete ON `test`
FOR EACH ROW BEGIN
INSERT INTO backup Set row_id = OLD.id, content = OLD.content;
END* This source code was highlighted with Source Code Highlighter.

Теперь если мы отредактируем или удалим строку из test она скопируется в backup.

зы: надеюсь статья была интересной и полезной
UPD: для создания триггеров в версии до 5.1.6 требуются полномочия суперпользователя.

Источник

Декабрь2

Предисловие

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

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

INSERT (BEFORE | AFTER)
UPDATE (BEFORE | AFTER)
DELETE (BEFORE | AFTER)

Что необходимо для работы

Cервер БД версии 5.0.2 и выше
привилегии на использование этой ф-ции (TRIGGER) начиная с версии 5.1.6, до этого было SUPER, но т.к. все локально используют пользователя root, то опасаться нечего

Добавление триггера

Для того чтобы понять работу триггера необходим пример.

Задание

Необходимо при добавлении записи в табл user, пароль преобразовывать в хеш md5(), также имя и отчество преобразовывать в инициалы.

Решение

mysql> DELIMITER //
mysql> CREATE TRIGGER `test_user_pass` BEFORE INSERT ON `test`.`user`
    -> FOR EACH ROW
    -> BEGIN
    ->  SET NEW.name = LEFT(NEW.name,1);
    ->  SET NEW.otch = LEFT(NEW.otch,1);
    ->  SET NEW.pass = md5(NEW.pass);
    -> END//
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;

Теперь вставляем туда запись

mysql> INSERT  INTO `user` SET `fam`='Нагайченко', `name`='Максим', `otch` = 'Валерьевич', `pass` = 'password', `login` = 'maxnag';
Query OK, 1 row affected (0.00 sec)

Что теперь в таблице

mysql> SELECT * FROM `user`;
+----+-------------+------+------+----------------------------------+--------+
| id | fam         | name | otch | pass                             | login  |
+----+-------------+------+------+----------------------------------+--------+
|  1 | Нагайченко  | M    | В    | 5f4dcc3b5aa765d61d8327deb882cf99 | maxnag |
+----+-------------+------+------+----------------------------------+--------+

1 row in set (0.00 sec)

Как видно всего несколькими строчками можно убрать целые методы, которые мы использовали при регистрации нового пользователя. Теперь еще надо создать триггер на UPDATE таблицы, с таким же телом, чтобы пользователь не смог записать полное имя, отчество и пароль не в МД5();

Создание триггера на BEFORE UPDATE

mysql> DELIMITER //
mysql> CREATE TRIGGER `test_user_pass2` BEFORE UPDATE ON `test`.`user`
    -> FOR EACH ROW
    -> BEGIN
    ->  SET NEW.name = LEFT(NEW.name,1);
    ->  SET NEW.otch = LEFT(NEW.otch,1);
    ->  SET NEW.pass = md5(NEW.pass);
    -> END//
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;

Обновление записи

mysql> UPDATE `user` SET `fam`='Иванов', `name`='Иван', `otch` = 'Иванович', `pass` = 'пароль', `login` = 'ivan' WHERE id=1;
Query OK, 1 row affected (0.00 sec)

Итог

mysql> SELECT * FROM `user`;
+----+-------------+------+------+----------------------------------+--------+
| id | fam         | name | otch | pass                             | login  |
+----+-------------+------+------+----------------------------------+--------+
|  1 | Иванов      | И    | И    | e242f36f4f95f12966da8fa2efd59992 | ivan   |
+----+-------------+------+------+----------------------------------+--------+

1 row in set (0.00 sec)

Могут возникнуть вопросы, что такое NEW в теле триггера,
NEW — для доступа к новым записям
OLD — для доступа к старым записям

Напимер, если я обновил фамилию, то новое значение мне доступно через NEW.fam, а к старому OLD.fam

Изменение триггера

А вот команды по изменению триггера я не нашел и был удивлен, почитал форумы, так и есть — её просто нет ((

Alter trigger

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

Для удаления триггера используется, как обычно оператор DROP, пример

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

где
schema_name — название БД,
trigger_name — название триггера

Список созданных триггеров

Показать триггер можно с помощью команды

SHOW TRIGGERS [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

Это полная часть команды, в основном пользуются командами:

SHOW TRIGGERS [FROM db_name] [LIKE 'pattern']
или
SHOW TRIGGERS

Это полная часть команды, в основном пользуются командами:

SHOW TRIGGERS [FROM db_name] [LIKE 'pattern']
или
SHOW TRIGGERS

Вот что мы увидем, когда выполним команду

mysql> SHOW TRIGGERSG;
*************************** 1. row ***************************
             Trigger: test_user_pass
               Event: INSERT
               Table: user
           Statement: BEGIN
                          SET NEW.name = LEFT(NEW.name,1);
                          SET NEW.otch = LEFT(NEW.otch,1);
                          SET NEW.pass = md5(NEW.pass);
                      END
              Timing: BEFORE
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.01 sec)

Хороший ассортимент выбора книг, электроники, бытовой техники, посуды, подарки, сувениры и много другое Вы можете сделать на сайте компании E5.RU. Купить фильмы на dvd в фирменной упаковке и сделать хороший подарок родным и близким, Вы можете там же.

Статья просмотренна 176365 раз, зашло посетителей 78680

Источник

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

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

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

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

Триггеры были введены в 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 нет необходимости менять разделитель.

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

 DELIMITER $$

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

 DELIMITER ;

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

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

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

 $sql = 'DELETE FROM no_trigger_cart_items WHERE cart_id = 1';
$rs = $this->db->query($sql);
$sql = 'DELETE FROM no_trigger_carts WHERE cart_id = 1';
$rs = $this->db->query($sql);

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

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

 CREATE TRIGGER `tutorial`.`before_delete_carts`
BEFORE DELETE ON `trigger_carts` FOR EACH ROW
BEGIN
DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;
END

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

Первая строка“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.

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

Два запроса:

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

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

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

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

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

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

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

 CREATE TRIGGER `after_insert_cart_items`
AFTER INSERT ON `trigger_cart_items` FOR EACH ROW
BEGIN
INSERT INTO trigger_cart_log (cart_id, item_id)
VALUES (NEW.cart_id, NEW.item_id);
END

Первая строка “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) цену. Давайте используем триггер.

 CREATE TRIGGER `after_update_cost`
AFTER UPDATE ON `trigger_items_cost` FOR EACH ROW
BEGIN
UPDATE trigger_items
SET price = (NEW.cost * 1.3)
WHERE item_id = NEW.item_id;
END

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

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

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

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

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

 CREATE TRIGGER `before_update_cost`
BEFORE UPDATE ON `trigger_items_cost` FOR EACH ROW
BEGIN
IF NEW.cost < 50 THEN
SET NEW.cost = 50;
ELSEIF NEW.cost > 50 AND NEW.cost < 100 THEN
SET NEW.cost = 100;
END IF;
END

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

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

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

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

Заключение

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

Источник

�� 6. ��

�� , �� MySQL 5.0.2. ��
�� , �� ,
� �� , �� .
��, ��
INSERT. �� , ��
�� :

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

�� ,
�� , �� . ��
��
»
11.1. �� ».

6.1. �� `CREATE TRIGGER`

CREATE
  [DEFINER = {user | CURRENT_USER}]
  TRIGGER trigger_name trigger_time trigger_event
  ON tbl_name FOR EACH ROW trigger_stmt

�� . CREATE TRIGGER ��
�� MySQL 5.0.2. � ��
�� SUPER.

��
tbl_name, ��
��. �� view ��
TEMPORARY.

�� , �� DEFINER ��
��, �� , �� .

trigger_time �� . ��
BEFORE �� AFTER, �� , ��
�� , �� .

trigger_event �� , ��
�� . �� trigger_event ��
�� :

INSERT: �� , ��
�� . ��, �� INSERT, LOAD DATA �� REPLACE.
UPDATE: �� , �� . ��,
�� UPDATE.
DELETE: �� , �� .
��, �� DELETE � REPLACE.
��, �� DROP TABLE � TRUNCATE
�� , ��
�� DELETE!

�� , �� trigger_event ��
�� SQL, �� , ��
�� . ��, �� INSERT
�� INSERT, �� LOAD DATA, �� .

�� , ��
�� . ��, ��
BEFORE UPDATE �� . �� BEFORE UPDATE � BEFORE INSERT �� BEFORE UPDATE � AFTER UPDATE.

trigger_stmt �� , ��
��, �� . ��
��, �� BEGIN ... END.
�� , ��
�� .

��: � ��
�� . ��
�� .

�� : �� MySQL
5.0.10 �� . � MySQL
5.0.10, �� , �� :

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                   b4 INT DEFAULT 0);
DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|
DELIMITER ;
INSERT INTO test3 (a3) VALUES
  (NULL), (NULL), (NULL), (NULL), (NULL),
  (NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

��, ��
test1 �� :

mysql> INSERT INTO test1 VALUES 
    -> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

� �� :

mysql> SELECT * FROM test1;
+------+
| a1   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)

�� (��, ��
� ��), �� OLD � NEW.
OLD.col_name ��
�� , �� .
NEW.col_name ��
��, �� , �� ,
�� .

�� DEFINER �� MySQL, ��
�� . ��
�� MySQL 5.0.17. �� user,
�� MySQL � ��
'user_name'@'host_name'
(�� GRANT). ��
user_name � host_name.
CURRENT_USER ��
CURRENT_USER(). ��
DEFINER: ��, ��
CREATE TRIGGER. �� , ��
DEFINER = CURRENT_USER.

�� DEFINER, ��
�� , �� , ��
�� SUPER. ��
�� DEFINER:

�� SUPER,
�� user: ��
��, �� CURRENT_USER. ��
�� DEFINER � �� .
�� SUPER, ��
�� . ��
�� , �� .

��
DEFINER, �� , ��
��, �� DEFINER �� . ��,
�� .

�� : �� MySQL � �� , ��
�� SUPER �� CREATE TRIGGER,
�� . MySQL 5.1.6 ��
�� TRIGGER � ��, ��
�� , �� , �
SUPER ��
DEFINER ��, �� .

�� MySQL 5.0.17, MySQL �� :

� �� CREATE TRIGGER
��, �� , ��
�� SUPER.
��
DEFINER. �� :
- SUPER.
- SELECT �� , ��
  �� OLD.col_name ��
  or NEW.col_name � �� .
- UPDATE �� , ��
  �� SET NEW.col_name = value, �� .
- ��
  ��, �� .

�� MySQL 5.0.17, MySQL �� :

�� CREATE TRIGGER ��,
�� , �� SUPER.
� �� , ��
�� . ��
��, �� , �� .

�� , �� DEFINER ��
�� CURRENT_USER() � �� : ��
CURRENT_USER() �� DEFINER �
MySQL 5.0.17 (� ��) �� , ��
�� (�� MySQL 5.0.17).

6.2. �� `DROP TRIGGER`

DROP TRIGGER [schema_name.]trigger_name

�� . �� . �� ,
�� , �� DROP TRIGGER �� MySQL 5.0.2.
�� SUPER.

�� : �� MySQL
5.0.10, ��
(table_name.trigger_name). �� MySQL 5.0 �� MySQL 5.0.10 �� , ��
�� , ��
�� DROP TRIGGER �� .

�� , ��, �� MySQL 5.0.16 �� , ��
�� MySQL 5.0.15 �� . �� , ��
��
�� .

6.3. ��

�� , �� MySQL 5.0.2. ��
��, �� .

�� , ��
��, � ��, ��
��. �� , �� ,
��
��, �� .

�� , �� , ��
�� INSERT, DELETE
�� UPDATE. �� , ��
�� . ��, ��
�� , �� , ��
�� .

�� , ��
�� INSERT. �� , ��
��, �� .

�� :

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    ->        FOR EACH ROW SET @sum = @sum + NEW.amount;

�� CREATE TRIGGER �� ins_sum,
�� account. ��
��, �� , �� , � ��
� �� :

�� BEFORE ��
��. � ��
��, �� . ��
�� : AFTER.
�� INSERT �� , ��
�� . � ��
INSERT. ��
�� DELETE � UPDATE.
�� FOR EACH ROW ��, ��
�� , ��
��. ��
SET, �� , ��
amount. ��
NEW.amount, �� «�� amount,
�� ».

�� , �� ,
�� INSERT, � �� , ��
�� :

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48               |
+-----------------------+

� �� @sum ��
INSERT �� 14.98 + 1937.50 - 100 ��
1852.48.

�� DROP TRIGGER. ��
�� , �� :

mysql> DROP TRIGGER test.ins_sum;

�� . �� , ��
�� . ��
�� .

� �� , �� ,
�� , �� . ��
�� , ��
�� . ��, ��
BEFORE INSERT �� AFTER UPDATE �� . ��
�� , ��
�� , ��
BEGIN ... END �� FOR EACH ROW.

�� OLD � NEW ��
�� , ��
OLD � NEW �� . � ��
INSERT �� NEW.col_name: �� . � ��
DELETE �� , ��
�� OLD.col_name.
� �� UPDATE ��
OLD.col_name, ��
�� , �� , � NEW.col_name, �� .

��, �� OLD �� . ��
�� (�� SELECT, ��
�� . ��, �� NEW �� ,
�� SELECT �� . � ��
BEFORE �� SET NEW.col_name = value, ��
UPDATE �� . �� , ��
��, �� , �� , ��
�� , �� .

� �� BEFORE �� NEW ��
AUTO_INCREMENT: 0, � ��
��, �� , �� .

OLD � NEW ��
MySQL-�� .

�� BEGIN ... END, ��
��, �� . �� BEGIN
�� , ��
�� , �� . ��, ��
�� , ��
mysql, �� , ��
�� mysql
��, �� ; ��
�� . �� . ��
�� UPDATE, �� ,
�� , � ��
��, �� 0 �� 100. ��
�� BEFORE, ��
��, �� , �� :

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    ->        FOR EACH ROW
    -> BEGIN
    ->   IF NEW.amount < 0 THEN SET NEW.amount = 0;
    ->      ELSEIF NEW.amount > 100 THEN SET NEW.amount = 100;
    ->   END IF;
    -> END;//
mysql> delimiter ;

��
�� CALL. �� , ��
�� .

�� , �� :

��
CALL, �� , ��
�� SQL. ��
��
OUT �� INOUT.
�� , ��
�� , �� START TRANSACTION, COMMIT �� ROLLBACK.
�� MySQL 5.0.10 ��
�� .

MySQL �� :

�� BEFORE,
�� .
�� BEFORE ��
�� , �� , �� .
�� AFTER �� , ��
BEFORE � �� (��!) �� .
�� BEFORE �� AFTER
�� , �� .
��
�� , �� . ��
�� , ��
��. ��
��, �� , �� , �� ,
�� , �� .

6.4. MySQL 5 FAQ ��

6.4.1:
��
�� MySQL?

��.
http://forums.mysql.com/list.php?99.

6.4.2:
MySQL 5.1 ��
�� ?

� MySQL 5.1 �� FOR EACH ROW, ��
�� , �� , ��
��. MySQL 5.1 ��
FOR EACH STATEMENT.

6.4.3:
��
�� ?

��. MySQL ��
�� TIMESTAMP, � �� , �� ,
�� AUTO_INCREMENT.

6.4.4:
�� MySQL?

� MySQL 5.1 �� , �� CREATE TRIGGER, � �� DROP TRIGGER.

�� , ��
INFORMATION_SCHEMA.TRIGGERS.

6.4.5:
��
�� ?

��. �� , ��
�� dbname, ��
INFORMATION_SCHEMA.TRIGGERS �� :

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE,
       ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS
       WHERE TRIGGER_SCHEMA='dbname';

�� SHOW TRIGGERS,
�� MySQL.

6.4.6:
�� ?

��
.TRG-��, �� .
�� , �� .

� ��, �� , ��
�� .FRM-��, �� .
�� , ��
�� SQL-��.

6.4.7:

�� ?

��.

6.4.8:

�� ?

�� .
�� , ��
�� .

6.4.9:
��
�� UDF?

��, �� .

6.4.10:
��
�� ?

��. �� , ��
�� FEDERATED.

Источник

3 марта, 2020 12:36 пп
1 293 views
| Комментариев нет

mySQL, Ubuntu

Триггеры (trigger) в MySQL – это определяемые пользователем SQL-команды, которые автоматически вызываются во время операций INSERT, DELETE или UPDATE. Код триггера связан с таблицей и уничтожается после удаления таблицы. Пользователь может определить время действия триггера и указать, когда его нужно активировать – до или после определенного события базы данных.

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

Вы также можете использовать триггеры, чтобы сохранить правила проверки на уровне базы данных. Это бывает необходимо при совместном использовании одного источника данных несколькими приложениями – так можно избежать нарушения бизнес-логики. Это значительно уменьшает количество обращений к серверу БД, что, следовательно, улучшает время отклика приложения.

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

Требования

Сервер Ubuntu 18.04, настроенный согласно этому мануалу.
Сервер MySQL, настроить который вам поможет этот мануал.
Учетные данные пользователя root MySQL.

1: Создание базы данных

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

Читайте также: Запросы в MySQL

Войдите на сервер MySQL как root:

mysql -u root -p

При появлении запроса введите свой root пароль MySQL и нажмите клавишу Enter, чтобы продолжить. Когда вы увидите префикс mysql> в командной строке, выполните следующую команду, чтобы создать БД test_db:

Create database test_db; Query OK, 1 row affected (0.00 sec)

Затем перейдите в test_db с помощью этой команды:

Use test_db; Database changed

Теперь создадим таблицу customers. В этой таблице будут храниться записи клиентов, в том числе customer_id, customer_name и level. У нас будет две категории клиентов: BASIC и VIP.

Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec)

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

Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC'); Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC'); Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

После выполнения каждой команды INSERT вы увидите следующий вывод:

Query OK, 1 row affected (0.01 sec)

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

Затем мы создадим еще одну таблицу, customer_status, для хранения соответствующей информации об учетной записи клиентов. Таблица будет иметь поля customer_id и status_notes.

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

Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

Затем мы создадим таблицу sales. В этой таблице будут храниться данные о продажах, связанные с другими таблицами через столбец customer_id:

Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec)

Данные в таблицу sales мы добавим во время тестирования триггеров. Сейчас создайте таблицу audit_log для регистрации обновлений, внесенных в таблицу sales после выполнения триггера AFTER UPDATE в разделе 5:

Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB; Query OK, 0 rows affected (0.02 sec)

Теперь у вас есть тестовая БД test_db и четыре таблицы. Давайте попробуем поработать с различными триггерами MySQL.

2: Создание триггера BEFORE INSERT

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

Общий синтаксис для создания триггера MySQL выглядит так:

DELIMITER // CREATE TRIGGER [TRIGGER_NAME] [TRIGGER TIME] [TRIGGER EVENT] ON [TABLE] FOR EACH ROW [TRIGGER BODY]// DELIMITER ;

В состав триггера входят такие компоненты:

DELIMITER //: Разделитель MySQL по умолчанию – это точка с запятой (;). Его необходимо заменить чем-то другим, чтобы MySQL рассматривал все строки как одну команду, пока не достигнет пользовательского разделителя. В этом примере мы выбрали в качестве пользовательского разделителя два слеша (//). Стандартный разделитель (;) стоит в конце выражения.
[TRIGGER_NAME]: триггер должен иметь имя, и именно здесь вы можете его указать.
[TRIGGER TIME]: триггер можно вызывать в разные моменты времени. MySQL позволяет вам определить, когда запускать триггер – до или после операции с БД.
[TRIGGER EVENT]: триггеры вызываются только операциями INSERT, UPDATE и DELETE. Здесь вы можете использовать любое из этих значений в зависимости от того, чего вы хотите достичь.
[TABLE]: каждый триггер, который вы создаете в своей базе данных MySQL, должен быть связан с таблицей. Здесь нужно ее указать.
FOR EACH ROW: этот оператор позволяет MySQL выполнять код триггера для каждой строки в таблице.
[TRIGGER BODY]: код, который выполняется при вызове триггера, называется телом триггера. Это может быть один SQL оператор или несколько команд. Обратите внимание, если в теле триггера вы выполняете несколько SQL операторов, вы должны заключить их в блок BEGIN … END.

Примечание: При создании тела триггера вы можете использовать ключевые слова OLD и NEW для доступа к старым и новым значениям столбца, введенным во время операций INSERT, UPDATE и DELETE. В триггере DELETE можно использовать только ключевое слово OLD (подробнее об этом – в разделе 4).

Теперь вы можете создать свой первый триггер BEFORE INSERT. Этот триггер будет связан с таблицей sales, он будет вызываться перед вставкой записи для проверки sales_amount. Функция триггера состоит в том, чтобы проверить, является ли значение sales_amount, добавляемое в таблицу продаж, больше 10000, и выдать ошибку, если это оценивается как true.

Убедитесь, что вы вошли на сервер MySQL. Затем введите следующие команды MySQL одну за другой:

DELIMITER // CREATE TRIGGER validate_sales_amount BEFORE INSERT ON sales FOR EACH ROW IF NEW.sales_amount>10000 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.'; END IF// DELIMITER ;

Оператор IF … THEN … END IF позволяет оценить, находится ли сумма, указанная в операторе INSERT, в пределах вашего диапазона. Триггер может извлечь новое значение sales_amount, используя ключевое слово NEW.

Чтобы вызвать общее сообщение об ошибке, мы используем здесь следующие строки:

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';

Теперь вставьте запись sales_amount со значением 11000 в таблицу sales, чтобы проверить, остановит ли триггер операцию:

Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000'); ERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

Эта ошибка показывает, что код триггера работает должным образом.

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

Insert into sales(sales_id, customer_id, sales_amount) values('1','1','7500');

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

Query OK, 1 row affected (0.01 sec)

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

Select * from sales;

Вывод подтверждает, что данные находятся в таблице:

+----------+-------------+--------------+ | sales_id | customer_id | sales_amount | +----------+-------------+--------------+ | 1 | 1 | 7500 | +----------+-------------+--------------+ 1 row in set (0.00 sec)

Итак, вы протестировали триггеры для проверки данных перед вставкой в БД.

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

3: Создание триггера AFTER INSERT

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

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

Чтобы создать триггер AFTER INSERT, введите следующие команды:

DELIMITER // CREATE TRIGGER customer_status_records AFTER INSERT ON customers FOR EACH ROW Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')// DELIMITER ; Query OK, 0 rows affected (0.00 sec)

Эти команды говорят MySQL сохранить еще одну запись в таблицу customer_status, как только новая запись о клиенте будет добавлена в таблицу customers.

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

Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP'); Query OK, 1 row affected (0.01 sec)

Поскольку запись была успешно вставлена в таблицу customers, убедитесь, что новая запись о статусе была добавлена в таблицу customer_status:

Выходные данные подтверждают, что триггер работает успешно.

Триггер AFTER INSERT полезен для мониторинга жизненного цикла клиента. В производственной среде учетные записи клиентов могут проходить различные этапы (например, открытие, приостановка и закрытие счета).

4: Создание триггера BEFORE UPDATE

Триггер BEFORE UPDATE похож на триггер BEFORE INSERT. Разница только в том, когда они вызываются. Вы можете использовать триггер BEFORE UPDATE, чтобы проверить бизнес-логику перед обновлением записи. Чтобы посмотреть, как он работает, используйте таблицу customers, в которую уже вставлены некоторые данные.

В базе данных есть два уровня клиентов. Для примера представим себе такую ситуацию: как только учетная запись клиента будет обновлена до уровня VIP, она не сможет быть понижена до уровня BASIC. Чтобы применить такое правило, мы создадим триггер BEFORE UPDATE, который будет выполняться перед оператором UPDATE. Если пользователь базы данных попытается понизить клиента до уровня BASIC с уровня VIP, будет сгенерировано пользовательское исключение.

Введите следующие команды SQL одну за другой, чтобы создать триггер BEFORE UPDATE:

DELIMITER // CREATE TRIGGER validate_customer_level BEFORE UPDATE ON customers FOR EACH ROW IF OLD.level='VIP' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.'; END IF // DELIMITER ;

Ключевое слово OLD позволяет зафиксировать уровень, предоставленный пользователем при выполнении команды UPDATE. Опять же, вы используете оператор IF … THEN … END IF, чтобы сообщить пользователю об ошибке.

Затем выполните следующую SQL команду, которая попытается понизить учетную запись клиента, связанную с customer_id 3:

Update customers set level='BASIC' where customer_id='3';

Вы увидите следующий вывод, предоставляющий SET MESSAGE_TEXT:

ERROR 1644 (45000): A VIP customer can not be downgraded.

Если вы выполните ту же команду для клиента уровня BASIC и попытаетесь повысить учетную запись до уровня VIP, команда выполнится успешно:

Update customers set level='VIP' where customer_id='1'; Rows matched: 1 Changed: 1 Warnings: 0

Теперь давайте посмотрим, как работает триггер AFTER UPDATE для ведения журнала аудита.

5: Создание триггера AFTER UPDATE

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

Давайте создадим триггер, который регистрирует активность обновления таблицы sales. Таблица audit_log будет содержать информацию о пользователях MySQL, обновляющих таблицу sales, дату обновления, а также новые и старые значения sales_amount.

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

DELIMITER // CREATE TRIGGER log_sales_updates AFTER UPDATE ON sales FOR EACH ROW Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )// DELIMITER ;

Вы вставляете новую запись в таблицу audit_log. Ключевое слово NEW позволяет получить значение sales_id и новое значение sales_amount. Ключевое слово OLD выдает предыдущие значения sales_amount, благодаря чему вы можете зарегистрировать обе суммы для аудита.

Команда SELECT USER() извлекает текущего пользователя, выполняющего операцию, а NOW () извлекает значение текущей даты и времени с сервера MySQL.

Если теперь пользователь попытается обновить значение какой-либо записи в таблице sales, триггер log_sales_updates вставит новую запись в таблицу audit_log.

Давайте создадим новую запись о продажах с у словным значением sales_id, равным 5, и попробуем обновить ее. Сначала вставьте запись о продажах:

Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000'); Query OK, 1 row affected (0.00 sec)

Затем обновите эту запись:

Update sales set sales_amount='9000' where sales_id='5';

Вы получите такой вывод:

Rows matched: 1 Changed: 1 Warnings: 0

Теперь выполните следующую команду, чтобы проверить, смог ли триггер AFTER UPDATE зарегистрировать новую запись в таблице audit_log:

Select * from audit_log;

Триггер зарегистрировал обновление. Ваш вывод покажет предыдущий sales_amount и новую сумму, зарегистрированную пользователем, который обновил запись:

+--------+----------+-----------------+------------+----------------+---------------------+ | log_id | sales_id | previous_amount | new_amount | updated_by | updated_on | +--------+----------+-----------------+------------+----------------+---------------------+ | 1 | 5 | 8000 | 9000 | root@localhost | 2019-11-07 09:28:36 | +--------+----------+-----------------+------------+----------------+---------------------+ 1 row in set (0.00 sec)

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

Далее мы рассмотрим триггер DELETE для обеспечения целостности ссылок на уровне базы данных.

6: Создание триггера BEFORE DELETE

Триггеры BEFORE DELETE вызываются до выполнения операции DELETE. Этот вид триггеров обычно используется для обеспечения ссылочной целостности в разных связанных таблицах. Например, каждая запись в таблице sales связана с записью в таблице customers через столбец customer_id. Если пользователь базы данных удалил из таблицы customers запись, у которой есть связанная запись в таблице sales, у вас не будет возможности узнать, какой клиент был связан с этой записью.

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

DELIMITER // CREATE TRIGGER validate_related_records BEFORE DELETE ON customers FOR EACH ROW IF OLD.customer_id in (select customer_id from sales) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The customer has a related sales record.'; END IF// DELIMITER ;

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

Delete from customers where customer_id='2';

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

ERROR 1644 (45000): The customer has a related sales record.

Триггер BEFORE DELETE может предотвратить случайное удаление связанной информации в базе данных.

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

7: Создание триггера AFTER DELETE

Триггеры AFTER DELETE активируются, когда запись была успешно удалена. Примером использования триггера AFTER DELETE является такая ситуация: скидка, которую получает конкретный клиент, определяется количеством покупок, совершенных этим клиентом в течение определенного периода. Следовательно, если некоторые из записей клиента будут удалены из таблицы sales, скидка для этого клиента должна уменьшиться.

Еще один вариант использования триггера AFTER DELETE – удаление связанной информации из других таблиц после удаления записи из базовой таблицы. Например, вы можете установить триггер, который удалит запись о клиенте, если записи о продажах с соответствующим customer_id будут удалены из таблицы sales. Запустите следующую команду, чтобы создать такой триггер:

DELIMITER // CREATE TRIGGER delete_related_info AFTER DELETE ON sales FOR EACH ROW Delete from customers where customer_id=OLD.customer_id;// DELIMITER ;

Теперь запустите следующую команду, чтобы удалить все записи о продажах для клиента с customer_id 2.

Delete from sales where customer_id='2'; Query OK, 1 row affected (0.00 sec)

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

Select * from customers where customer_id='2';

Вы получите вывод Empty Set, поскольку запись клиента, связанная с customer_id 2, была удалена триггером:

Empty set (0.00 sec)

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

8: Удаление триггеров

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

Drop trigger [TRIGGER NAME];

Например, чтобы удалить последний созданный триггер, AFTER DELETE, выполните следующую команду:

Drop trigger delete_related_info; Query OK, 0 rows affected (0.00 sec)

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

Заключение

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

Читайте также:

Оптимизация работы MySQL с помощью кэша запросов в Ubuntu 18.04
Постраничный вывод данных на MySQL и PHP в Ubuntu 18.04
Доступ к логам ошибок MySQL

Tags: MySQL, Ubuntu 18.04

Источник

Синтаксис создания триггера:

Применение

Лог

Расширенный лог:

Предисловие

Что необходимо для работы

Добавление триггера

Задание

Решение

Изменение триггера

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

Список созданных триггеров

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

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

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

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

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

Заключение

6.1. ��������� CREATE TRIGGER

6.2. ��������� DROP TRIGGER

6.3. ������������� ���������

6.4. MySQL 5 FAQ �� ���������

Требования

1: Создание базы данных

2: Создание триггера BEFORE INSERT

3: Создание триггера AFTER INSERT

4: Создание триггера BEFORE UPDATE

5: Создание триггера AFTER UPDATE

6: Создание триггера BEFORE DELETE

7: Создание триггера AFTER DELETE

8: Удаление триггеров

Заключение

Читайте также:

6.1. �� `CREATE TRIGGER`

6.2. �� `DROP TRIGGER`

6.3. ��

6.4. MySQL 5 FAQ ��