Тригер для автоматичного оновлення last_modified поля при зміні запису

Уяви, що ти розробляєш додаток для керування студентами та курсами, і у тебе є таблиця students. В цій таблиці є поле last_modified, яке має автоматично оновлюватися кожного разу, коли змінюються дані в записі (наприклад, оновлення імені студента чи його віку).

Замість того, щоб вручну прописувати оновлення last_modified у кожному SQL-запиті, ми створимо тригер, який зробить це за нас.

Структура таблиці students

Для початку давай створимо таблицю students, яка буде використовуватись у прикладі. Ця таблиця містить базову інформацію про студентів:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- Унікальний ідентифікатор студента
    name VARCHAR(100) NOT NULL,   -- Ім'я студента
    age INT,                      -- Вік студента
    last_modified TIMESTAMP NOT NULL DEFAULT NOW() -- Час останньої зміни
);

• Поле last_modified спочатку заповнюється поточним часом (NOW()) при створенні запису.
• Це поле буде автоматично оновлюватись при зміні даних студента.

Заповнимо таблицю кількома тестовими даними:

INSERT INTO students (name, age)
VALUES 
    ('Отто Лін', 20),
    ('Марія Чі', 22),
    ('Алекс Сонг', 19);

Тепер дані в таблиці виглядають так:

Створення функції для оновлення last_modified

Функція на PL/pgSQL буде використовуватись тригером для оновлення значення поля last_modified. Вона викликається автоматично перед виконанням зміни в записі.

Створимо функцію update_last_modified:

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- Оновлюємо поле last_modified на поточний час
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• NEW — це спеціальна змінна, яка містить нові дані запису (після його зміни).
• Ми задаємо для NEW.last_modified значення NOW() (поточна дата і час).
• Функція повертає оновлену змінну NEW, що потрібно для коректної роботи тригера.

Створення тригера

Тепер створимо тригер, який буде автоматично викликати функцію update_last_modified при кожному оновленні запису в таблиці students.

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Що тут відбувається:

• BEFORE UPDATE означає, що тригер спрацьовує до виконання операції оновлення.
• FOR EACH ROW означає, що тригер спрацьовує для кожного зміненого рядка.
• EXECUTE FUNCTION update_last_modified() вказує, що треба викликати функцію update_last_modified.

Тестування тригера

Тепер давай перевіримо, як працює наш тригер. Виберемо поточні дані з таблиці students:

SELECT * FROM students;

Результат:

Тепер оновимо вік студента з student_id = 1:

UPDATE students
SET age = 21
WHERE student_id = 1;

Знову виберемо дані з таблиці:

SELECT * FROM students;

Очікуваний результат:

Зверни увагу: поле last_modified для запису з student_id = 1 оновилось на поточний час, а інші записи залишились без змін.

Розширення логіки тригера

Припустимо, тепер ми хочемо, щоб поле last_modified оновлювалось тільки у випадку зміни певних стовпців. Наприклад, якщо змінюється лише ім'я студента або його вік, тригер має спрацьовувати, а у випадку інших змін — ні.

Для цього можна додати умову з оператором WHEN у визначення тригера.

Створимо новий тригер з умовою:

DROP TRIGGER IF EXISTS set_last_modified ON students;

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name OR OLD.age IS DISTINCT FROM NEW.age)
EXECUTE FUNCTION update_last_modified();

Тут:

• Умова WHEN перевіряє, чи відрізняються старі значення (OLD) від нових (NEW) для стовпців name і age.
• Якщо жоден з цих стовпців не змінився, тригер не спрацьовує.

Спробуємо знову оновити дані в таблиці і протестувати нову логіку.

Поради щодо використання тригерів

1. Не зловживай тригерами. Вони зручні, але можуть ускладнити логіку бази даних і зробити відлагодження більш складним.
2. Завжди документуй, що робить тригер і для яких випадків він використовується.
3. Використовуй умови WHEN, щоб мінімізувати випадкові виклики тригера.
4. Пам'ятай, що тригери можуть впливати на продуктивність бази даних, особливо якщо таблиця містить багато записів.

Типові помилки при роботі з тригерами

Неправильна зміна даних. Наприклад, забув задати значення для NEW і повернув оригінальні дані без змін.

Неправильні умови. Наприклад, ти забув додати умову WHEN, і тригер спрацьовує навіть тоді, коли змінювати нічого не потрібно.

Рекурсія. Якщо тригер викликає функцію, яка знову викликає тригер, можна випадково створити нескінченний цикл. PostgreSQL має захист від рекурсії, але краще уникати таких ситуацій.

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