PostgreSQL триггеры: как реагировать на изменение значения в определённой колонке

Частый сценарий — настроить уведомления или действия при изменении данных относительно исходного значения. Данные обновляются по крону из сторонних API через update или upsert. PostgreSQL предоставляет триггеры и функции для отслеживания изменений на уровне базы данных.

В качестве примера используется таблица leads с информацией о потенциальных клиентах. Задача — отслеживать изменения в колонке status, которая отражает, был ли лид автоматически назначен менеджеру. При обновлении статуса нужно создавать новую запись в отдельной таблице status_log с временной меткой и новым значением статуса.

Функция

CREATE OR REPLACE FUNCTION log_lead_status_changes()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status <> OLD.status THEN -- only log changes
    INSERT INTO status_log (lead_id, time, new_status)
      VALUES (NEW.id, now(), NEW.status);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Условие IF NEW.status <> OLD.status THEN определяет, изменилось ли значение колонки status.

Триггер

CREATE TRIGGER lead_status_change
AFTER UPDATE OF status ON leads
FOR EACH ROW
EXECUTE FUNCTION log_lead_status_changes();

Таблица для логов

CREATE TABLE status_log (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER NOT NULL REFERENCES leads(id),
  time TIMESTAMP NOT NULL DEFAULT now(),
  new_status VARCHAR(255) NOT NULL
);

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