Перейти к основному содержимому

Практика: Триггеры и правила


Онлайн редактор кода для PostgreSQL

Здесь вы можете попробовать свои силы в программировании на языке SQL для работы с PostgreSQL. Для этого мы предоставляем вам онлайн редактор кода, в котором вы можете написать свой код и запустить его.


❗️ При обновлении страницы код пропадёт, по этому, сохраните свой код куда-нибудь, если он важный.

Задание 1 — BEFORE триггеры: валидация и нормализация​

Задание 1: BEFORE-триггеры для защиты и нормализации данных

⏱️ Примерное время: 15-30 минут

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


Требования:

  • создай триггерную функцию + триггер BEFORE INSERT OR UPDATE
  • используй NEW и OLD
  • верни NEW (или NULL для отмены операции)
  • протестируй INSERT и UPDATE, покажи, что триггер сработал

Пример:

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at();

Задание 2 — AFTER триггеры: аудит, пересчёт, уведомления​

Задание 2: AFTER-триггеры для реакции на изменения

⏱️ Примерное время: 25-45 минут

Создай триггеры, которые реагируют после изменения данных.


Требования:

  • триггер AFTER INSERT OR UPDATE OR DELETE (или нужные события)
  • используй TG_OP, TG_LEVEL, NEW/OLD
  • протестируй массовую операцию (INSERT 10 строк, UPDATE 50 строк)
  • покажи, как триггер влияет на производительность (EXPLAIN ANALYZE)

Пример:

CREATE OR REPLACE FUNCTION recalc_order_total()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    v_order_id INT := COALESCE(NEW.order_id, OLD.order_id);
BEGIN
    UPDATE orders
    SET total = (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = v_order_id)
    WHERE order_id = v_order_id;
    RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER trg_order_items_recalc
    AFTER INSERT OR UPDATE OR DELETE ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION recalc_order_total();

Задание 3 — INSTEAD OF + Rules​

Задание 3: INSTEAD OF триггеры и Rules

⏱️ Примерное время: 30-50 минут

Сделай обновляемые представления и правила.


Требования:

  • создай view + INSTEAD OF триггер (или Rule)
  • реализуй INSERT/UPDATE/DELETE через представление
  • протестируй, что обычный UPDATE/DELETE на view не работает без триггера
  • сравни поведение Rules и триггеров (если возможно)

Пример:

CREATE VIEW products_live AS SELECT * FROM products WHERE is_active;

CREATE OR REPLACE FUNCTION soft_delete_products()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    UPDATE products SET is_active = false, deleted_at = NOW()
    WHERE product_id = OLD.product_id;
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_products_live_delete
    INSTEAD OF DELETE ON products_live
    FOR EACH ROW EXECUTE FUNCTION soft_delete_products();

Задание 4 — Универсальный аудит-триггер + WHEN​

Задание 4: Универсальный аудит + условные триггеры

⏱️ Примерное время: 45-75 минут

Создай один триггер для всей схемы + условные срабатывания.


Требования:

  • одна универсальная функция (с TG_ARGV для PK-колонки)
  • триггер на несколько таблиц с разными PK
  • используй WHEN для фильтрации (например, WHEN NEW.price <> OLD.price)
  • добавь поле changed_fields (массив изменённых колонок)
  • протестируй на INSERT/UPDATE/DELETE + массовые операции

Пример:

CREATE OR REPLACE FUNCTION generic_audit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    v_pk TEXT := TG_ARGV[0];
    v_id TEXT;
    v_changed TEXT[] := '{}';
BEGIN
    -- Определяем ID
    EXECUTE format('SELECT ($1).%I::TEXT', v_pk) INTO v_id USING NEW;
    -- Логика аудита...
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_audit
    AFTER INSERT OR UPDATE OR DELETE ON products
    FOR EACH ROW EXECUTE FUNCTION generic_audit('product_id');

Задание 5 — Итоговый проект: полная система аудита + soft-delete + пересчёты​

Задание 5: Полноценная система триггеров (итоговое)

⏱️ Примерное время: 90-180 минут

Создай самодостаточную систему с триггерами.


Требования:

  • минимум 5–7 триггеров (BEFORE + AFTER + INSTEAD OF)
  • универсальный аудит-триггер + минимум 2 специализированных
  • soft-delete хотя бы на одной таблице
  • пересчёт итогов (total, rating, balance и т.д.)
  • контроль целостности (остатки, баланс, лимиты)
  • условные триггеры (WHEN) + логирование
  • тестовые сценарии: массовые INSERT/UPDATE/DELETE + проверка аудита
  • документация: COMMENT ON TRIGGER + описание логики

Это задание — полноценный кейс для портфолио или собеседования на позицию DBA / Senior Backend.

подсказка

Триггеры — это контракт БД с самой собой.
Главные правила успеха:

  1. BEFORE → валидация и нормализация
  2. AFTER → аудит, пересчёт, уведомления
  3. INSTEAD OF → обновляемые представления
  4. WHEN → экономия производительности
  5. Универсальные функции + TG_ARGV → меньше кода
  6. Мониторинг: pg_stat_user_functions + EXPLAIN ANALYZE