Введення в тригери MySQL - Про життя, роботу, людей
Метатригера - додатковий рівень абстрагування рівня додатків. Тригер є іменованим об'єктом бази даних, який пов'язаний з певною таблицею, і він буде активізований, коли для таблиці відбудеться одна з подій.
Тригер - це правило, яке міститься в таблиці, і при виконанніDELETE,UPDATE абоINSERT робить додаткові дії. Наприклад, можна робити запис у журналі про зміну. Але замість написання двох окремих запитів (один – для зміни даних, інший для внесення запису до журналу), можна написати тригер, який міститиме правило: “Коли б не змінювався рядок, створити новий рядок в іншій таблиці, щоб повідомити, що були зроблені зміни”. Такий підхід створює певну надмірність в основному запиті, але тепер немає проходів двох різних пакетів до сервера вашої бази даних, щоб виконати дві різні дії, що в цілому сприяє покращенню продуктивності.
Тригери виконуються як процедури при подіях UPDATE, DELETE та INSERT. При цьому можуть бути виконані або до або після визначення події. Таким чином, Ви можете визначити тригер, який буде виконуватися до DELETE або після DELETE, і так далі. Це означає, що можна мати один тригер, який виконається до INSERT і зовсім інший, який виконається після INSERT, що є дуже потужним інструментом.
Не може бути двох тригерів для даної таблиці, які мають той самий час дії та подію. Наприклад, Ви не можете мати два тригери BEFORE UPDATE для таблиці. Але Ви можете мати BEFORE UPDATE та BEFORE INSERT або BEFORE UPDATE та AFTER UPDATE.
Якщо кілька команд у тригері, то через точку з комою.
Тригер контролюцілісності даних
Розглянемо дві таблиці sotr(ids,fio) та sotr_in_proekt(idp,ids). Перша таблиця містить ідентифікатор співробітника та його ім'я, друге номери проектів та номери співробітників, які беруть участь у цих проектах.
Реалізуємо тригер, який при видаленні даних із таблиці sotr автоматично видаляє дані з таблиці sotr_in_proekt для забезпечення цілісності (каскадне видалення).
Якщо не використовувати тригери, після видалення даних з першої таблиці було б необхідно додатково виконувати команду для видалення даних з другої таблиці. Це може бути реалізовано на рівні інтерфейсу, а у разі ручного видалення доведеться постійно про це пам'ятати.
Розглянемо тригери на прикладіMySQL. Тригери були введені в MySQL з версії 5.0.2. Вибираємо меню Тригер – Новий, після чого вкажемо:
CREATE TRIGGER exp.before_delete_sotr
BEFORE DELETE ON sotr FOR EACH ROW
BEGIN DELETE FROM sotr_in_proekt WHERE OLD. >
Розберемо приклад докладно.
CREATE TRIGGER exp.before_delete_sotr
вказує, що тригер треба створити для бази даних "exp", який матиме ім'я "before_delete_sotr".
BEFORE DELETE ON sotr FOR EACH ROW
вказує MySQL, що певні дії необхідно виконати перед тим, як провести видалення з даної таблиці
Дані дії визначені далі між BEGIN та END:
DELETE FROM sotr_in_proekt WHERE OLD. >
Змінна OLD визначає змінну, вихідні значення якої містяться в рядку перед видаленням.
Перевага використання тригера – контрольцілісності даних переміщається з рівня логіки до рівня даних. Слід також зазначити, що є певний прирістпродуктивність системи. Пояснення тут таке. База даних для прикладу використовує той же сервер, що і клієнт. Але якщо сервер баз даних розташований в іншому місці, слід очікувати більшої різниці, оскільки до часу виконання запитів додасться час передачі даних між серверами. Переміщення логіки даних на рівень даних подібно до того, як завдання стилю переміщається з рівня розмітки на рівень презентації, що відоме усьому світу як CSS.
Після створення кожного тригера обов'язково подивіться, як він працює!
Журналування та аудит
Створимо тригер, який буде при внесенні даних до таблиці sotr додаватиме введене прізвище до спеціально створеної таблиці zh(zapis). Для цього треба зазначити:
CREATE TRIGGER `after_insert_sotr`
AFTER INSERT ON `sotr` FOR EACH ROW
INSERT INTO zh(zapis) VALUES ( NEW.fio)
Тут використовуються значення NEW, які вставляють у таблицю zh.
Реалізація бізнес-логіки
Додамо таблицю proekt (idp, name, cost, price), що містить код проекту, назву, вартість. Створимо тригер для UPDATE, який до вартості, що вводиться, встановлює націнку в 25% і поміщає нову вартість в полі price.
Для цього створимо наступний тригер:
CREATE TRIGGER `before_update_cost`
BEFORE UPDATE ON `proekt` FOR EACH ROW
SET New.price = New.cost * 1.25;
Зауваження. Багато де рекомендують використовувати замість BEFORE AFTER та другий UPDATE після BEGIN, але тоді щось дивує, т.к. нескінченно викликається UPDATE.
Тепер визначимо, щоб нова вартість визначалася в залежності від величини старої, а саме якщо ціна менша за 100, то нехай буде 100, якщо ж ціна більша за 100, але менша за 200, то нехай буде 200.
Для цьоговикористовуємо вираз IF, з урахуванням якого тригер before_update_cost набуває вигляду:
CREATE TRIGGER `before_update_cost`
BEFORE UPDATE ON `proekt` FOR EACH ROW
IF NEW.cost 100 AND NEW.cost
Рекомендую прочитати статті, пов'язані з цією: