Devs Hive
Back to all questions

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

JuniorMiddleDatabases
Seen on interview:1 user

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.

Seen on interview?

Related Questions

DatabasesJunior/Middle/Senior

What are referential actions in SQL?

One of those questions that can leave a Node.js developer slightly stunned. For some reason, SQL and an understanding of relational databases have become the Achilles’ heel of backend development in the JavaScript world.

Referential actions are rules that define what should happen to rows in a child table when a row in the parent table, referenced by a foreign key, is updated or deleted.

There are three main ways to control this, and now we’ll look at them with an example. Let’s imagine we have users and orders tables. In the orders table, there is a user_id field, which is a foreign key that links each order to a specific user.

If we try to delete a user with id = 1, and that user is still being referenced by rows in the orders table, the referential integrity mechanism will come into play. It determines whether the deletion is allowed and, if so, under what rules. Next, we’ll go through those rules one by one, and you’ll see that it’s actually very simple and easy to understand.

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

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

RESTRICT

If a row in users is referenced by at least one row in orders, we won’t be able to delete it. The only way to delete such a record is to first delete the related rows from orders, and only then will referential integrity allow us to delete the user.

CASCADE

The most dangerous rule, as it means that when you delete a row from the users table, all rows in orders that reference that user will also be deleted.

SET NULL

When a user is deleted from the users table, all orders that reference that user will have their foreign key (user_id) set to NULL. The same rules apply to UPDATE operations as well, but since they are used relatively rarely, interviewers will usually ask specifically about DELETE.

DatabasesJuniorMiddle

What is a foreign key in SQL?

Foreign key is a column in a table that references a primary key in another table. The most important thing to understand is that a foreign key is not just an ID value pointing to a specific record in another table - it’s a mechanism that enforces.

Simple example

We have users and orders tables. A user can create orders, so a record in the orders table must be linked to a specific record in users. To connect them, we use the orders.user_id field, which indicates who created the order.

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

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

From a practical standpoint, we could implement this relationship without a foreign key by simply storing the ID as a number in the user_id column. But this approach has several significant downsides:

  1. The database doesn’t enforce the relationship between the tables. For example, in this case we have a one-to-many relationship (one user has many orders). But if we try to query orders for a specific user, the database will scan the entire table, because it doesn’t know which orders belong to that user - user_id is just a number, not a reference to another table.
  2. There’s no protection against non-existent data. If we try to write the value 100 into user_id, we won’t get any error telling us that such a user doesn’t exist.
  3. There’s no control over what happens when a user is deleted or updated. If, when deleting a user, we also need to delete all of their orders, then without a foreign key, we’ll have to handle it in business logic. A foreign key, on the other hand, provides a referential actions mechanism that lets you control this process. You can read more about this mechanism here - https://devs-hive.tech/interview-qa/referential-actions-sql.

All these problems are solved by a foreign key, which enforces a strong relationship between tables and allows us to define relationships between them.

Junior/Middle/SeniorDatabases

What are polymorphic relationships?

A polymorphic relationship is a type of relationship where a single record can be associated with records from different tables through a single universal structure. In simpler terms, one table can reference different tables, not just one.

Let’s look at an example

Imagine we’re building a social network and we have entities like posts, photos, and videos. We need to implement the ability to add comments to these entities. The most obvious ways to implement this are:

  1. Create a separate table for each content type, for example post_comments, photo_comments, video_comments, and so on.
  2. Create a universal comments table and link it to the others via foreign keys - for example, postId, photoId, videoId, and so on.

These approaches work, but when we add new content types, we’ll have to either create new tables in the database or add new foreign keys, which will complicate the API’s business logic and the database schema.

A popular solution in such cases is a polymorphic relationship. The idea is that we have one universal comments table with entity_id and entity_type columns.

  1. entity_id - the identifier of the record the comment was added to. It’s important to understand that this is not a foreign key, but just a number or a string (for example, if you use UUIDs or a similar type of ID).
  2. entity_type - the type of content the comment belongs to, for example post or photo. We need this column to know which table to query. For instance, if we want to fetch all comments for the post with ID=1, we filter only those comments where 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 |
+----+----------------+----------------+------------------+

The main thing to understand is that there are no perfect solutions. If you use polymorphic relationships, you gain flexibility and avoid duplication, but you trade off the following:

  1. Referential integrity - since we don’t have foreign keys, the database can’t guarantee the integrity of the data and the relationships between them. This can be partially addressed by adding CHECK constraints and Partial Indexes, but that’s more of a workaround than a real solution.
  2. JOIN - database queries become more complex and slower due to the lack of foreign keys. This is mainly an issue for systems with large amounts of data and heavy load, so in such cases it’s better to use the approaches I mentioned earlier.
  3. CASCADE - since it isn’t possible without foreign keys, we have to implement it at the business logic level. This directly affects how transactions work, because it can break data consistency (Consistency, in ACID terms).

Comments (0)

Sign in to leave a comment

No comments yet. Be the first!