Создание простого триггера для обновления данных: AFTER INSERT

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

Сценарий работы следующий:

1. Когда в таблицу добавляется новая запись, поле last_modified автоматически присваивает текущую дату и время.
2. Мы воспользуемся триггером AFTER INSERT, который сработает после успешной вставки данных.

Создание функции для триггера

Сначала необходимо создать функцию на языке PL/pgSQL. Эта функция будет обновлять поле last_modified в нашей таблице. Функция — это обязательный элемент для работы триггера, поскольку сам триггер только указывает на то, что нужно делать, а всю логику выполняет функция.

Итак, начнем с создания таблицы:

-- Создаем таблицу students
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL,
    last_modified TIMESTAMP
);

Теперь создадим функцию для обновления поля last_modified:

-- Функция для обновления last_modified
CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- Устанавливаем текущее время в поле last_modified
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Давайте разберёмся, что эта функция делает:

• CREATE OR REPLACE FUNCTION update_last_modified() — создаем функцию с именем update_last_modified. Если такая функция уже существует, она будет заменена.
• RETURNS TRIGGER — указываем, что функция предназначена для использования с триггером.
• NEW.last_modified := NOW(); — обновляем поле last_modified с помощью функции NOW(), которая возвращает текущее дату и время.
• RETURN NEW; — возвращаем обновленную запись. Это обязательный шаг для триггера AFTER.

Создание триггера

После создания функции мы можем создать сам триггер, привязав его к таблице students. Вот как это сделать:

-- Создаем триггер после вставки записи
CREATE TRIGGER set_last_modified
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Вот что тут делается:

• CREATE TRIGGER set_last_modified — создаем триггер с именем set_last_modified.
• AFTER INSERT — триггер будет срабатывать после успешной вставки строки в таблицу.
• ON students — триггер привязан к таблице students.
• FOR EACH ROW — триггер будет выполняться для каждой новой строки, добавляемой в таблицу.
• EXECUTE FUNCTION update_last_modified(); — вызов функции, которую мы создали ранее.

Примечание: название триггера (set_last_modified) и функции (update_last_modified) можно выбрать любое, однако важно следовать стандартам именования, чтобы код был понятен.

Тестирование триггера

Проверим, как работает наш триггер. Сначала добавим несколько записей в таблицу students:

-- Вставляем данные в таблицу
INSERT INTO students (name, age) VALUES ('Иван Иванов', 20);
INSERT INTO students (name, age) VALUES ('Анна Петрова', 22);

Теперь посмотрим, что получилось в таблице:

-- Просматриваем данные в таблице
SELECT * FROM students;

Ожидаемый результат может быть примерно таким:

Заметьте, что поле last_modified автоматически заполнилось текущей датой и временем для каждой записи.

Ошибки, которые могут возникнуть

1. Ошибка: "relation does not exist" при создании триггера. Эта ошибка возникает, если таблица students не создана. Убедитесь, что вы создали таблицу перед созданием триггера.
2. Ошибка доступа. Если пользователь базы данных не имеет прав на создание функций или триггеров, триггер не будет создан. Проверьте привилегии пользователя.
3. Отсутствие вызова функции в триггере. Если вы забудете указать EXECUTE FUNCTION update_last_modified(), триггер не сможет выполнить требуемые действия.

Улучшение триггера: добавление условий

В реальных задачах бывает полезно ограничить выполнение триггера определенными условиями. Например, если поле age меньше 18, мы не хотим обновлять last_modified. Это можно сделать с помощью условия WHEN:

-- Создаем триггер с условием
CREATE TRIGGER set_last_modified
AFTER INSERT ON students
FOR EACH ROW
WHEN (NEW.age >= 18)
EXECUTE FUNCTION update_last_modified();

Теперь поле last_modified будет обновляться только для студентов, возраст которых >= 18.

Практическое применение

Триггеры, подобные этому, часто используются в реальных проектах. Вот некоторые примеры:

• Автоматическое обновление времени последнего изменения записи (как мы сделали).
• Слежение за изменениями в базе и запись этих изменений в таблицу логов.
• Обеспечение целостности данных, например, проверка взаимосвязанных таблиц перед выполнением операции.
• Аудит данных для соблюдения правовых или корпоративных норм.

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

2

Задача

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

Недоступна

Создание функции и триггера для автоматического заполнения времени изменения

Создание функции и триггера для автоматического заполнения времени изменения

Открыть