PostgreSQL 触发器（Trigger）详解 —— 从基础到企业级实战

✅ PostgreSQL 触发器（Trigger）详解 —— 从基础到企业级实战

触发器是数据库的“自动化引擎”，能在数据变更时自动执行业务逻辑。本篇将带你深入掌握 PostgreSQL 触发器的原理、类型、实战应用及性能优化，助你构建数据一致性、审计、自动化处理的强大系统。

---

🧭 一、触发器核心概念

✅ 什么是触发器？

触发器（Trigger） 是与表关联的特殊函数，当指定事件（INSERT/UPDATE/DELETE）发生时自动执行。

✅ 触发器执行时机

时机	说明
BEFORE	在事件执行前触发（可修改数据）
AFTER	在事件执行后触发（常用于日志）
INSTEAD OF	替代原操作（用于视图）

✅ 触发事件

• INSERT
• UPDATE
• DELETE
• TRUNCATE（仅 AFTER）

---

一、触发器基础语法

---

✅ 1. 创建触发器函数

触发器函数必须返回 TRIGGER 类型：

CREATE OR REPLACE FUNCTION trigger_function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- 触发器逻辑
    RETURN NEW; -- INSERT/UPDATE 返回 NEW，DELETE 返回 OLD
END;
$$ LANGUAGE plpgsql;

💡 特殊变量：

• NEW：新行数据（INSERT/UPDATE）
• OLD：旧行数据（UPDATE/DELETE）
• TG_OP：操作类型（‘INSERT’, ‘UPDATE’, ‘DELETE’）
• TG_TABLE_NAME：表名
• TG_WHEN：触发时机

---

✅ 2. 创建触发器

CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE | AFTER | INSTEAD OF} {event [OR ...]}
    ON table_name
    [FOR [EACH] {ROW | STATEMENT}]
    EXECUTE FUNCTION function_name();

▶ 行级触发器 vs 语句级触发器

类型	触发频率	适用场景
FOR EACH ROW	每行触发一次	数据验证、审计、计算列
FOR EACH STATEMENT	每语句触发一次	聚合统计、批量操作日志

---

二、触发器实战案例

---

✅ 1. 数据验证（BEFORE INSERT/UPDATE）

示例：确保邮箱格式正确

CREATE OR REPLACE FUNCTION validate_email()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.email !~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        RAISE EXCEPTION '无效的邮箱格式: %', NEW.email;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

---

✅ 2. 自动更新时间戳（BEFORE UPDATE）

示例：自动设置 updated_at

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

CREATE TRIGGER tr_set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

---

✅ 3. 审计日志（AFTER INSERT/UPDATE/DELETE）

示例：记录用户表变更

