Триггеры на уровне строк и таблиц: 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. Будьте аккуратны с потенциальной рекурсией триггеров. Например, если триггер вызывает изменение данных в той же таблице, это может привести к бесконечному циклу.