Триггеры в PostgreSQL — CREATE TRIGGER, PL/pgSQL, NEW и OLD

Триггер — функция, автоматически вызываемая при INSERT, UPDATE или DELETE. Используется для аудита изменений, автоматического заполнения полей и валидации бизнес-правил. Разбираем синтаксис, переменные NEW/OLD и типовые паттерны.

Как работают триггеры

-- Триггер состоит из двух частей:
-- 1. Функция-обработчик (RETURNS trigger)
-- 2. Привязка функции к таблице и событию

-- Пример: автоматически заполнять updated_at при UPDATE:

-- Шаг 1: создать функцию:
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;   -- RETURN NEW для BEFORE триггеров
END;
$$ LANGUAGE plpgsql;

-- Шаг 2: привязать к таблице:
CREATE TRIGGER users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- Теперь при любом UPDATE поле updated_at обновляется автоматически:
UPDATE users SET email = 'new@example.com' WHERE id = 1;
-- updated_at обновится без явного указания

Переменные NEW и OLD

-- NEW — новые значения строки (доступно в INSERT и UPDATE)
-- OLD — старые значения строки (доступно в UPDATE и DELETE)

-- Таблица доступности:
-- Событие   | NEW  | OLD
-- INSERT    | да   | нет
-- UPDATE    | да   | да
-- DELETE    | нет  | да

CREATE OR REPLACE FUNCTION audit_users()
RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, operation, new_data, changed_at)
    VALUES ('users', 'INSERT', to_jsonb(NEW), NOW());
    RETURN NEW;

  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_at)
    VALUES ('users', 'UPDATE', to_jsonb(OLD), to_jsonb(NEW), NOW());
    RETURN NEW;

  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (table_name, operation, old_data, changed_at)
    VALUES ('users', 'DELETE', to_jsonb(OLD), NOW());
    RETURN OLD;  -- для DELETE RETURN OLD

  END IF;
END;
$$ LANGUAGE plpgsql;

BEFORE vs AFTER триггеры

-- BEFORE INSERT — валидация и нормализация:
CREATE OR REPLACE FUNCTION normalize_email()
RETURNS trigger AS $$
BEGIN
  -- Привести email к нижнему регистру:
  NEW.email = lower(NEW.email);

  -- Проверить формат (простая проверка):
  IF NEW.email NOT LIKE '%@%.%' THEN
    RAISE EXCEPTION 'Неверный формат email: %', NEW.email;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_normalize_email
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION normalize_email();

-- AFTER INSERT — уведомление через NOTIFY:
CREATE OR REPLACE FUNCTION notify_new_order()
RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('new_order', row_to_json(NEW)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_notify
  AFTER INSERT ON orders
  FOR EACH ROW EXECUTE FUNCTION notify_new_order();

Управление триггерами

-- Посмотреть все триггеры таблицы:
SELECT trigger_name, event_manipulation, action_timing, action_statement
FROM information_schema.triggers
WHERE event_object_table = 'users';

-- В psql:
\d users   -- в разделе "Triggers" видны все триггеры таблицы

-- Отключить триггер (например для bulk-загрузки):
ALTER TABLE users DISABLE TRIGGER users_updated_at;

-- Включить обратно:
ALTER TABLE users ENABLE TRIGGER users_updated_at;

-- Отключить все триггеры таблицы:
ALTER TABLE users DISABLE TRIGGER ALL;

-- Удалить триггер:
DROP TRIGGER users_updated_at ON users;
DROP TRIGGER IF EXISTS users_updated_at ON users;

-- Удалить функцию-обработчик (только если нет триггеров использующих её):
DROP FUNCTION set_updated_at();
DROP FUNCTION set_updated_at() CASCADE;  -- удалит и зависимые триггеры

Триггер для таблицы аудита

-- Создать таблицу аудита:
CREATE TABLE audit_log (
  id         BIGSERIAL PRIMARY KEY,
  table_name TEXT NOT NULL,
  operation  TEXT NOT NULL,  -- INSERT, UPDATE, DELETE
  old_data   JSONB,
  new_data   JSONB,
  changed_by TEXT DEFAULT current_user,
  changed_at TIMESTAMPTZ DEFAULT NOW()
);

-- Универсальная функция аудита:
CREATE OR REPLACE FUNCTION audit_table_changes()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,   -- имя таблицы (автоматически из триггера)
    TG_OP,           -- INSERT, UPDATE или DELETE
    CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END
  );

  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  ELSE
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Подключить к таблице:
CREATE TRIGGER audit_users
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_table_changes();

CREATE TRIGGER audit_orders
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION audit_table_changes();

Часто задаваемые вопросы

Когда использовать триггеры, а когда логику в приложении?

Триггеры хороши для: аудита изменений (гарантируют запись даже при прямом SQL), автозаполнения системных полей (updated_at, version), поддержания денормализованных агрегатов, валидации сложных бизнес-правил на уровне БД. Логика в приложении предпочтительнее для: бизнес-логики которую нужно тестировать, операций требующих внешних вызовов (HTTP, email), сложных условий зависящих от состояния приложения. Минус триггеров: неявное поведение затрудняет отладку, могут замедлить bulk-операции.

Почему триггер не срабатывает при TRUNCATE?

TRUNCATE не является row-level операцией — он удаляет все данные без обработки каждой строки. Триггеры FOR EACH ROW не срабатывают при TRUNCATE. Можно создать statement-level триггер: FOR EACH STATEMENT — тогда триггер сработает один раз для всей операции, но без доступа к OLD строкам. Если нужно перехватить TRUNCATE — используйте CREATE TRIGGER ... AFTER TRUNCATE ON table FOR EACH STATEMENT. Если нужен аудит удаления данных — используйте DELETE вместо TRUNCATE.

Как отладить триггер в PostgreSQL?

Используйте RAISE NOTICE для вывода отладочной информации: RAISE NOTICE 'NEW.email = %', NEW.email; — выведет в консоль psql. Для более сложной отладки: RAISE EXCEPTION откатит транзакцию и покажет сообщение. Проверьте что триггер вообще вызывается: временно добавьте INSERT INTO debug_log VALUES (TG_OP, NOW());. Посмотреть все триггеры: SELECT * FROM information_schema.triggers WHERE event_object_table = 'ваша_таблица';. Проверить отключённые триггеры: SELECT tgname, tgenabled FROM pg_trigger WHERE tgrelid = 'users'::regclass;.