-- 创建审计表
CREATE TABLE users_audit (
    id SERIAL PRIMARY KEY,
    user_id INT,
    operation TEXT,
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT,
    changed_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO users_audit (user_id, operation, new_data, changed_by)
        VALUES (NEW.id, 'INSERT', row_to_json(NEW)::JSONB, current_user);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO users_audit (user_id, operation, old_data, new_data, changed_by)
        VALUES (NEW.id, 'UPDATE', row_to_json(OLD)::JSONB, row_to_json(NEW)::JSONB, current_user);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO users_audit (user_id, operation, old_data, changed_by)
        VALUES (OLD.id, 'DELETE', row_to_json(OLD)::JSONB, current_user);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_audit_users
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION log_user_changes();

---

✅ 4. 计算列维护（BEFORE INSERT/UPDATE）

示例：自动计算订单总额

CREATE OR REPLACE FUNCTION calculate_order_total()
RETURNS TRIGGER AS $$
BEGIN
    NEW.total_amount = NEW.quantity * NEW.unit_price;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_calculate_total
    BEFORE INSERT OR UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION calculate_order_total();

---

✅ 5. 视图更新（INSTEAD OF）

示例：可更新视图

-- 创建视图
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE is_active = true;

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_active_users()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO users (name, email, is_active) 
        VALUES (NEW.name, NEW.email, true);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE users 
        SET name = NEW.name, email = NEW.email 
        WHERE id = NEW.id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM users WHERE id = OLD.id;
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER tr_active_users
    INSTEAD OF INSERT OR UPDATE OR DELETE ON active_users
    FOR EACH ROW
    EXECUTE FUNCTION update_active_users();

---

✅ 6. 条件触发器（WHEN 子句）

示例：只在价格变化时记录

CREATE TRIGGER tr_price_change_audit
    AFTER UPDATE ON products
    FOR EACH ROW
    WHEN (OLD.price IS DISTINCT FROM NEW.price)
    EXECUTE FUNCTION log_price_changes();

---

三、高级触发器技巧

---

✅ 1. 禁用/启用触发器

-- 禁用单个触发器
ALTER TABLE users DISABLE TRIGGER tr_audit_users;

-- 启用单个触发器
ALTER TABLE users ENABLE TRIGGER tr_audit_users;

-- 禁用所有触发器
ALTER TABLE users DISABLE TRIGGER ALL;

-- 启用所有触发器
ALTER TABLE users ENABLE TRIGGER ALL;

-- 会话级禁用（不影响其他会话）
SET session_replication_role = replica; -- 禁用所有触发器
SET session_replication_role = origin;  -- 恢复

💡 session_replication_role = replica 常用于数据迁移，避免触发器执行。

---

✅ 2. 递归触发器控制

-- 避免触发器递归调用
CREATE OR REPLACE FUNCTION prevent_recursion()
RETURNS TRIGGER AS $$
BEGIN
    IF pg_trigger_depth() > 1 THEN
        RETURN NEW;
    END IF;
    -- 正常逻辑
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

---

✅ 3. 性能优化技巧

▶ 3.1 避免在触发器中执行复杂查询

-- ❌ 避免：在触发器中 JOIN 大表
CREATE OR REPLACE FUNCTION bad_trigger()
RETURNS TRIGGER AS $$
DECLARE
    dept_name TEXT;
BEGIN
    SELECT name INTO dept_name FROM departments WHERE id = NEW.dept_id; -- 慢！
    -- ...
END;
$$ LANGUAGE plpgsql;

-- ✅ 优化：预先加载或使用缓存

▶ 3.2 使用语句级触发器处理批量操作

-- 批量更新后统计
CREATE OR REPLACE FUNCTION update_stats()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW user_stats;
    RETURN NULL; -- 语句级触发器返回 NULL
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_update_stats
    AFTER UPDATE ON users
    FOR EACH STATEMENT
    EXECUTE FUNCTION update_stats();

---

✅ 4. 调试触发器

-- 使用 RAISE 记录调试信息
CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE '操作: %, 表: %, 行ID: %', TG_OP, TG_TABLE_NAME, NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 查看触发器执行
SET client_min_messages = NOTICE;

---

四、触发器管理与监控

---

✅ 1. 查看触发器

-- 查看表的所有触发器
\d table_name

-- SQL 查询
SELECT 
    tgname as trigger_name,
    tgtype::int as type_code,
    tgenabled as enabled,
    tgconstraint as constraint_trigger
FROM pg_trigger
WHERE tgrelid = 'users'::regclass;

-- 查看触发器函数
SELECT proname, prosrc 
FROM pg_proc 
WHERE oid IN (SELECT tgfoid FROM pg_trigger WHERE tgrelid = 'users'::regclass);

---

✅ 2. 删除触发器

DROP TRIGGER IF EXISTS trigger_name ON table_name;
-- 示例：
DROP TRIGGER IF EXISTS tr_audit_users ON users;

---

✅ 3. 触发器执行顺序

同一事件的多个触发器按名称字母顺序执行：

-- 按名称控制顺序
CREATE TRIGGER a_tr_first ... -- 先执行
CREATE TRIGGER b_tr_second ... -- 后执行

---

五、最佳实践与注意事项

---

✅ 1. 使用场景推荐

场景	推荐触发器类型
数据验证	BEFORE ROW
时间戳自动更新	BEFORE ROW
审计日志	AFTER ROW
计算列维护	BEFORE ROW
批量统计更新	AFTER STATEMENT
视图更新	INSTEAD OF ROW

---

✅ 2. 性能注意事项

1. 避免复杂逻辑：触发器应尽量简单快速
2. 慎用行级触发器：大批量操作时性能影响大
3. 监控执行时间：使用 pg_stat_user_functions 监控
4. 避免递归调用：使用 pg_trigger_depth() 检查
5. 测试充分：触发器错误可能导致数据不一致

---

✅ 3. 替代方案考虑

需求	触发器方案	替代方案
数据验证	BEFORE 触发器	应用层验证 + CHECK 约束
审计日志	AFTER 触发器	逻辑复制 + 外部日志系统
计算列	BEFORE 触发器	GENERATED ALWAYS AS
批量统计	AFTER STATEMENT	物化视图 + 定时刷新

💡 PostgreSQL 12+ 支持生成列：

ALTER TABLE orders ADD COLUMN total_amount 
    GENERATED ALWAYS AS (quantity * unit_price) STORED;

---

🎯 六、实践任务

请完成以下操作：

1. 创建 products(id, name, price, created_at, updated_at) 表
2. 创建触发器自动设置 created_at 和 updated_at
3. 创建审计表 products_audit，记录所有变更
4. 创建触发器在价格变化时发送通知（使用 RAISE NOTICE）
5. 创建视图 expensive_products（价格>1000），并实现可更新
6. 测试触发器性能，对比有无触发器的批量插入速度

---

📊 触发器性能影响测试

-- 无触发器
INSERT INTO products (name, price) 
SELECT 'Product ' || i, i * 10 
FROM generate_series(1, 10000) as i;

-- 有触发器（自动设置时间戳）
-- 对比执行时间