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

Уяви собі ситуацію, коли ти — адміністратор великої бази даних. Раптом хтось видаляє важливу інформацію з таблиці, і всі кричать: "Хто це зробив?!". Щоб уникнути таких ситуацій, бази даних дають можливість записувати зміни і відстежувати, що відбувалося з даними. Все це робиться за допомогою логування і аудиту.

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

Тепер, коли ти розумієш "навіщо", давай навчимося робити це "як".

Створення таблиці для логів

Перш ніж ми почнемо налаштовувати тригери, нам потрібна таблиця для зберігання логів змін. Ось приклад:

-- Створюємо таблицю для логування змін
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. Забезпечення відновлення даних: якщо хтось випадково видалить дані, ти зможеш відновити їх з таблиці логів.

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

При реалізації логування за допомогою тригерів важливо враховувати продуктивність. Якщо тригер спрацьовує дуже часто, це може збільшити навантаження на базу даних. Тому:

• Використовуй логування лише на критично важливих таблицях.
• Якщо обсяг логів стає занадто великим, розробляй стратегії архівування.

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