Триггер для автоматического обновления 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 имеет защиту от рекурсии, но лучше избегать таких ситуаций.

Этот пример показывает, как использование триггеров может упростить задачи автоматического обновления данных. В реальных проектах такая техника часто применяется для журналирования изменений, поддержки целостности данных и автоматизации рутинной работы.