Тригери — це сутності бази даних у 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 – тут ми визначаємо назву таблиці для налаштування тригера.
- ДЛЯ КОЖНОГО РЯДКА – цей оператор стосується тригера рядка, тобто тригери виконуватимуться щоразу, коли рядок буде змінено.
- trigger_body – визначає дію, яка має бути виконана, коли активується тригер.
Тригери — це збережені функції з відмітними ідентифікаторами, які дозволяють нам повторно використовувати запити, які вже були виконані та безпечно збережені в пам’яті. Тепер давайте спробуємо зрозуміти, навіщо вони потрібні SQL.
Тригери здебільшого використовуються для регулювання розгортання коду щоразу, коли відбувається подія. Іншими словами, використання тригерів є ідеальним вибором, якщо вам потрібно, щоб певний фрагмент коду постійно запускався у відповідь на певну подію.
Нижче наведено деякі переваги використання тригерів в операціях з базою даних SQL.
Посилальна цілісність є ключовою основною властивістю систем реляційних баз даних. Це означає, що дані, які зберігаються в системі бази даних, завжди повинні бути точними для кожної транзакції та кожної операції.
Якщо дві таблиці розташовані в окремих базах даних або системах, неможливо забезпечити перевірку даних у них за допомогою значень обмежень. У такій ситуації тригери є єдиним варіантом для виконання.
Комбінація тригерних аргументів
Для кожної таблиці ми можемо вказати шість різних типів тригерів. Це комбінація аргументів тригера, включена в тригери рівня рядка SQL.
ПЕРЕД ВСТАВЛЕННЯМ: ці тригери виконують дію над рядками перед виконанням будь-яких операцій ВСТАВЛЕННЯ у вказаній таблиці або в базі даних.
ПІСЛЯ ВСТАВЛЕННЯ: виконує дію над рядками відразу після будь-якої дії INSERT бази даних.
ПЕРЕД ОНОВЛЕННЯМ: за допомогою цих тригерів функція для рядків виконується до виконання дії ОНОВЛЕННЯ в базі даних.
ПІСЛЯ ОНОВЛЕННЯ: виконує дію над рядками, що йдуть безпосередньо після будь-якої операції ОНОВЛЕННЯ бази даних або певної таблиці.
ПЕРЕД ВИДАЛЕННЯМ: він виконує певну операцію над рядками ще до того, як база даних або таблиця піддається дії ВИДАЛЕННЯ.
ПІСЛЯ ВИДАЛЕННЯ: ці тригери виконують дію над рядками після кожної транзакції ВИДАЛЕННЯ.
Типи тригерів 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-sharp.
Зразок тригера 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.
Ім’я створеного мною тригера – «марки». Щойно таблицю буде змінено оцінками студента, тригер нижче намагається автоматично визначити загальну оцінку студента.
CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;
Оскільки нам потрібно замінити дані рядків, а не працювати зі старими, ми визначили «total» за допомогою нового імені класу, а всі наступні вирази мають префікс нових ключових слів після total за допомогою оператора крапки. Тепер ми додамо значення до кожного рядка та побачимо результати. Спочатку загальна оцінка становить 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);
У цьому випадку оператор тригера запускатиметься автоматично, коли дані буде вставлено в таблицю студента. Загальна кількість балів кожного студента буде підрахована тригером. Тепер давайте подивимося, чи викликається тригер за допомогою оператора 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, ми можемо дезактивувати або активувати всі тригери таблиці одночасно, замінивши «ВСІ» замість конкретного імені тригера.
ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL
Запит на видалення або скидання тригера
Тригер можна усунути, видаливши його або всю таблицю. Кожен пов’язаний тригер також видаляється, коли видаляється таблиця.
DROP TRIGGER [trigger_name];
Щоразу, коли тригер видаляється, пов’язані дані видаляються з таблиці даних sys.objects.
Переваги тригерів
- Створювати тригери просто, і сам тригер може викликати збережені функції та методи.
- Користувачі можуть реалізувати простий аудит за допомогою тригерів.
- На жаль, ви не можете створити обмеження між сутностями в системах баз даних за допомогою SQL Server, хоча ви можете емулювати роботу обмежень, використовуючи тригери.
- Обмеження цілісності можна застосувати в базах даних за допомогою тригерів.
- Якщо потрібна групова перевірка, а не перевірка рядків за рядками щойно введених або змінених даних, тригери можуть бути корисними.
Недоліки тригерів
Тригери SQL можуть бути не найкращим вибором у деяких ситуаціях через їхні обмеження.
- Тригери повинні бути точно задокументовані.
- Через одночасне виконання бази даних, яка може бути недоступною для компонентів програми, тригери можуть бути складними для налагодження.
- Інструкції DML стають складнішими, коли використовуються тригери.
- Навіть незначна проблема тригера може призвести до логічних помилок у заяві.
Висновок
Тригери є дуже корисними компонентами Transact-SQL і SQL, і ви також можете використовувати їх в Oracle. Використання тригерів має вирішальне значення під час виклику збережених методів. Ці тригери SQL дозволяють нам аналізувати часові рамки діяльності та визначати, як на них реагувати, якщо це необхідно. Ми також можемо перевірити певну таблицю, яка підключена до тригера для отримання даних.
Рекурсія може бути включена тригерами. Кожного разу, коли тригер у таблиці виконує команду в батьківській таблиці, запускається друга ітерація тригера, і це відомо як рекурсивний тригер. Це допомагає під час спроби вирішити кореляцію ідентичності.
Крім того, тригери регулюють шаблон оновлення, який база даних може приймати. Дуже корисно зберігати обмеження цілісності даних у системі бази даних, якщо ключі обмежень SQL не існують, переважно первинний ключ і зовнішній ключ.
Сподіваюся, ця стаття допоможе вам дізнатися про SQL-тригери.
Якщо ви хочете поглиблено вивчити бази даних, ось кілька чудових ресурсів для вивчення SQL і NoSQL.