middle

Что такое триггеры в PostgreSQL?

Триггер (trigger) — именованный объект базы данных, который автоматически выполняет функцию при наступлении определённого события (INSERT, UPDATE, DELETE, TRUNCATE) на таблице. Триггеры позволяют реализовать бизнес-логику на уровне БД.

Виды триггеров

BEFORE — выполняется до операции (может изменить данные или отменить операцию)
AFTER — выполняется после операции (данные уже изменены)
INSTEAD OF — заменяет операцию (только для представлений)
FOR EACH ROW — для каждой строки
FOR EACH STATEMENT — один раз на весь оператор

Пример: автообновление timestamp

Пример

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

CREATE TRIGGER trg_accounts_updated
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_timestamp();

Пример: аудит изменений

CREATE TABLE audit_log (
    id bigserial PRIMARY KEY,
    table_name text NOT NULL,
    operation text NOT NULL,
    old_data jsonb,
    new_data jsonb,
    changed_by text DEFAULT current_user,
    changed_at timestamptz DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, old_data)
        VALUES (TG_TABLE_NAME, 'DELETE', to_jsonb(OLD));
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data)
        VALUES (TG_TABLE_NAME, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, new_data)
        VALUES (TG_TABLE_NAME, 'INSERT', to_jsonb(NEW));
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_accounts_audit
    AFTER INSERT OR UPDATE OR DELETE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger_func();

Специальные переменные в триггерных функциях

NEW — новая строка (INSERT, UPDATE)
OLD — старая строка (UPDATE, DELETE)
TG_OP — тип операции (‘INSERT’, ‘UPDATE’, ‘DELETE’, ‘TRUNCATE’)
TG_TABLE_NAME — имя таблицы
TG_WHEN — ‘BEFORE’ или ‘AFTER’

Условный триггер (PostgreSQL 9.0+)

Пример

CREATE TRIGGER trg_large_transactions
    AFTER INSERT ON transactions
    FOR EACH ROW
    WHEN (NEW.amount > 1000000)
    EXECUTE FUNCTION notify_compliance_department();

На собеседовании: важно разделить BEFORE и AFTER: BEFORE может модифицировать NEW и даже отменить операцию (RETURN NULL), AFTER — нет. Условный триггер (WHEN) — продвинутый ответ, показывающий, что вы не используете IF внутри триггерной функции для простых условий.

#postgresql