Оптимизация SQL
Полезные скрипты
Использование PGAdmin
Сквозной пример в PostgreSQL
Видеомонтаж
Использование нейросетей
Интерактивные материалы
Офисные приложения

Создание триггеров в PostgreSQL: система аудита изменений

Информация о материале
Автор: Андрей К.
Категория: Scripts
Просмотров: 739

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

Подготовка: основная таблица employees, как пример

Предположим, у нас уже существует основная таблица сотрудников:

sql
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2),
    department_id INTEGER,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);
Шаг 1: Создание таблицы для истории изменений

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

sql
CREATE TABLE employees_audit (
    audit_id SERIAL PRIMARY KEY,
    operation_type VARCHAR(10) NOT NULL,      -- Тип операции: INSERT/UPDATE/DELETE
    operation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Идентификатор сотрудника
    employee_id INTEGER,
    
    -- Старые значения (до изменения)
    old_first_name VARCHAR(50),
    old_last_name VARCHAR(50),
    old_email VARCHAR(100),
    old_hire_date DATE,
    old_salary DECIMAL(10, 2),
    old_department_id INTEGER,
    old_is_active BOOLEAN,
    
    -- Новые значения (после изменения)
    new_first_name VARCHAR(50),
    new_last_name VARCHAR(50),
    new_email VARCHAR(100),
    new_hire_date DATE,
    new_salary DECIMAL(10, 2),
    new_department_id INTEGER,
    new_is_active BOOLEAN,
    
    -- Метаданные операции
    changed_by VARCHAR(100) DEFAULT CURRENT_USER,  -- Кто внес изменения
    application_name VARCHAR(100),                  -- Имя приложения
    client_address INET                             -- IP-адрес клиента
    
    -- Индекс для быстрого поиска по сотруднику
    CREATE INDEX idx_employees_audit_employee_id ON employees_audit(employee_id);
    
    -- Индекс для поиска по времени
    CREATE INDEX idx_employees_audit_time ON employees_audit(operation_time);
    
    -- Индекс для поиска по типу операции
    CREATE INDEX idx_employees_audit_operation ON employees_audit(operation_type);
);
Описание полей таблицы аудита:

  1. operation_type — тип выполненной операции:

    • INSERT — вставка новой записи

    • UPDATE — обновление существующей записи

    • DELETE — удаление записи

  2. Старые значения (old_*) — данные до изменения (заполняются для UPDATE и DELETE)

  3. Новые значения (new_*) — данные после изменения (заполняются для INSERT и UPDATE)

  4. Метаданные — информация о том, кто, когда и откуда внес изменения

Шаг 2: Создание триггерной функции

Триггерная функция — это функция, которая будет выполняться при срабатывании триггера:

