Основи синтаксису тригерів: CREATE TRIGGER, WHEN, EXECUTE FUNCTION

Щоб створити тригер в PostgreSQL, треба визначити такі компоненти:

• Ім'я тригера.
• Тип події (INSERT, UPDATE, DELETE).
• Момент виконання (BEFORE або AFTER).
• Таблиця, до якої він відноситься.
• Функція, яка буде виконана (на PL/pgSQL або іншій мові).

Ось загальна структура команди:

CREATE TRIGGER ім'я_тригера
[BEFORE | AFTER] {INSERT | UPDATE | DELETE}
ON ім'я_таблиці
[FOR EACH ROW | FOR EACH STATEMENT]
WHEN (умова)
EXECUTE FUNCTION ім'я_функції();

Приклад простого тригера

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

Для початку створимо таблицю students

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Що тут відбувається? Ми створили таблицю з полями id, name і last_modified. Поле last_modified буде зберігати дату і час останньої зміни запису.

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

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

Що це за магія?

1. NEW — спеціальна змінна, яка зберігає нові значення рядка (для подій INSERT або UPDATE).
2. CURRENT_TIMESTAMP — функція, що повертає поточну дату і час.
3. RETURN NEW — повертає змінений рядок для подальшого збереження.

Тепер створимо сам тригер:

CREATE TRIGGER set_last_modified
AFTER INSERT
ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Розшифровка:

• AFTER INSERT: тригер спрацьовує після додавання нового рядка.
• ON students: тригер застосовується до таблиці students.
• FOR EACH ROW: тригер спрацьовує для кожного нового рядка.
• EXECUTE FUNCTION: вказує, яку функцію треба викликати.

Перевірка роботи тригера

Давай перевіримо, як працює наш тригер:

INSERT INTO students (name) VALUES ('Alice');
SELECT * FROM students;

Ти побачиш приблизно такий результат:

Тригер автоматично оновив поле last_modified. Магія? Та ні, просто PostgreSQL.

Використання умов з WHEN

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

Давай розглянемо приклад, де тригер виконується тільки для певних значень.

Припустимо, ми хочемо, щоб тригер спрацьовував тільки для студентів з ім'ям "Alice". Змінимо наш тригер:

CREATE OR REPLACE FUNCTION update_last_modified_condition()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_modified := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_last_modified_condition
AFTER INSERT
ON students
FOR EACH ROW
WHEN (NEW.name = 'Alice')
EXECUTE FUNCTION update_last_modified_condition();

Тепер тригер оновить поле last_modified тільки для студентів з ім'ям "Alice".

Перевіримо:

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO students (name) VALUES ('Bob');
SELECT * FROM students;

Результат:

Зверни увагу: для студента "Bob" поле last_modified залишилось порожнім, бо тригер не спрацював.

Зв'язок тригера з функцією: EXECUTE FUNCTION

Функція — це серце будь-якого тригера. Тригер не може існувати без функції, яка визначає його логіку. В PostgreSQL функції можна писати на мові PL/pgSQL або інших підтримуваних мовах, таких як Python чи C.

Давай наведемо приклад використання PL/pgSQL-функції.

Створимо функцію, яка логуватиме зміни в окремій таблиці audit_log.

Спочатку створимо таблицю audit_log

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    operation TEXT NOT NULL,
    student_id INTEGER NOT NULL,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

А тепер – функцію:

CREATE OR REPLACE FUNCTION log_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (operation, student_id)
    VALUES ('INSERT', NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Тепер напишемо тригер:

CREATE TRIGGER log_student_insert
AFTER INSERT
ON students
FOR EACH ROW
EXECUTE FUNCTION log_insert();

І перевіримо роботу:

INSERT INTO students (name) VALUES ('Charlie');
SELECT * FROM audit_log;

Ти побачиш приблизно такий результат:

Тригер автоматично записав лог про новий запис.

Помилки та особливості роботи з тригерами

Помилка: відсутність функції. Якщо ти спробуєш створити тригер без функції, PostgreSQL видасть помилку. Завжди створюй функцію до створення тригера.

Проблеми з продуктивністю. Велика кількість тригерів або складні функції можуть пригальмовувати базу даних. Використовуй їх обережно.

Рекурсія. Якщо тригер змінює ту ж таблицю, в якій він спрацьовує, це може викликати нескінченний цикл. Використовуй умови WHEN, щоб цього уникнути.