Тригери

 

Тригери

Тригер - процедура, що зберігається, але прив'язана до події зміни вмісту таблиці. Можливі три події зміни таблиці, до яких можна прив'язати тригер: це зміна вмісту таблиці за допомогою операторів insert, delete і update. Наприклад, при оформленні нового замовлення, тобто при додаванні нового запису в таблицю orders, можна створити тригер, який автоматично буде віднімати число замовлених товарних позицій у таблиці products.

Зауваження:

  • Підтримка тригерів введена в СКБД MySQL, починаючи з версії 5.0.2.

У даній лекції розглядається створення, видалення і використання тригерів.

Оператор CREATE TRIGGER

Оператор create trigger дозволяє створити новий тригер і має наступний синтаксис:

create trigger trigger_name trigger_time trigger_event on tbl_name for each row trigger_stmt

Оператор create trigger створює trigger_name, прив'язаний до таблиці tbl_name. Таблиця повинна існувати фізично, тобто не допускається прив'язка тригера до тимчасової таблиці або перегляду.

 Конструкція trigger_time вказує момент виконання тригера і може приймати два значення:

  1. before-дії тригера проводяться до виконання операції зміни таблиці;
  2. AFTER-дії тригера проводяться після виконання операції зміни таблиці.

Конструкція trigger_event показує, на яке з подій повинен реагувати тригер, і може приймати три значення:

  1. INSERT - тригер прив'язаний до події вставки нового запису в таблицю;
  2. UPDATE - тригер прив'язаний до події оновлення запису таблиці;
  3. DELETE - тригер прив'язанй до події видалення записів таблиці.

Зауваження:

  • Для таблиці tbl_name може бути створений лише один тригер для кожного з подій trigger_event і моменту trigger_time. Тобто для кожної з таблиць може бути створено лише шість тригерів.

Конструкція trigger_stmt представляє тіло тригера, тобто оператор, який необхідно виконати при виникненні події trigger_event в таблиці tbl_name. Якщо потрібно виконати декілька операторів, то слід використати складений оператор begin ... end, в якому розміщуються всі необхідні запити.

 Взагалі синтаксис і допустимі оператори збігаються з тілом збережених процедур (буде розглядатися в наступних лекціях). Усередині складеного оператора begin ... end допускаються всі специфічні для збережених процедур оператори та конструкції:

  • інші складені оператори begin ... end;
  • оператори управління потоком (IF, CASE, WHILE, LOOP, REPEAT, LEAVE, ITERATE);
  • оголошення локальних змінних за допомогою оператора DECLARE і призначення їм значень за допомогою оператора SET;
  • іменовані умови і обробники помилок.

Зауваження:

  • Для створення тригера за допомогою оператора CREATE TRIGGER потрібна наявність привілеї SUPER.
  • В СКБД MySQL тригери не можна прив'язати до каскадного оновлення або видалення записів з таблиці типу InnoDB по зв'язку первинний ключ / зовнішній ключ.

 

Тригери дуже складно використовувати, не маючи доступу до нових записів, які вставляються в таблицю, або старим записам, які оновлюються або видаляються. Для доступу до нових і старих записів використовуються префікси NEW і OLD відповідно. Тобто якщо в таблиці оновлюється поле total, то отримати доступ до старого значенням можна по імені OLD. total, а до нового - NEW.total.

 

 Створимо найпростіший тригер, який при оформленні нового замовлення (додавання нового запису в таблицю orders) буде присвоювати значення 1 для змінної користувача @tot (приклад).

Приклад. Створення найпростішого тригера

CREATE TRIGGER sub_count AFTER INSERT ON orders

FOR EACH ROW

BEGIN

SET @tot = 1;

END //

 

SELECT @tot//

 

@tot

NULL

 

INSERT INTO orders VALUES (NULL,1, NOW(),1,10)//

 

SELECT @tot//

 

@tot

1

Як видно з прикладу, в результаті додавання нового запису в таблицю orders змінній присвоюється значення 1. Відредагуємо тригер sub_count таким чином, щоб до змінної @ tot додавалося щоразу число замовлених товарних позицій number (дивіться приклад нижче).

Приклад. Новий варіант тригера

CREATE TRIGGER sub_count AFTER INSERT ON orders

FOR EACH ROW

BEGIN

 SET @tot = @tot + NEW.number;

END//

 

SELECT @tot//

 

@tot

1

 

INSERT INTO orders VALUES (NULL,1,NOW(),5,10)//

 

select @tot//

 

@tot

6

Як видно з прикладу, для того щоб отримати число товарних позицій, всередині тригера відбувається звернення до змінної NEW.number, яка пов'язана з полем number INSERT-запиту.

Зауваження:

  • Для коректної роботи тригера необхідно, щоб змінна користувача @tot мала значення, відмінне від NULL, т. к. операції складання з NULL призводять до NULL.
  • При створенні тригерів для таблиці в каталозі даних створюється файл, назва якого збігається з ім'ям таблиці. Файл є текстовим і має розширення trg.

Попередні два приклади демонстрували роботу тригерів після додавання запису в таблицю (AFTER) без втручання в запит. Розглянемо тригер, який буде викликатися до (BEFORE) вставки нових записів в таблицю orders. Основне завдання тригера полягає в обмеженні числа замовлених товарів до 1 (приклад).

Приклад. Використання ключового слова BEFORE

CREATE TRIGGER restrict_count BEFORE INSERT ON orders

FOR EACH ROW BEGIN

SET NEW.number = 1;

END  //

 

INSERT INTO orders VALUES (NULL,1,NOW(),2,10)//

Як видно з прикладу, незважаючи на те, що замовлення оформлялося на дві товарні позиції, тригер restrict_count змінив значення поля number на 1.

 Часто при оновленні одних полів таблиці оператори баз даних забувають оновити пов'язані поля таблиці або здійснюється спроба додавання некоректних значень. Нехай при додаванні нового клієнта необхідно перетворити імена та по батькові клієнтів в ініціали. Тригер для обробки цієї ситуації може глядати так, як це представлено в наступному прикладі.

Приклад. Контролюючий тригер.

CREATE TRIGGER restrict_user BEFORE INSERT ON users

FOR EACH ROW

BEGIN

SET NEW.name = LEFT(NEW.name,1);

SET NEW.patronymic = LEFT(NEW.patronymic,1);

END//

 

INSERT INTO users VALUES (NULL, 'Ремизов', 'Алексеевич', 'Сергей',

'83-89-00'f NULL, NULL, 'active')//

 

 SELECT surname, patronymic, name FROM users

WHERE id_user = LAST_INSERT_ID()//

 

surname       patronymic name

Ремизов    A                   С

Як видно з прикладу, ім'я та по батькові кожного нового відвідувача урізується до однієї літери. Для того щоб ім'я та по батькові не було відредаговано за допомогою оператора update, слід також створити тригер, прив'язаний до події update.

Оператор DROP TRIGGER

Оператор DROP TRIGGER дозволяє видаляти існуючі тригери і має наступний синтаксис:

DROP TRIGGER tbl_name.trigger_name

Оператор видаляє тригер з ім'ям trigger_name таблиці tbl_name. У прикладі демонструється видалення тригера restrict_user таблиці users, створеного в прикладах вище.

Приклад. Видалення тригера

DROP TRIGGER users.restrict_user;

Tags: 
mySQL (4 курс)