Автоматизация задач с помощью триггеров

Представьте себе ситуацию, когда вы — администратор большой базы данных. Неожиданно кто-то удаляет важную информацию из таблицы, и все кричат: "Кто это сделал?!". Чтобы избежать таких ситуаций, базы данных предоставляют возможность записывать изменения и отслеживать, что происходило с данными. Всё это делается с помощью логирования и аудита.

• Логирование изменений позволяет сохранить историю того, что произошло: какая запись изменилась, каким образом и когда.
• Аудит данных используется для более глубокой проверки, включая запись не только изменений, но и информации о пользователе, который их инициировал.

Теперь, когда вы понимаете "зачем", давайте научимся делать это "как".

Создание таблицы для логов

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

-- Создаем таблицу для логирования изменений
CREATE TABLE change_logs (
    log_id SERIAL PRIMARY KEY,       -- Уникальный идентификатор записи
    table_name TEXT NOT NULL,        -- Имя таблицы, где произошли изменения
    operation TEXT NOT NULL,         -- Тип операции: INSERT, UPDATE, DELETE
    change_time TIMESTAMP DEFAULT NOW(), -- Время изменения
    old_data JSONB,                  -- Данные до изменения (для UPDATE/DELETE)
    new_data JSONB                   -- Данные после изменения (для INSERT/UPDATE)
);

Что здесь происходит?

1. log_id — уникальный идентификатор для каждой записи лога.
2. table_name — мы будем записывать, какая таблица была изменена.
3. operation — тип операции: INSERT, UPDATE или DELETE.
4. change_time — фиксирует точное время изменения.
5. old_data и new_data — данные до и после изменения в формате JSON.

Логирование изменений с помощью триггера

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

1. Напишем функцию на PL/pgSQL, которая будет добавлять записи в таблицу логов.
2. Создадим триггер на таблице students.

Функция будет получать информацию об операции (INSERT, UPDATE, DELETE), а также данные, которые изменились (OLD и NEW).

-- Функция для записи изменений в таблицу логов
CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Логирование операции INSERT
    IF TG_OP = 'INSERT' THEN
        INSERT INTO change_logs (table_name, operation, new_data)
        VALUES ('students', 'INSERT', row_to_json(NEW));

    -- Логирование операции DELETE
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO change_logs (table_name, operation, old_data)
        VALUES ('students', 'DELETE', row_to_json(OLD));

    -- Логирование операции UPDATE
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO change_logs (table_name, operation, old_data, new_data)
        VALUES ('students', 'UPDATE', row_to_json(OLD), row_to_json(NEW));
    END IF;

    RETURN NULL; -- Возвращаем NULL, так как это AFTER-триггер
END;
$$ LANGUAGE plpgsql;

Здесь:

• TG_OP — специальная переменная, которая содержит текущую операцию: INSERT, UPDATE, DELETE.
• row_to_json(OLD) и row_to_json(NEW) — преобразуют данные строки в формат JSON для удобного хранения.
• RETURN NULL — поскольку это AFTER-триггер, он не должен возвращать измененные данные.

Теперь свяжем нашу функцию с таблицей students.

-- Создаем триггер для логирования изменений
CREATE TRIGGER students_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_changes();

Что здесь происходит?

• AFTER INSERT OR UPDATE OR DELETE — триггер срабатывает после выполнения указанных операций над таблицей students.
• FOR EACH ROW — триггер выполняется для каждой измененной строки.
• EXECUTE FUNCTION log_student_changes() — вызывает нашу функцию для логирования.

Тестируем триггер

Настало время проверить работу нашего триггера.

1. Вставка новой записи

INSERT INTO students (name, age, grade)
VALUES ('Отто Лин', 20, 'A');

Посмотрим, что записалось в таблицу логов:

SELECT * FROM change_logs;

Пример результата:

2. Обновление записи

UPDATE students
SET grade = 'B'
WHERE name = 'Отто Лин';

Снова проверим таблицу логов:

SELECT * FROM change_logs ORDER BY change_time DESC;

Результат:

3. Удаление записи

DELETE FROM students
WHERE name = 'Отто Лин';

И снова, проверим лог:

Примеры реального использования

1. Логирование операций на критически важных таблицах: например, таблица с банковскими счетами требует записи всех изменений для предотвращения мошенничества.
2. Аудит системы: вы можете хранить записи для нормативного соответствия или анализа пользовательской активности.
3. Обеспечение восстановления данных: если кто-то случайно удалит данные, вы сможете восстановить их из таблицы логов.

Особенности и подводные камни

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

• Используйте логирование только на критически важных таблицах.
• Если объем логов становится слишком большим, разрабатывайте стратегии архивирования.

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

2

Задача

SQL SELF, 58 уровень, 0 лекция

Недоступна

Реализация триггера с логированием

Реализация триггера с логированием

Открыть