Devs Hive
Повернутися до всіх запитань

Що таке Triggers в базах даних?

JuniorMiddleDatabases
Зустрічали на інтервʼю:1 користувач

Triggers у базах даних - це спеціальні процедури, які автоматично виконуються у відповідь на певну подію (INSERT, UPDATE, DELETE) в таблиці або view.

Розберемо приклад

Уявімо, що ми маємо таблицю users і таблицю user_logs. Ми можемо створити trigger, який буде записувати подію під час створення нового користувача. Замість того, щоб реалізовувати цю логіку в сервісі, надійніше використати trigger, який виконуватиметься на рівні бази даних.

-- users
id | name | email
-------------------
1 | Alice | alice@test.com
2 | Bob | bob@test.com


-- user_logs
id | user_id | action | created_at
------------------------------------------
1 | 1 | USER_CREATED | 2025-01-01 10:00:00

Для того щоб реалізувати trigger, ми можемо використати наступний SQL-код:

CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO user_logs (user_id, action, created_at)
VALUES (NEW.id, 'USER_CREATED', NOW());
END;

Ми створюємо trigger з назвою after_user_insert, який буде автоматично спрацьовувати після додавання нового запису в таблицю users.

AFTER INSERT ON users означає, що він виконується після операції INSERT у таблиці users.

FOR EACH ROW означає, що trigger буде запускатися для кожного нового рядка окремо. Усередині trigger ми записуємо дані в user_logs.

NEW.id - це значення id нового користувача, якого щойно було додано в таблиці users.

BEFORE і AFTER

Як видно з SQL-коду вище, ми використовуємо AFTER, тобто trigger виконується після операції INSERT. Загалом, trigger може виконуватися як після певної операції, так і перед нею. Для того, щоб виконати його перед операцією, потрібно використати ключове слово BEFORE.

Зустрічав на інтервʼю?

Повʼязані питання

DatabasesJunior/Middle/Senior

Що таке referential actions в SQL?

Одне з запитань, яке вводить Node.js розробника в легкий ступор. Чомусь так склалося, що SQL і розуміння реляційних баз даних - це ахіллесова пʼята в світі backend-розробки на JavaScript.

Referential actions - це правила, які визначають, що має відбутися з рядками в дочірній таблиці, коли в батьківській таблиці оновлюється або видаляється рядок, на який вони посилаються через foreign key.

В основному є три способи контролю, і зараз ми розберемо їх на прикладі. Уявимо, що в нас є таблички users і orders. В таблиці orders є user_id, який представляє собою foreign key і звʼязує orders з конкретним користувачем.

Якщо ми спробуємо видалити користувача з id=1, на який посилаються рядки з orders, то спрацює механізм референційної цілісності (referential integrity), який визначає, чи можливе видалення і, якщо так, то за якими правилами. Далі ми по черзі розберемо правила, і ви зрозумієте, що це дуже просто і зрозуміло.

// users
+----+--------+
| id | name |
+----+--------+
| 1 | Ivan |
| 2 | Maria |
+----+--------+

// orders
+----+---------+----------+
| id | user_id | product |
+----+---------+----------+
| 10 | 1 | Book |
| 11 | 1 | Laptop |
| 12 | 2 | Phone |
+----+---------+----------+

RESTRICT

Якщо на рядок з users посилається хоча б один рядок з orders, ми не зможемо видалити. Єдиний спосіб видалити такий запис - це спочатку видалити дані з orders, а тільки після того referential integrity дозволить видалити користувача.

CASCADE

Найбільш небезпечне правило, оскільки воно означає, що при видаленні рядка з таблиці users будуть видалені всі рядки з orders, які посилаються на користувача.

SET NULL

При видаленні користувача з таблиці users всім orders, які на нього посилаються, буде записано NULL в foreign key (user_id).

Всі ті самі правила застосовуються і для UPDATE-операції, але так як це використовується доволі рідко, на інтервʼю вас будуть запитувати саме про DELETE.

DatabasesJuniorMiddle

Що таке foreign key в SQL?

Foreign Key (зовнішній ключ) - це поле у таблиці, яке посилається на первинний ключ (Primary Key) в іншій таблиці. Найважливіше, що потрібно розуміти - це те, що Foreign Key не просто рядок з ID, який вказує на конкретний запис в іншій таблиці, а спеціальний механізм, який гарантує цілісність даних (referential integrity).

Простий приклад

В нас є таблиця users i orders. Користувач може створювати замовлення, і відповідно запис в таблиці orders має бути привʼязаний до конкретного запису в users. Для того, щоб звʼязати їх, ми будемо використовувати поле orders.user_id і таким чином будемо розуміти, хто створив замовлення.

users
+----+-------+
| id | name |
+----+-------+
| 1 | Ivan |
| 2 | Maria |
+----+-------+

