Тригери на рівні рядків і таблиць: FOR EACH ROW vs FOR EACH STATEMENT

Якщо ти коли-небудь стикався з ситуацією, коли треба виконати якусь дію для кожного рядка при масовому оновленні даних або ж тільки один раз для всієї таблиці, то, мабуть, мав дилему: як саме це краще реалізувати? PostgreSQL пропонує два варіанти: тригери на рівні рядків і тригери на рівні операцій. Розуміння, коли застосовувати кожен із цих підходів, важливе для правильного проєктування бази даних, оптимізації її продуктивності й уникнення помилок. Давай розберемося!

Тригери, які працюють на рівні рядків (FOR EACH ROW), спрацьовують кожного разу для кожного рядка, якого торкається операція INSERT, UPDATE або DELETE. Це означає, що якщо SQL-запит зачіпає 100 рядків, тригер буде виконаний 100 разів.

Коли їх використовувати?

Тригери на рівні рядків корисні, якщо треба обробити кожен змінений рядок окремо. Наприклад:

• Логування змін для кожного рядка.
• Автоматичне оновлення пов’язаних даних для кожного рядка.

Приклад: логування змін для кожного рядка

Припустимо, у нас є таблиця студентів:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ми хочемо логувати кожен рядок, який оновлюється в цій таблиці, в окрему таблицю students_log:

CREATE TABLE students_log (
    log_id SERIAL PRIMARY KEY,
    student_id INT,
    old_name VARCHAR(100),
    new_name VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Функція для логування змін:

CREATE OR REPLACE FUNCTION log_student_update()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO students_log(student_id, old_name, new_name, changed_at)
    VALUES (OLD.id, OLD.name, NEW.name, CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Створення тригера FOR EACH ROW:

CREATE TRIGGER student_update_logger
AFTER UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_update();

Тестування:

UPDATE students
SET name = 'Іван Іванов'
WHERE id = 1;

Після виконання цього запиту, в таблиці students_log з’явиться запис з деталями змін.

Тригери на рівні операцій (FOR EACH STATEMENT)

Тригери на рівні операцій (FOR EACH STATEMENT) спрацьовують один раз для всього SQL-запиту незалежно від кількості затронутих рядків. Якщо запит оновлює 100 рядків, тригер виконається лише один раз.

Тригери на рівні операцій корисні, якщо тобі потрібно:

• Виконати дію лише один раз для всієї операції.
• Працювати з агрегованими даними або виконувати обчислення для всієї таблиці.

Приклад: оновлення лічильника змін

Допустимо, у нас є таблиця лічильника змін для таблиці students:

CREATE TABLE students_changes_log (
    total_changes INT DEFAULT 0
);
INSERT INTO students_changes_log(total_changes) VALUES (0);

Ми хочемо збільшувати значення цього лічильника кожного разу, коли виконується операція UPDATE в таблиці students.

Функція для оновлення лічильника:

CREATE OR REPLACE FUNCTION increment_changes_counter()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE students_changes_log
    SET total_changes = total_changes + 1;
    RETURN NULL; -- Тригер на рівні операцій не повертає рядки
END;
$$ LANGUAGE plpgsql;

Створення тригера FOR EACH STATEMENT:

CREATE TRIGGER update_changes_counter
AFTER UPDATE ON students
FOR EACH STATEMENT
EXECUTE FUNCTION increment_changes_counter();

Тестування:

UPDATE students
SET age = age + 1
WHERE age < 20;

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

Порівняння FOR EACH ROW і FOR EACH STATEMENT

Коли використовувати FOR EACH ROW і FOR EACH STATEMENT?

Використовуй FOR EACH ROW, якщо:

1. Ти хочеш, щоб тригер виконувався для кожного рядка.
2. Логіку виконання треба прив’язати до змін конкретних рядків.
3. Тобі потрібен доступ до даних OLD і NEW для кожного рядка.

Приклад: Логування змін у таблиці або автоматичне створення пов’язаних записів.

Використовуй FOR EACH STATEMENT, якщо:

1. Ти хочеш виконати дію лише один раз для всієї операції.
2. Логіка тригера не залежить від змін конкретних рядків.
3. Продуктивність має критичне значення, і масовий виклик тригера небажаний.

Приклад: Оновлення лічильників, обчислення метаданих для таблиці.

Помилки і важливі моменти

Використання правильного типу тригера може бути неочевидним, і ось що треба враховувати:

1. Одна з найчастіших помилок — спроба використовувати дані OLD і NEW у тригері FOR EACH STATEMENT. Це викличе помилку, бо ці змінні доступні тільки в тригерах рівня рядків.
2. Тригери рівня рядків (FOR EACH ROW) можуть сильно сповільнювати операції, якщо запит зачіпає велику кількість рядків. Завжди враховуй продуктивність.
3. Будь обережний з потенційною рекурсією тригерів. Наприклад, якщо тригер викликає зміну даних у тій же таблиці, це може призвести до нескінченного циклу.