sql
CREATE OR REPLACE FUNCTION process_employees_audit()
RETURNS TRIGGER 
LANGUAGE plpgsql
AS $$
BEGIN
    -- Определение типа операции
    IF (TG_OP = 'INSERT') THEN
        -- Для операции INSERT сохраняем только новые данные
        INSERT INTO employees_audit (
            operation_type,
            employee_id,
            
            -- Новые значения
            new_first_name,
            new_last_name,
            new_email,
            new_hire_date,
            new_salary,
            new_department_id,
            new_is_active,
            
            -- Метаданные
            application_name,
            client_address
        ) VALUES (
            'INSERT',
            NEW.employee_id,
            NEW.first_name,
            NEW.last_name,
            NEW.email,
            NEW.hire_date,
            NEW.salary,
            NEW.department_id,
            NEW.is_active,
            current_setting('application_name'),
            inet_client_addr()
        );
        RETURN NEW;
        
    ELSIF (TG_OP = 'UPDATE') THEN
        -- Для операции UPDATE сохраняем и старые, и новые данные
        -- Проверяем, действительно ли были изменения
        IF (
            OLD.first_name IS DISTINCT FROM NEW.first_name OR
            OLD.last_name IS DISTINCT FROM NEW.last_name OR
            OLD.email IS DISTINCT FROM NEW.email OR
            OLD.hire_date IS DISTINCT FROM NEW.hire_date OR
            OLD.salary IS DISTINCT FROM NEW.salary OR
            OLD.department_id IS DISTINCT FROM NEW.department_id OR
            OLD.is_active IS DISTINCT FROM NEW.is_active
        ) THEN
            INSERT INTO employees_audit (
                operation_type,
                employee_id,
                
                -- Старые значения
                old_first_name,
                old_last_name,
                old_email,
                old_hire_date,
                old_salary,
                old_department_id,
                old_is_active,
                
                -- Новые значения
                new_first_name,
                new_last_name,
                new_email,
                new_hire_date,
                new_salary,
                new_department_id,
                new_is_active,
                
                -- Метаданные
                application_name,
                client_address
            ) VALUES (
                'UPDATE',
                NEW.employee_id,
                OLD.first_name,
                OLD.last_name,
                OLD.email,
                OLD.hire_date,
                OLD.salary,
                OLD.department_id,
                OLD.is_active,
                NEW.first_name,
                NEW.last_name,
                NEW.email,
                NEW.hire_date,
                NEW.salary,
                NEW.department_id,
                NEW.is_active,
                current_setting('application_name'),
                inet_client_addr()
            );
        END IF;
        RETURN NEW;
        
    ELSIF (TG_OP = 'DELETE') THEN
        -- Для операции DELETE сохраняем только старые данные
        INSERT INTO employees_audit (
            operation_type,
            employee_id,
            
            -- Старые значения
            old_first_name,
            old_last_name,
            old_email,
            old_hire_date,
            old_salary,
            old_department_id,
            old_is_active,
            
            -- Метаданные
            application_name,
            client_address
        ) VALUES (
            'DELETE',
            OLD.employee_id,
            OLD.first_name,
            OLD.last_name,
            OLD.email,
            OLD.hire_date,
            OLD.salary,
            OLD.department_id,
            OLD.is_active,
            current_setting('application_name'),
            inet_client_addr()
        );
        RETURN OLD;
    END IF;
    
    RETURN NULL; -- Результат должен быть определен для всех веток
END;
$$;
Разбор ключевых элементов функции:

  1. TG_OP — специальная переменная, содержащая тип операции:

    • INSERT — при вставке новой записи

    • UPDATE — при обновлении записи

    • DELETE — при удалении записи

  2. NEW — специальная переменная, содержащая новые значения записи (доступна для INSERT и UPDATE)

  3. OLD — специальная переменная, содержащая старые значения записи (доступна для UPDATE и DELETE)

  4. IS DISTINCT FROM — оператор сравнения, который корректно обрабатывает NULL-значения

  5. current_setting('application_name') — получение имени приложения из параметров сессии

  6. inet_client_addr() — получение IP-адреса клиента

Шаг 3: Создание триггера

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

sql
CREATE TRIGGER trg_employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION process_employees_audit();
Параметры триггера:

  1. AFTER — триггер срабатывает ПОСЛЕ выполнения операции

    • Альтернатива: BEFORE — срабатывает ПЕРЕД выполнением операции

  2. INSERT OR UPDATE OR DELETE — триггер реагирует на все три типа операций

  3. FOR EACH ROW — триггер срабатывает для КАЖДОЙ измененной строки

    • Альтернатива: FOR EACH STATEMENT — срабатывает один раз для всего оператора

Проверка создания триггера

Убедимся, что триггер создан успешно:

sql
-- Просмотр всех триггеров для таблицы employees
SELECT 
    tgname AS trigger_name,
    tgtype::integer & 1 > 0 AS "before",
    tgtype::integer & 2 > 0 AS "insert",
    tgtype::integer & 4 > 0 AS "delete",
    tgtype::integer & 8 > 0 AS "update",
    tgenabled AS enabled
FROM pg_trigger
WHERE tgrelid = 'employees'::regclass;

-- Альтернативный способ через information_schema
SELECT 
    trigger_name,
    event_manipulation,
    action_timing,
    event_object_table
FROM information_schema.triggers
WHERE event_object_table = 'employees'
ORDER BY trigger_name;
Тестирование работы триггера
Тест 1: Вставка нового сотрудника (INSERT)
sql
-- Вставка новой записи
INSERT INTO employees (
    first_name,
    last_name,
    email,
    hire_date,
    salary,
    department_id,
    is_active
) VALUES (
    'Иван',
    'Петров',
    Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.',
    '2024-01-15',
    75000.00,
    3,
    TRUE
);

