Основы синтаксиса триггеров: 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, чтобы этого избежать.