Триггерные функции, типы триггеров, практические примеры
Триггеры — это функции, которые автоматически выполняются при наступлении определённых событий (INSERT, UPDATE, DELETE) на таблице. В этой теме вы изучите триггерные функции, типы триггеров и практические примеры использования.
Триггер состоит из двух частей:
TRIGGERСобытие (INSERT/UPDATE/DELETE)
↓
Триггер
↓
Триггерная функция
↓
Действие
CREATE OR REPLACE FUNCTION trigger_function_name()
RETURNS TRIGGER AS $$
BEGIN
-- логика триггера
RETURN NEW; -- или RETURN NULL, или RETURN OLD
END;
$$ LANGUAGE plpgsql;| Переменная | Описание |
|---|---|
NEW | Новая строка (для INSERT/UPDATE) |
OLD | Старая строка (для UPDATE/DELETE) |
TG_NAME | Имя триггера |
TG_WHEN | WHEN (BEFORE/AFTER) |
TG_LEVEL | ROW или STATEMENT |
TG_OP | Операция (INSERT/UPDATE/DELETE/TRUNCATE) |
TG_TABLE_NAME | Имя таблицы |
TG_TABLE_SCHEMA | Имя схемы |
| Тип | Описание |
|---|---|
BEFORE | Срабатывает до выполнения операции |
AFTER | Срабатывает после выполнения операции |
INSTEAD OF | Выполняется вместо операции (для представлений) |
| Тип | Описание |
|---|---|
FOR EACH ROW | Срабатывает для каждой изменённой строки |
FOR EACH STATEMENT | Срабатывает один раз на оператор (по умолчанию) |
-- Триггерная функция
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Создание триггера
CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
-- Использование
UPDATE users SET name = 'New Name' WHERE id = 1;
-- updated_at автоматически обновится-- Таблица для логов
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
table_name TEXT,
operation TEXT,
old_data JSONB,
new_data JSONB,
changed_by TEXT DEFAULT current_user,
changed_at TIMESTAMPTZ DEFAULT NOW()
);
-- Триггерная функция
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (table_name, operation, new_data)
VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW));
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, operation, old_data, new_data)
VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO audit_log (table_name, operation, old_data)
VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD));
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Создание триггеров для таблицы
CREATE TRIGGER trg_users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_trigger_function();-- Триггер для проверки возраста
CREATE OR REPLACE FUNCTION validate_user_age()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.age < 0 OR NEW.age > 150 THEN
RAISE EXCEPTION 'Возраст должен быть от 0 до 150 лет (получено: %)', NEW.age;
END IF;
IF NEW.email NOT LIKE '%@%.%' THEN
RAISE EXCEPTION 'Некорректный email: %', NEW.email;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_users_validate
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION validate_user_age();-- Таблица заказов
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
total NUMERIC(10, 2),
status TEXT
);
-- Добавим счётчики в таблицу users
ALTER TABLE users ADD COLUMN total_orders INTEGER DEFAULT 0;
ALTER TABLE users ADD COLUMN total_spent NUMERIC(10, 2) DEFAULT 0;
-- Триггерная функция
CREATE OR REPLACE FUNCTION update_user_stats()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE users
SET total_orders = total_orders + 1,
total_spent = total_spent + NEW.total
WHERE id = NEW.user_id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
UPDATE users
SET total_orders = total_orders - 1,
total_spent = total_spent - OLD.total
WHERE id = OLD.user_id;
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
-- Если изменился user_id
IF OLD.user_id IS DISTINCT FROM NEW.user_id THEN
UPDATE users
SET total_orders = total_orders - 1,
total_spent = total_spent - OLD.total
WHERE id = OLD.user_id;
UPDATE users
SET total_orders = total_orders + 1,
total_spent = total_spent + NEW.total
WHERE id = NEW.user_id;
ELSE
-- Если изменилась сумма
IF OLD.total IS DISTINCT FROM NEW.total THEN
UPDATE users
SET total_spent = total_spent - OLD.total + NEW.total
WHERE id = NEW.user_id;
END IF;
END IF;
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_orders_stats
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_user_stats();-- Добавим столбец deleted_at
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
-- Триггерная функция для перехвата DELETE
CREATE OR REPLACE FUNCTION soft_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
-- Вместо удаления устанавливаем deleted_at
NEW.deleted_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Триггер INSTEAD OF DELETE (требуется представление)
-- Или используем BEFORE DELETE
CREATE TRIGGER trg_soft_delete
BEFORE DELETE ON users
FOR EACH ROW
WHEN (OLD.deleted_at IS NULL) -- Только если ещё не удалён
EXECUTE FUNCTION soft_delete_trigger();
-- Альтернатива: функция мягкого удаления
CREATE OR REPLACE FUNCTION soft_delete_user(user_id INTEGER)
RETURNS BOOLEAN AS $$
BEGIN
UPDATE users
SET deleted_at = NOW()
WHERE id = user_id AND deleted_at IS NULL;
RETURN FOUND;
END;
$$ LANGUAGE plpgsql;-- Триггер для ограничения числа активных заказов у пользователя
CREATE OR REPLACE FUNCTION limit_active_orders()
RETURNS TRIGGER AS $$
DECLARE
active_count INTEGER;
BEGIN
SELECT COUNT(*) INTO active_count
FROM orders
WHERE user_id = NEW.user_id
AND status = 'active';
IF active_count >= 5 THEN
RAISE EXCEPTION 'У пользователя не может быть более 5 активных заказов';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_limit_orders
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION limit_active_orders();-- Триггер для синхронизации имени пользователя в заказах
CREATE OR REPLACE FUNCTION sync_user_name_in_orders()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'UPDATE' AND OLD.name IS DISTINCT FROM NEW.name THEN
UPDATE orders
SET user_name = NEW.name
WHERE user_id = NEW.id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_sync_user_name
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION sync_user_name_in_orders();-- Триггер срабатывает только при определённых условиях
CREATE TRIGGER trg_email_changed
AFTER UPDATE ON users
FOR EACH ROW
WHEN (OLD.email IS DISTINCT FROM NEW.email)
EXECUTE FUNCTION send_email_confirmation();
-- Триггер только для INSERT
CREATE TRIGGER trg_welcome_email
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION send_welcome_email();-- Представление
CREATE VIEW user_orders_view AS
SELECT
u.id as user_id,
u.name as user_name,
o.id as order_id,
o.total as order_total
FROM users u
JOIN orders o ON u.id = o.user_id;
-- Триггерная функция для вставки через представление
CREATE OR REPLACE FUNCTION user_orders_insert()
RETURNS TRIGGER AS $$
DECLARE
user_id INTEGER;
BEGIN
-- Находим или создаём пользователя
SELECT id INTO user_id FROM users WHERE name = NEW.user_name;
IF user_id IS NULL THEN
INSERT INTO users (name) VALUES (NEW.user_name)
RETURNING id INTO user_id;
END IF;
-- Создаём заказ
INSERT INTO orders (user_id, total)
VALUES (user_id, NEW.order_total);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Триггер INSTEAD OF
CREATE TRIGGER trg_user_orders_insert
INSTEAD OF INSERT ON user_orders_view
FOR EACH ROW
EXECUTE FUNCTION user_orders_insert();
-- Теперь можно вставлять через представление
INSERT INTO user_orders_view (user_name, order_total)
VALUES ('John', 100.50);-- Просмотр триггеров таблицы
SELECT
trigger_name,
event_manipulation,
event_object_table,
action_timing
FROM information_schema.triggers
WHERE event_object_table = 'users';
-- Отключение триггера
ALTER TABLE users DISABLE TRIGGER trg_users_audit;
-- Отключение всех триггеров
ALTER TABLE users DISABLE TRIGGER ALL;
-- Включение триггера
ALTER TABLE users ENABLE TRIGGER trg_users_audit;
-- Удаление триггера
DROP TRIGGER IF EXISTS trg_users_audit ON users;-- Плохо: сложная логика с множеством запросов
-- Хорошо: простая валидация или обновление timestamp-- Может вызвать бесконечный цикл
CREATE TRIGGER trg1 AFTER UPDATE ON table1 ...
CREATE TRIGGER trg2 AFTER UPDATE ON table2 ...
-- Решение: использовать рекурсивное ограничение
SET session_replication_role = 'replica'; -- отключает триггеры
-- операции
RESET session_replication_role;COMMENT ON TRIGGER trg_users_audit ON users IS
'Аудит всех изменений пользователей, пишет в audit_log';-- BEFORE-триггер может отклонить изменение
-- AFTER-триггер срабатывает после записи, отмена требует отката транзакцииТриггеры влияют на производительность:
-- Массовая вставка с триггером (медленно)
INSERT INTO users (name, email) SELECT name, email FROM temp_users;
-- Альтернатива: отключить триггер на время
ALTER TABLE users DISABLE TRIGGER trg_users_audit;
INSERT INTO users (name, email) SELECT name, email FROM temp_users;
ALTER TABLE users ENABLE TRIGGER trg_users_audit;Теперь вы умеете создавать триггеры для автоматизации реакций на изменения данных. В следующей теме вы изучите курсоры и динамический SQL — инструменты для построчной обработки результатов и выполнения гибких запросов.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.