МДК.11.01 - 21 - Триггеры

Примеры данной темы используют учебную БД:

SET foreign_key_checks = 0;

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS accounts;
DROP TABLE IF EXISTS transactions;

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `accounts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) DEFAULT NULL,
  `user_id` INT NOT NULL,
  `balance` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `user_id_idx` (`user_id`),
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE `transactions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `date_time` DATETIME NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  `transaction_type` ENUM('deposit', 'withdrawal', 'transfer') NOT NULL,
  `user_id` INT NOT NULL,
  `account_id` INT NOT NULL,
  `transfer_to_account_id` INT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_idx` (`user_id`),
  KEY `account_id_idx` (`account_id`),
  CONSTRAINT `transaction_user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `account_id`
    FOREIGN KEY (`account_id`)
    REFERENCES `accounts` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES
(1,'Иванов Иван Иванович', 31),
(2,'Петров Петр Петрович', 27),
(3,'Сидоров Сидор Сидорович', 35);
UNLOCK TABLES;

LOCK TABLES `accounts` WRITE;
INSERT INTO `accounts` VALUES (1,'Основной',1,10500.00),(2,'Резервный',1,3412.57),(3,'Основной',2,20750.00),(4,'Основной',3,25000.00),(5,'Накопительный',2,5401.75);
UNLOCK TABLES;

LOCK TABLES `transactions` WRITE;
INSERT INTO `transactions` (`date_time`, `amount`, `transaction_type`, `user_id`, `account_id`, `transfer_to_account_id`)
VALUES
('2022-01-01 12:00:00', 100.00, 'deposit', 1, 1, NULL),
('2022-01-02 15:30:00', 50.00, 'withdrawal', 1, 1, NULL),
('2022-01-03 09:45:00', 200.00, 'deposit', 2, 3, NULL),
('2022-01-04 14:20:00', 75.00, 'withdrawal', 2, 3, NULL),
('2022-01-05 11:10:00', 150.00, 'transfer', 3, 4, 5);
UNLOCK TABLES;

SET foreign_key_checks = 1;

Триггеры в MySQL 8 представляют собой специальные объекты базы данных, которые автоматически выполняются при определенных событиях или действиях с данными. Они позволяют программистам определить пользовательские действия, которые должны быть выполнены перед или после выполнения операции в базе данных. Иными словами, это определяемая пользователем SQL-команда, которая автоматически вызывается во время операций INSERT, DELETE или UPDATE. Код триггера связан с таблицей и уничтожается после удаления таблицы. Вы можете определить время действия триггера и указать, когда его нужно активировать — до или после определенного события базы данных.Поддержка триггеров в MySQL началась с версии 5.0.2

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

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

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

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

Структура триггера включает:

• [TRIGGER_NAME]: триггер должен иметь имя, и вы можете указать его именно здесь.
• [TRIGGER TIME]: триггер может быть вызван в разные моменты времени. MySQL позволяет определить, когда запускать триггер — до или после операции с базой данных (ключевые слова BEFORE / AFTER).
• [TRIGGER EVENT]: триггеры могут быть вызваны только операциями INSERT, UPDATE и DELETE. Вы можете использовать любое из значений в зависимости от того, чего вы хотите достичь, при этом стоит учесть что операции CREATE, DROP и TRUNCATE триггеров не вызывают.
• [TABLE]: любой триггер, который вы создаете в своей базе данных MySQL, должен быть связан с таблицей.
• FOR EACH ROW: этот оператор позволяет MySQL выполнять код триггера для каждой строки, на которую влияет триггер.
• [TRIGGER BODY]: код, который выполняется при вызове триггера, называется телом триггера. Это может быть один SQL-оператор или несколько команд. Обратите внимание, если вы выполняете несколько SQL-операторов в теле триггера, вы должны заключить их в блок BEGIN... END (по аналогии с хранимыми процедурами или функциями).

Примеры триггеров, которые можно добавить в нашей БД

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

