Створення простого тригера для оновлення даних: BEFORE INSERT

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

Сценарій роботи такий:

1. Коли в таблицю додається новий запис, поле last_modified автоматично отримує поточну дату й час.
2. Ми скористаємося тригером BEFORE INSERT, який спрацює до фізичного додавання даних — тільки в BEFORE-тригері модифікація NEW.* реально потрапляє в таблицю.

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

Спочатку треба створити функцію на мові PL/pgSQL. Ця функція буде оновлювати поле last_modified у нашій таблиці. Функція — це обов'язковий елемент для роботи тригера, бо сам тригер лише вказує, що треба робити, а всю логіку виконує функція.

Отже, почнемо зі створення таблиці:

-- Створюємо таблицю students
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL,
    last_modified TIMESTAMP
);

Тепер створимо функцію для оновлення поля last_modified:

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

Давай розберемо, що ця функція робить:

• CREATE OR REPLACE FUNCTION update_last_modified() — створюємо функцію з іменем update_last_modified. Якщо така функція вже існує, вона буде замінена.
• RETURNS TRIGGER — вказуємо, що функція призначена для використання з тригером.
• NEW.last_modified := NOW(); — оновлюємо поле last_modified за допомогою функції NOW(), яка повертає поточну дату й час.
• RETURN NEW; — повертаємо оновлений запис. Це обов'язковий крок для тригера BEFORE: саме повернутий NEW і буде записано в таблицю.

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

Після створення функції можемо створити сам тригер, прив'язавши його до таблиці students. Ось як це зробити:

-- Створюємо тригер після вставки запису
CREATE TRIGGER set_last_modified
BEFORE INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

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

• CREATE TRIGGER set_last_modified — створюємо тригер з іменем set_last_modified.
• BEFORE INSERT — тригер буде спрацьовувати безпосередньо перед додаванням рядка в таблицю; саме тому модифікація NEW.last_modified у функції реально збережеться (в AFTER-тригері такі зміни мовчки ігноруються).
• ON students — тригер прив'язаний до таблиці students.
• FOR EACH ROW — тригер буде виконуватись для кожного нового рядка, доданого в таблицю.
• EXECUTE FUNCTION update_last_modified(); — виклик функції, яку ми створили раніше.

Примітка: назву тригера (set_last_modified) і функції (update_last_modified) можна обрати будь-яку, але важливо дотримуватись стандартів іменування, щоб код був зрозумілим.

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

Перевіримо, як працює наш тригер. Спочатку додамо кілька записів у таблицю students:

-- Вставляємо дані в таблицю
INSERT INTO students (name, age) VALUES ('Іван Іванов', 20);
INSERT INTO students (name, age) VALUES ('Анна Петрова', 22);

Тепер подивимось, що вийшло в таблиці:

-- Переглядаємо дані в таблиці
SELECT * FROM students;

Очікуваний результат може бути приблизно таким:

Зверни увагу, що поле last_modified автоматично заповнилось поточною датою й часом для кожного запису.

Помилки, які можуть виникнути

1. Помилка: "relation does not exist" при створенні тригера. Ця помилка виникає, якщо таблиця students не створена. Переконайся, що ти створив таблицю перед створенням тригера.
2. Помилка доступу. Якщо користувач бази даних не має прав на створення функцій або тригерів, тригер не буде створений. Перевір привілеї користувача.
3. Відсутність виклику функції в тригері. Якщо ти забудеш вказати EXECUTE FUNCTION update_last_modified(), тригер не зможе виконати потрібні дії.

Покращення тригера: додавання умов

У реальних задачах буває корисно обмежити виконання тригера певними умовами. Наприклад, якщо поле age менше 18, ми не хочемо оновлювати last_modified. Це можна зробити за допомогою умови WHEN:

-- Створюємо тригер з умовою
CREATE TRIGGER set_last_modified
BEFORE INSERT ON students
FOR EACH ROW
WHEN (NEW.age >= 18)
EXECUTE FUNCTION update_last_modified();

Тепер поле last_modified буде оновлюватись тільки для студентів, вік яких >= 18.

Практичне застосування

Тригери, подібні цьому, часто використовуються в реальних проєктах. Ось декілька прикладів:

• Автоматичне оновлення часу останньої зміни запису (як ми зробили).
• Відстеження змін у базі й запис цих змін у таблицю логів.
• Забезпечення цілісності даних, наприклад, перевірка пов'язаних таблиць перед виконанням операції.
• Аудит даних для дотримання правових або корпоративних норм.

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