-- Проверка записи в аудите
SELECT 
    audit_id,
    operation_type,
    employee_id,
    new_first_name,
    new_last_name,
    new_email,
    new_salary,
    changed_by,
    operation_time
FROM employees_audit
WHERE operation_type = 'INSERT'
ORDER BY operation_time DESC
LIMIT 1;

Ожидаемый результат: Одна запись с operation_type = 'INSERT', заполнены только поля new_*.

Тест 2: Обновление зарплаты сотрудника (UPDATE)
sql
-- Обновление записи
UPDATE employees
SET salary = 80000.00,
    updated_at = CURRENT_TIMESTAMP
WHERE email = Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.';

-- Проверка записи в аудите
SELECT 
    audit_id,
    operation_type,
    employee_id,
    old_salary,
    new_salary,
    changed_by,
    operation_time
FROM employees_audit
WHERE operation_type = 'UPDATE'
ORDER BY operation_time DESC
LIMIT 1;

Ожидаемый результат: Одна запись с operation_type = 'UPDATE', заполнены и old_salary, и new_salary.

Тест 3: Удаление сотрудника (DELETE)
sql
-- Удаление записи
DELETE FROM employees
WHERE email = Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.';

-- Проверка записи в аудите
SELECT 
    audit_id,
    operation_type,
    employee_id,
    old_first_name,
    old_last_name,
    old_email,
    changed_by,
    operation_time
FROM employees_audit
WHERE operation_type = 'DELETE'
ORDER BY operation_time DESC
LIMIT 1;

Ожидаемый результат: Одна запись с operation_type = 'DELETE', заполнены только поля old_*.

Просмотр всех записей аудита
sql
-- Полный просмотр истории изменений
SELECT 
    audit_id,
    operation_type,
    employee_id,
    
    -- Старые значения (если есть)
    CASE 
        WHEN old_first_name IS NOT NULL OR old_last_name IS NOT NULL 
        THEN old_first_name || ' ' || old_last_name 
        ELSE NULL 
    END as old_full_name,
    old_salary,
    
    -- Новые значения (если есть)
    CASE 
        WHEN new_first_name IS NOT NULL OR new_last_name IS NOT NULL 
        THEN new_first_name || ' ' || new_last_name 
        ELSE NULL 
    END as new_full_name,
    new_salary,
    
    -- Метаданные
    changed_by,
    operation_time,
    application_name,
    client_address
    
FROM employees_audit
ORDER BY operation_time DESC;

-- Статистика по операциям
SELECT 
    operation_type,
    COUNT(*) as operation_count,
    MIN(operation_time) as first_operation,
    MAX(operation_time) as last_operation,
    COUNT(DISTINCT changed_by) as unique_users
FROM employees_audit
GROUP BY operation_type
ORDER BY operation_count DESC;
Расширенные возможности системы аудита
Вариант 1: Триггер только для критических полей
sql
CREATE OR REPLACE FUNCTION process_salary_changes_audit()
RETURNS TRIGGER 
LANGUAGE plpgsql
AS $$
BEGIN
    -- Логируем только изменения зарплаты
    IF (TG_OP = 'UPDATE' AND OLD.salary IS DISTINCT FROM NEW.salary) THEN
        INSERT INTO salary_audit (
            employee_id,
            old_salary,
            new_salary,
            change_percentage,
            changed_by,
            change_time
        ) VALUES (
            NEW.employee_id,
            OLD.salary,
            NEW.salary,
            ROUND(((NEW.salary - OLD.salary) / OLD.salary * 100)::numeric, 2),
            CURRENT_USER,
            CURRENT_TIMESTAMP
        );
    END IF;
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION process_salary_changes_audit();
Вариант 2: Триггер с проверкой бизнес-правил
sql
CREATE OR REPLACE FUNCTION validate_employee_changes()
RETURNS TRIGGER 
LANGUAGE plpgsql
AS $$
BEGIN
    -- Проверка для INSERT
    IF (TG_OP = 'INSERT') THEN
        -- Минимальная зарплата
        IF NEW.salary < 30000 THEN
            RAISE EXCEPTION 'Зарплата не может быть меньше 30000';
        END IF;
        
        -- Email должен содержать @
        IF POSITION('@' IN NEW.email) = 0 THEN
            RAISE EXCEPTION 'Некорректный email адрес';
        END IF;
    END IF;
    
    -- Проверка для UPDATE
    IF (TG_OP = 'UPDATE') THEN
        -- Нельзя снижать зарплату более чем на 20%
        IF NEW.salary < OLD.salary * 0.8 THEN
            RAISE EXCEPTION 'Нельзя снижать зарплату более чем на 20%%';
        END IF;
        
        -- Нельзя деактивировать без причины
        IF OLD.is_active = TRUE AND NEW.is_active = FALSE THEN
            RAISE EXCEPTION 'Деактивация требует указания причины';
        END IF;
    END IF;
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_employee
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION validate_employee_changes();
Вариант 3: Триггер для автоматического обновления updated_at
sql
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER 
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();
Управление триггерами
Включение и отключение триггеров
sql
-- Временное отключение триггера
ALTER TABLE employees DISABLE TRIGGER trg_employees_audit;

