Взаимодействие триггеров с функциями PL/pgSQL: OLD, NEW, TG_OP

Триггеры в PostgreSQL позволяют не только запускать функции в ответ на какие-то действия, они также передают в эти функции чудесные переменные. Именно благодаря этим переменным можно узнать, что было с данными в таблице до операции, что стало после неё, и какая операция вообще произошла.

• OLD — содержит старые данные строки таблицы до выполнения операции. Используется в триггерах для UPDATE и DELETE, потому что в случае INSERT ничего "старого" просто не существует.
• NEW — содержит новые данные строки таблицы после выполнения операции. Используется в триггерах для INSERT и UPDATE.
• TG_OP — содержит текстовую информацию о текущей операции: INSERT, UPDATE, или DELETE.

Все эти переменные автоматически доступны внутри функции, связанной с триггером.

Теория без практики — как SQL без индексов: медленно и печально. Так что разберёмся на практических примерах.

Использование OLD для доступа к старым данным

Представьте, что у нас есть таблица students. И в ней кто-то исправляет возраст студента (вдруг в голове закралась ошибка, что студенту ещё 20 лет, а не все 25).

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL
);

Чтобы отследить, что за изменения произошли, создадим таблицу логов:

CREATE TABLE student_changes (
    change_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    old_value INT,
    new_value INT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Теперь идём дальше: создаём функцию, которая будет записывать изменения. Вот тут-то и пригодится OLD:

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Логируем изменения возраста
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (OLD.id, OLD.age, NEW.age);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

А теперь делаем триггер:

CREATE TRIGGER student_age_update
AFTER UPDATE OF age ON students
FOR EACH ROW
WHEN (OLD.age IS DISTINCT FROM NEW.age) -- Выполняем только если возраст изменился
EXECUTE FUNCTION log_student_changes();

Давайте добавим студента, а потом внесём изменения:

INSERT INTO students (name, age) VALUES ('Алиса', 20);

UPDATE students
SET age = 25
WHERE name = 'Алиса';

-- Проверяем лог изменений:
SELECT * FROM student_changes;

Вы увидите, что в таблицу логов записалось изменение: возраст с 20 на 25. Магия? Нет, OLD.

Использование NEW для новых данных

Теперь представим, что мы хотим при добавлении нового студента автоматически записывать его ID и имя в таблицу логов (да, это паранойя, но иногда полезная):

CREATE OR REPLACE FUNCTION log_new_student()
RETURNS TRIGGER AS $$
BEGIN
    -- Логируем данные нового студента
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (NEW.id, NULL, NEW.age); -- Старого значения нет, так как это INSERT
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER student_insert_log
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION log_new_student();

Опять же, добавим нового студента и проверим:

INSERT INTO students (name, age) VALUES ('Боб', 22);

-- Проверяем лог:
SELECT * FROM student_changes;

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

Использование TG_OP для определения типа операции

Но что, если мы хотим иметь универсальный триггер для логирования, который сможет обрабатывать и INSERT, и UPDATE, и даже DELETE? Здесь на помощь приходит переменная TG_OP.

Создадим универсальную функцию:

CREATE OR REPLACE FUNCTION log_all_operations()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (NEW.id, NULL, NEW.age);

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NEW.age);

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NULL);

    END IF;

    RETURN NULL; -- Для AFTER триггера на DELETE возвращаем NULL
END;
$$ LANGUAGE plpgsql;

Создаём триггер, который будет срабатывать на все три операции:

CREATE TRIGGER universal_student_log
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_all_operations();

Добавляем, изменяем и удаляем студента:

INSERT INTO students (name, age) VALUES ('Чарли', 30);
UPDATE students SET age = 31 WHERE name = 'Чарли';
DELETE FROM students WHERE name = 'Чарли';

-- Проверяем лог:
SELECT * FROM student_changes;

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

Типичные ошибки при использовании OLD, NEW, TG_OP

Работая с триггерами, можно напороться на несколько распространённых проблем:

"Почему OLD не работает при вставке?" Это поведение по умолчанию: для INSERT нет старых данных. Используйте NEW.

"Что делать, если NEW не работает при удалении?" Опять же, это ожидаемое поведение: для DELETE нет новых данных. Используйте OLD.

Логика триггера вызывает бесконечную рекурсию. Проверяйте, чтобы триггер случайно не вызывал сам себя. Для этого можно прописывать чёткие условия в блоке WHEN или проверять TG_OP.

2

Задача

SQL SELF, 57 уровень, 4 лекция

Недоступна

Логирование изменений возраста студента

Логирование изменений возраста студента

Открыть

1

Опрос

Введение в триггеры, 57 уровень, 4 лекция

Недоступен

Введение в триггеры

Введение в триггеры

Открыть