DELIMITER $$
CREATE TRIGGER update_balance
AFTER INSERT
ON transactions
FOR EACH ROW
BEGIN
   IF NEW.transaction_type = 'deposit' THEN
      UPDATE accounts SET balance = balance + NEW.amount WHERE id = NEW.account_id;
   ELSEIF NEW.transaction_type = 'withdrawal' THEN
      UPDATE accounts SET balance = balance - NEW.amount WHERE id = NEW.account_id;
   ELSEIF NEW.transaction_type = 'transfer' THEN
      UPDATE accounts SET balance = balance - NEW.amount WHERE id = NEW.account_id;
      UPDATE accounts SET balance = balance + NEW.amount WHERE id = NEW.transfer_to_account_id;
   END IF;
END$$
DELIMITER ;

Как проверить:

INSERT INTO `transactions` (`date_time`, `amount`, `transaction_type`, `user_id`, `account_id`, `transfer_to_account_id`)
VALUES
(NOW(), 1000.00, 'deposit', 1, 1, NULL)

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

SELECT * FROM accounts WHERE account_id = 1;

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

DELIMITER $$
CREATE TRIGGER check_balance
BEFORE INSERT
ON transactions
FOR EACH ROW
BEGIN
   IF NEW.transaction_type = 'withdrawal' AND NEW.amount > (SELECT balance FROM accounts WHERE id = NEW.account_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Недостаточно средств';
   END IF;
END$$
DELIMITER ;

Как проверить:

INSERT INTO `transactions` (`date_time`, `amount`, `transaction_type`, `user_id`, `account_id`, `transfer_to_account_id`)
VALUES
(NOW(), 100000.00, 'withdrawal', 1, 1, NULL)

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

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

DELIMITER $$
CREATE TRIGGER update_datetime
BEFORE UPDATE
ON transactions
FOR EACH ROW
BEGIN
   SET NEW.date_time = NOW();
END$$
DELIMITER ;

Как проверить:

INSERT INTO `transactions` (`date_time`, `amount`, `transaction_type`, `user_id`, `account_id`, `transfer_to_account_id`)
VALUES
(NOW(), 500.00, 'transfer', 1, 1, 3)

Проверяем дату и время транзакции:

SELECT * FROM transactions WHERE id = LAST_INSERT_ID();

Вставляем тестовую транзакцию, затем обновляем запись в таблице transactions

UPDATE `transactions` SET `amount` = 250 WHERE id = LAST_INSERT_ID();

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

SELECT * FROM transactions WHERE id = LAST_INSERT_ID();

Как узнать что у таблиц в MySQL есть триггеры?

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

SHOW TRIGGERS FROM database_name;

Замените database_name на имя вашей базы данных. Этот запрос покажет список всех триггеров в указанной базе данных, включая таблицу, на которую они применяются, тип события (например, INSERT, UPDATE или DELETE), время выполнения (BEFORE или AFTER) и имя триггера.

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

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

Синтаксис удаления триггера следующий:

DROP TRIGGER [TRIGGER NAME];

Транзакции в триггерах

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

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

• START TRANSACTION: начинает новую транзакцию.
• COMMIT: фиксирует изменения, сделанные в рамках текущей транзакции, и завершает ее успешно.
• ROLLBACK: отменяет все изменения, сделанные в рамках текущей транзакции, и завершает ее неудачно.

Пример использования транзакций в триггерах:

CREATE TRIGGER trigger_name
AFTER INSERT
ON table_name
FOR EACH ROW
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION

   BEGIN
      ROLLBACK;
      RESIGNAL;
   END;

   START TRANSACTION;

   -- выполнение операций

   COMMIT;
END;

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

DECLARE EXIT HANDLER FOR SQLEXCEPTION — это оператор, который объявляет обработчик исключений для текущего блока кода. В данном случае, мы объявляем обработчик исключений для ошибок, связанных с выполнением SQL-запросов (SQLEXCEPTION).

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

В приведенном выше примере, если происходит исключение типа SQLEXCEPTION внутри блока кода триггера, то обработчик перехватывает его и выполняет операции в блоке BEGIN ... END. Затем, с помощью ROLLBACK, происходит откат транзакции, чтобы отменить все изменения, сделанные в рамках этой транзакции. После этого, с помощью RESIGNAL, исключение повторно возбуждается, чтобы оно могло быть обработано на уровне вызывающего кода.

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