Тригери у базах даних SQL Server являють собою спеціалізовані об’єкти. Фактично, це різновид функцій, які активуються у відповідь на певні події, що відбуваються в базі даних.
Цей посібник надасть вам вичерпну інформацію про тригери SQL, які можуть виявитися надзвичайно корисними у вашій роботі. Розпочнемо!
Що таке SQL-тригери?
Термін “тригер” описує інструкцію, за якою сервер автоматично виконує заданий набір дій, коли відбуваються зміни в базі даних.
Тригер є набором SQL-запитів, які мають своє ім’я та зберігаються в пам’яті. Це особливий тип функції, яка автоматично викликається при виникненні будь-якої події в базі даних. Кожен тригер пов’язаний з конкретною таблицею.
Наприклад, тригер може бути активований при додаванні нового стовпця до певної таблиці або при зміні певних записів.
Згідно з Microsoft Developer Network, тригери – це специфічний клас збережених процедур. У визначенні тригера спочатку вказується момент його активації, а потім – дії, які він має виконати.
Синтаксис:
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];
Розбір кожного параметра:
CREATE TRIGGER trigger_name– використовується для створення нового тригера або зміни назви вже існуючого.BEFORE/AFTER– вказує, коли тригер має спрацювати: до або після певної події.INSERT/UPDATE/DELETE– визначає тип операції, яка активує тригер (вставка, оновлення, видалення даних).ON tableName– вказує таблицю, з якою пов’язаний тригер.FOR EACH ROW– вказує, що тригер буде виконуватися для кожного зміненого рядка.trigger_body– визначає дії, які тригер повинен виконати.
Тригери – це збережені функції, що мають унікальні ідентифікатори, що дозволяє повторно використовувати вже виконані запити, надійно зберігаючи їх у пам’яті. Розглянемо, навіщо вони потрібні в SQL.
Зазвичай тригери використовуються для автоматизації виконання певного коду при виникненні конкретної події. Іншими словами, тригери є ідеальним рішенням, коли потрібно, щоб певний блок коду виконувався постійно у відповідь на задану подію.
Нижче наведено деякі переваги використання тригерів в операціях із базами даних SQL:
- Проводять додаткові перевірки під час вставки, оновлення або видалення даних із таблиці.
- Зменшують час відповіді, що сприяє збільшенню обчислювальної ефективності.
- Дозволяють реалізувати складні параметри за замовчуванням, недоступні за допомогою звичайних обмежень.
Забезпечення цілісності даних є важливою властивістю реляційних баз даних. Це означає, що дані, що зберігаються в базі, повинні бути точними для кожної транзакції та кожної операції.
Якщо дві таблиці розташовані в різних базах даних або системах, не завжди можливо гарантувати цілісність даних за допомогою звичайних обмежень. У таких ситуаціях тригери стають єдиним варіантом для реалізації перевірок.
Комбінація аргументів тригера
Для кожної таблиці можна визначити шість різних типів тригерів. Це комбінації аргументів, які використовуються в тригерах рівня рядків SQL:
BEFORE INSERT: ці тригери виконують дії над рядками до того, як відбудеться будь-яка операція INSERT у вказаній таблиці чи базі даних.
AFTER INSERT: виконують дії над рядками відразу після операції INSERT.
BEFORE UPDATE: з цими тригерами функція для рядків виконується до операції UPDATE.
AFTER UPDATE: виконують дії над рядками безпосередньо після будь-якої операції UPDATE.
BEFORE DELETE: виконує задану операцію над рядками до того, як база даних чи таблиця буде піддана операції DELETE.
AFTER DELETE: ці тригери виконують дії над рядками після кожної транзакції DELETE.
Типи тригерів SQL
Тригери SQL є збереженими функціями, які автоматично запускаються при виникненні певних подій. Це нагадує механізм планування, керованого подіями. Нижче наведено ситуації, які можуть ініціювати виконання тригерів:
Тригери DML: DML означає мову маніпулювання даними. Виконання коду у відповідь на модифікацію даних стає можливим завдяки тригерам DML. Ці тригери активуються, коли виконуються команди DML, такі як INSERT, UPDATE та DELETE. Їх ще називають “тригерами рівня таблиці”.
Тригери DDL: DDL означає мову визначення даних. Тригери DDL дозволяють запускати код у відповідь на зміни структури бази даних, такі як додавання чи видалення таблиць, або на події на сервері, наприклад, коли користувач входить в систему. Вони називаються “тригерами рівня бази даних”.
Ці тригери можуть активуватися, коли певні оператори DDL, такі як CREATE, ALTER або DROP виконуються в активній базі даних. Їх також можна використовувати для відстеження дій та управління ними.
Тригери входу в систему: тригери входу в систему автоматично запускаються при будь-якій події LOGON (запуск, вхід, вихід з системи, завершення роботи). Вони виконуються після аутентифікації користувача, але до початку його транзакцій. Тригери LOGON не спрацьовують, якщо авторизація не вдається.
Ці тригери можуть використовуватися для ведення історії входів або встановлення обмежень для певного користувача, а також для інших функцій аудиту та управління ідентифікацією підключень до сервера.
Тригери CLR: CLR означає Common Language Runtime. Тригери CLR є унікальною підмножиною тригерів, розроблених на основі CLR у середовищі .NET. Ці тригери корисні, коли тригеру потрібно виконати велику кількість обчислень або взаємодіяти з іншою сутністю, відмінною від SQL.
Тригери DML і DDL можуть бути розроблені з використанням підтримки кодування тригерів CLR у технологіях .NET, таких як Visual Basic, C# і F#.
Приклад тригера SQL Server
Розглянемо ці концепції тригерів на практичному прикладі.
Спершу створимо базу даних за допомогою операторів SQL:
CREATE DATABASE testdb; use testdb;
Тут “testdb” – це назва бази даних. Далі створимо таблицю.
CREATE TABLE student( name varchar(25), id int(2), maths int(2), physics int(2), biology int(2), social int(2), total int(2) );
Створено таблицю для зберігання інформації про студентів. Нижче показано команду для опису структури таблиці. “студент” – це назва таблиці.
DESC student;
Структура створеної таблиці:
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(25) | YES | | NULL | | | id | int | YES | | NULL | | | maths | int | YES | | NULL | | | physics | int | YES | | NULL | | | biology | int | YES | | NULL | | | social | int | YES | | NULL | | | total | int | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
Після створення таблиці потрібно налаштувати тригер. Скористаємося аргументом BEFORE INSERT.
Назва тригера – “marks”. Кожного разу, коли оцінки студента змінюються, тригер намагається автоматично обчислити загальну оцінку студента.
CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;
Оскільки потрібно замінити дані рядків, а не працювати зі старими, “total” визначено за допомогою нового імені класу, а всі наступні вирази мають префікс new. Додамо значення до кожного рядка та подивимося результати. Спочатку загальна оцінка дорівнює 0 для кожного студента.
INSERT INTO student VALUES("George",02,99,87,92,91,0);
INSERT INTO student VALUES("James",03,91,81,94,90,0);
INSERT INTO student VALUES("Harry",04,86,70,73,88,0);
INSERT INTO student VALUES("John",05,73,89,78,92,0);
INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);
У цьому випадку оператор тригера запускатиметься автоматично, коли дані вставляються в таблицю student. Загальна кількість балів кожного студента буде обчислена тригером. Перевіримо, чи запускається тригер, за допомогою оператора SELECT.
SELECT * FROM table_name;
І ось кінцевий результат:
mysql> select * from student; +--------+------+-------+---------+---------+--------+-------+ | name | id | maths | physics | biology | social | total | +--------+------+-------+---------+---------+--------+-------+ | George | 2 | 91 | 81 | 94 | 90 | 356 | | James | 3 | 86 | 70 | 73 | 88 | 317 | | Harry | 4 | 73 | 89 | 78 | 92 | 332 | | John | 5 | 94 | 75 | 69 | 79 | 317 | | Lisa | 1 | 99 | 87 | 92 | 91 | 369 | +--------+------+-------+---------+---------+--------+-------+ 5 rows in set (0.00 sec)
Як видно з результату, всі оцінки з предметів автоматично додаються для кожного студента. Отже, тригер було успішно активовано.
Додаткові операції з тригерами
За допомогою тригерів можна виконувати багато операцій. Деякі з них прості, інші складніші, але їх легко зрозуміти після вивчення запитів. За допомогою операторів Transact-SQL ви можете вмикати, вимикати та видаляти тригери, використовуючи наступні команди.
Запит для перевірки наявності певного тригера
Ця команда перевіряє наявність вказаного тригера у всій базі даних.
SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'
Запит для відображення тригерів
Усі тригери, доступні в активній базі даних, будуть показані за допомогою наступного оператора:
SHOW TRIGGERS;
Запит для вимкнення тригера
Наведена нижче команда вимикає тригер в активній базі даних.
DISABLE TRIGGER trigger_name ON DATABASE;
Також можна вказати конкретну таблицю, щоб вимкнути тригер.
DISABLE TRIGGER trigger_name ON table_name;
Запит для ввімкнення тригера
Наступна команда спочатку вимикає тригер, визначений для вказаної таблиці, а потім знову вмикає його.
ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name
Тригер потрібно вимкнути перед спробою ввімкнути.
Запит для ввімкнення або вимкнення всіх тригерів у таблиці
Використовуючи наведений вище оператор SQL, можна вимкнути або ввімкнути всі тригери таблиці одночасно, замінивши “ALL” замість конкретного імені тригера.
ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL
Запит для видалення або скидання тригера
Тригер можна видалити, скинувши його або всю таблицю. Усі пов’язані тригери також видаляються при видаленні таблиці.
DROP TRIGGER [trigger_name];
Коли тригер видаляється, пов’язані з ним дані видаляються з таблиці даних sys.objects.
Переваги тригерів
- Створювати тригери нескладно, і вони можуть викликати збережені функції та методи.
- Користувачі можуть реалізувати простий аудит за допомогою тригерів.
- Обмеження між сутностями в системах баз даних можна реалізувати за допомогою тригерів.
- Обмеження цілісності даних можна застосовувати за допомогою тригерів.
- Якщо потрібна групова перевірка, а не перевірка рядків за рядками введених або змінених даних, тригери можуть бути дуже корисними.
Недоліки тригерів
Тригери SQL можуть бути не найкращим вибором у деяких ситуаціях через свої обмеження.
- Тригери повинні бути точно задокументовані.
- Через одночасне виконання бази даних, яке може бути недоступним для компонентів програми, тригери можуть бути складними для налагодження.
- Інструкції DML стають складнішими при використанні тригерів.
- Навіть незначні проблеми з тригером можуть призвести до логічних помилок.
Висновок
Тригери є дуже корисними компонентами Transact-SQL та SQL, і їх також можна використовувати в Oracle. Застосування тригерів є важливим при виклику збережених методів. Ці тригери SQL дозволяють аналізувати часові рамки активності та визначати, як на них реагувати. Також можна перевірити певну таблицю, пов’язану з тригером, для отримання даних.
Тригери можуть бути рекурсивними. Коли тригер у таблиці виконує команду в батьківській таблиці, запускається друга ітерація тригера, що називається рекурсивним тригером. Це допомагає при спробі розв’язати кореляцію ідентичності.
Крім того, тригери регулюють шаблон оновлень, які база даних може приймати. Це дуже корисно для підтримки цілісності даних, якщо обмеження SQL, такі як первинний та зовнішній ключі, не існують.
Сподіваюся, ця стаття допомогла вам більше дізнатися про SQL-тригери.
Якщо ви хочете глибше вивчити бази даних, ось кілька чудових ресурсів для вивчення SQL і NoSQL.