-- Массовые операции без аудита
UPDATE employees 
SET salary = salary * 1.05 
WHERE department_id = 3;

-- Включение триггера обратно
ALTER TABLE employees ENABLE TRIGGER trg_employees_audit;

-- Отключение всех триггеров на таблице
ALTER TABLE employees DISABLE TRIGGER ALL;

-- Включение всех триггеров
ALTER TABLE employees ENABLE TRIGGER ALL;
Просмотр и изменение триггеров
sql
-- Просмотр определения триггера
SELECT 
    tgname,
    pg_get_triggerdef(oid) as trigger_definition
FROM pg_trigger
WHERE tgrelid = 'employees'::regclass;

-- Удаление триггера
DROP TRIGGER IF EXISTS trg_employees_audit ON employees;

-- Изменение триггера (требуется удаление и создание заново)
DROP TRIGGER IF EXISTS trg_employees_audit ON employees;

CREATE TRIGGER trg_employees_audit_v2
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION process_employees_audit_v2();
Оптимизация производительности
Создание индексов для таблицы аудита
sql
-- Основные индексы для эффективных запросов
CREATE INDEX idx_audit_employee_operation ON employees_audit(employee_id, operation_type);
CREATE INDEX idx_audit_time_range ON employees_audit(operation_time DESC);
CREATE INDEX idx_audit_changed_by ON employees_audit(changed_by);

-- Частичный индекс для частых запросов
CREATE INDEX idx_audit_recent_updates ON employees_audit(operation_time)
WHERE operation_time > CURRENT_DATE - INTERVAL '30 days';

-- Индекс для поиска по email
CREATE INDEX idx_audit_email ON employees_audit(
    COALESCE(old_email, new_email)
);

Оптимизация функции триггера

sql
-- Улучшенная версия с минимальной логикой
CREATE OR REPLACE FUNCTION process_employees_audit_fast()
RETURNS TRIGGER 
LANGUAGE plpgsql
AS $$
BEGIN
    -- Быстрая вставка без лишних проверок
    INSERT INTO employees_audit (
        operation_type,
        employee_id,
        old_first_name, old_last_name, old_email, old_salary,
        new_first_name, new_last_name, new_email, new_salary,
        changed_by, application_name, client_address
    ) VALUES (
        TG_OP,
        COALESCE(NEW.employee_id, OLD.employee_id),
        OLD.first_name, OLD.last_name, OLD.email, OLD.salary,
        NEW.first_name, NEW.last_name, NEW.email, NEW.salary,
        CURRENT_USER,
        current_setting('application_name'),
        inet_client_addr()
    );
    
    RETURN COALESCE(NEW, OLD);
END;
$$;
Практические примеры использования
Пример 1: Восстановление данных из аудита
sql
-- Восстановление сотрудника после ошибочного удаления
INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    hire_date,
    salary,
    department_id,
    is_active,
    created_at
)
SELECT 
    employee_id,
    old_first_name,
    old_last_name,
    old_email,
    old_hire_date,
    old_salary,
    old_department_id,
    old_is_active,
    CURRENT_TIMESTAMP