orders
+----+---------+---------+
| id | user_id | product |
+----+---------+---------+
| 1 | 1 | Laptop |
| 2 | 1 | Mouse |
| 3 | 2 | Phone |
+----+---------+---------+

З практичної точки зору, ми можемо реалізувати цю привʼязку і без Foreign Key, просто записуючи ID як число в поле user_id. Але цей підхід має декілька значних мінусів:

  1. База даних не контролює звʼязки між таблицями. Наприклад, в даному випадку в нас звʼязок one-to-many (один користувач має багато замовлень). Але якщо ми спробуємо зробити вибірку по конкретному користувачу, база даних буде сканувати всю таблицю, так як вона не знає, які замовлення відносяться до користувача, бо user_id це просто число, а не посилання на іншу таблицю.
  2. Немає захисту від неіснуючих даних. Якщо ми захочемо записати в user_id число 100, ми не отримаємо ніякої помилки про те, що такого користувача не існує.
  3. Немає контролю при видаленні чи оновленні користувача. Якщо при видаленні користувача нам потрібно буде видаляти всі його замовлення, то без Foreign Key ми будемо змушені робити це в бізнес-логіці. Тоді як зовнішній ключ реалізовує механізм referential actions, який дозволяє контролювати процес. Детально про цей механізм тут.

Всі ці проблеми вирішує Foreign Key, який забезпечує тісний звʼязок між таблицями і дозволяє нам реалізувати звʼязки між ними.

Junior/Middle/SeniorDatabases

Що таке поліморфні (Polymorphic) звʼязки?

Поліморфний зв’язок - це тип відношення, коли один запис може бути пов’язаний із записами з різних таблиць через одну універсальну структуру. Простішою мовою, одна таблиця може посилатися на різні таблиці, а не тільки на одну.

Розберемо приклад

Уявимо, що ми створюємо соціальну мережу і в нас є такі сутності, як posts, photos, videos. Нам потрібно реалізувати можливість додавати коментарі до цих сутностей. Найбільш очевидні способи реалізувати таку задачу це:

  1. Зробити окрему таблицю під кожен тип контенту, наприклад, post_comments, photo_comments, video_comments і так далі.
  2. Також можна зробити універсальну таблицю comments і звʼязати її з іншими через foreign key. Наприклад, postId, photoId, videoId.

Ці способи робочі, але при додаванні нових типів контенту нам доведеться або створювати нові таблиці в базі даних, або створювати нові foreign keys, що ускладнить бізнес-логіку API і структуру даних в БД.

Популярним рішенням в подібних ситуаціях є поліморфний звʼязок. Суть його в тому, що ми будемо мати одну універсальну таблицю comments з колонками entity_id і entity_type.

  1. entity_id - ідентифікатор запису, до якого ми додали коментар. Важливо розуміти, що це не foreign key, а просто число чи рядок (якщо використовуємо UUID чи подібний вид ID).
  2. entity_type - тип контенту, до якого ми лишили коментар. Наприклад, post чи photo. Ця колонка потрібна нам для того, щоб розуміти, по якій таблиці робити пошук. Наприклад, ми хочемо вибрати всі коментарі до post з ID=1, і таким чином ми відфільтруємо тільки ті коментарі, що мають entity_type=post.
posts
+----+-------------+
| id | title |
+----+-------------+
| 1 | My trip |
+----+-------------+

photos
+----+------------------+
| id | url |
+----+------------------+
| 5 | photo.jpg |
+----+------------------+

comments
+----+----------------+----------------+------------------+
| id | text | entity_id | entity_type |
+----+----------------+----------------+------------------+
| 1 | Nice post! | 1 | post |
| 2 | Beautiful pic | 5 | photo |
+----+----------------+----------------+------------------+

Основне, що потрібно розуміти - це те що в світі не буває ідеальних рішень і якщо ви використовуєте поліморфні звʼязки ви отримуєте гнучкість і відсутність дублювання, але жертвуєте наступним:

  1. Referential integrity - так як в нас відсутні зовнішні ключі, база даних не може гарантувати цілісність даних і звʼязків між ними. Частково цю проблему можна вирішити додаванням CHECK і Partial Indexes, але це більше костиль, ніж реальне рішення.
  2. JOIN - запити в базу даних стають складнішими і повільнішими через відсутність зовнішніх ключів. Ця проблема стосується систем з великою кількістю даних і інтенсивним навантаженням, тому в таких випадках краще використовувати способи, про які я згадував спочатку.
  3. CASCADE - так як вони неможливі без зовнішніх ключів, нам потрібно реалізовувати їх на рівні бізнес-логіки, що має прямий вплив на роботу транзакцій, так як порушує консистентність даних (Consistency, якщо говорити про ACID).

Коментарі (0)

Увійдіть, щоб залишити коментар

Поки що немає коментарів. Будьте першим!