FROM employees_audit
WHERE operation_type = 'DELETE'
  AND employee_id = 42
ORDER BY operation_time DESC
LIMIT 1;
Пример 2: Анализ истории изменений зарплаты
sql
-- История изменений зарплаты конкретного сотрудника
SELECT 
    operation_time,
    old_salary,
    new_salary,
    ROUND(((new_salary - old_salary) / old_salary * 100)::numeric, 2) as change_percent,
    changed_by
FROM employees_audit
WHERE employee_id = 42
  AND operation_type = 'UPDATE'
  AND old_salary IS NOT NULL
  AND new_salary IS NOT NULL
ORDER BY operation_time DESC;
Пример 3: Поиск подозрительных операций
sql
-- Поиск массовых изменений за короткий период
SELECT 
    changed_by,
    COUNT(*) as operations_count,
    MIN(operation_time) as first_operation,
    MAX(operation_time) as last_operation,
    MAX(operation_time) - MIN(operation_time) as duration
FROM employees_audit
WHERE operation_time > CURRENT_TIMESTAMP - INTERVAL '1 hour'
GROUP BY changed_by
HAVING COUNT(*) > 10
ORDER BY operations_count DESC;

-- Поиск изменений зарплаты более чем на 50%
SELECT 
    employee_id,
    old_salary,
    new_salary,
    ROUND(((new_salary - old_salary) / old_salary * 100)::numeric, 2) as increase_percent,
    changed_by,
    operation_time
FROM employees_audit
WHERE operation_type = 'UPDATE'
  AND ABS((new_salary - old_salary) / old_salary) > 0.5
ORDER BY increase_percent DESC;
Лучшие практики работы с триггерами
Практика 1: Минимизация логики в триггерах
sql
-- ПЛОХО: Слишком сложная логика в триггере
CREATE OR REPLACE FUNCTION complex_trigger_logic()
RETURNS TRIGGER AS $$
BEGIN
    -- Много проверок, вычислений, внешних вызовов...
    -- Это замедлит все операции с таблицей
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ХОРОШО: Минимальная логика, детали в отдельной функции
CREATE OR REPLACE FUNCTION simple_audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_table (...) VALUES (...);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Практика 2: Использование AFTER вместо BEFORE
sql
-- Используйте AFTER для аудита (данные уже сохранены)
CREATE TRIGGER trg_audit AFTER INSERT ...

-- Используйте BEFORE для валидации (можно предотвратить операцию)
CREATE TRIGGER trg_validate BEFORE INSERT ...
Практика 3: Документирование триггеров
sql
-- Добавление комментариев к триггерам
COMMENT ON TRIGGER trg_employees_audit ON employees IS 
'Триггер для аудита всех изменений в таблице employees. 
Создан 2024-01-15. Автор: Отдел разработки.';

-- Документация функции
COMMENT ON FUNCTION process_employees_audit() IS 
'Функция для записи истории изменений сотрудников. 
Записывает INSERT, UPDATE, DELETE операции с метаданными.';
Заключение

Триггеры в PostgreSQL предоставляют эффективный механизм для автоматизации бизнес-логики и отслеживания изменений данных. Созданная система аудита позволяет:

  1. Автоматически отслеживать все изменения в таблице employees

  2. Сохранять историю вставок, обновлений и удалений

  3. Регистрировать метаданные — кто, когда и откуда внес изменения

  4. Обеспечивать прозрачность и подотчетность операций

  5. Предоставлять возможность восстановления данных при необходимости

Ключевые принципы успешного использования триггеров:

  • Начинайте с простых триггеров для аудита

  • Тестируйте триггеры на тестовых данных

  • Мониторьте производительность при активном использовании

  • Документируйте созданные триггеры

  • Используйте транзакции для групп операций с триггерами

Помните: хорошо спроектированные триггеры становятся надежным фундаментом для защиты целостности данных и автоматизации бизнес-процессов.

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

Picture background

Статья подготовлена по материалам обучающего видео для вашего удобства.

Поиск по материалам сайта
Создание триггеров (TRIGGER) в видео

Создание триггеров (TRIGGER) в PostgreSQL

БОЛЬШЕ СМОТРЕТЬ ТУТ

 Picture background