SQL - Trigger 让人爱恨交加的钩子

Trigger（触发器），数据库里的”钩子”。某张表发生 INSERT、UPDATE、DELETE 时，自动执行一段预定义的逻辑——记录审计日志、同步缓存、检查业务规则……让数据库在数据变化时自主响应。

但 Trigger 是把双刃剑：用得好,减少重复代码、保证数据一致性；用不好，让调试变成噩梦、性能直线下降。

Trigger 的工作原理

Trigger 绑定在某张表上，监听特定操作（INSERT / UPDATE / DELETE）。当这些操作发生时，Trigger 自动执行：

graph LR
    A[应用层执行 SQL] --> B{Trigger 是否存在?}
    B -->|是| C[BEFORE Trigger 执行]
    C --> D[实际 SQL 操作]
    D --> E[AFTER Trigger 执行]
    E --> F[返回结果]
    B -->|否| D

关键概念：

BEFORE Trigger：在真正修改数据之前执行（可以拦截、修改即将写入的值）
AFTER Trigger：在数据修改完成后执行（可以记录历史、触发下游操作）
NEW / OLD：特殊变量，代表整行数据（所有字段都能访问）
- INSERT：只有 NEW
- DELETE：只有 OLD
- UPDATE：既有 NEW（修改后）也有 OLD（修改前）
返回值规则：
- RETURN NEW：继续操作，使用修改后的数据
- RETURN OLD：继续操作，使用原始数据
- RETURN NULL：取消本次操作（仅 BEFORE trigger）

语法细节

`$$` 字符串分隔符

PostgreSQL 定义函数时，函数体需要用字符串包裹。$$ 是dollar-quoted string，比单引号方便（不需要转义内部引号）：

-- 用单引号：内部引号要转义（麻烦）
CREATE FUNCTION my_func() RETURNS TEXT AS '
BEGIN
    RETURN ''Hello'';  -- 单引号要写两次
END;
' LANGUAGE plpgsql;
 
-- 用 $$：清晰（推荐）
CREATE FUNCTION my_func() RETURNS TEXT AS $$
BEGIN
    RETURN 'Hello';  -- 不需要转义
END;
$$ LANGUAGE plpgsql;

你也可以自定义标签：$body$...$body$，但 $$ 最常用。

`NEW` 和 `OLD` 的字段从哪来？

Trigger 绑定到某张表时，NEW/OLD 自动包含该表的所有字段：

-- 假设表结构
CREATE TABLE users (
    id INT,
    name TEXT,
    email TEXT,
    updated_at TIMESTAMP
);
 
-- Trigger 函数里，NEW 就是 users 表的一整行
CREATE FUNCTION my_trigger() RETURNS TRIGGER AS $$
BEGIN
    -- 可以访问任何字段
    RAISE NOTICE 'User %: % -> %', NEW.id, OLD.name, NEW.name;
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

如果访问不存在的字段（如 NEW.non_existent），运行时会报错。

常见用法

1. 自动更新时间戳

最常见的场景：每次修改记录时，自动更新 updated_at 字段。

-- 创建通用函数
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    -- 前提：表必须有 updated_at 字段
    NEW.updated_at = NOW();
    RETURN NEW;  -- 返回修改后的行，继续执行
END;
$$ LANGUAGE plpgsql;
 
-- 绑定到 users 表（假设 users 表有 updated_at 字段）
CREATE TRIGGER users_update_timestamp
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

应用层只需要 UPDATE users SET name = 'Alice'，updated_at 自动更新。

2. 审计日志（Audit Log）

记录谁、什么时候、做了什么修改：

CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    table_name TEXT,
    operation TEXT,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT NOW(),
    changed_by TEXT
);
 
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_logs (
        table_name, operation, old_data, new_data, changed_by
    ) VALUES (
        -- TG_TABLE_NAME: Trigger 内置变量，自动获取触发的表名（如 'orders'）
        TG_TABLE_NAME,
        
        -- TG_OP: Trigger 内置变量，当前操作类型（'INSERT' / 'UPDATE' / 'DELETE'）
        TG_OP,
        
        -- old_data: 只有 DELETE 时才有旧数据（INSERT 没有旧数据）
        -- row_to_json(OLD): 把整行数据转成 JSON 对象，如 {"id": 1, "status": "pending"}
        CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD) ELSE NULL END,
        
        -- new_data: INSERT 和 UPDATE 才有新数据（DELETE 没有新数据）
        -- row_to_json(NEW): 把整行数据转成 JSON 对象
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW) ELSE NULL END,
        
        -- current_user: 内置变量，当前数据库用户名
        current_user
    );
    
    -- COALESCE(NEW, OLD): 返回第一个非 NULL 的值
    -- INSERT/UPDATE 返回 NEW，DELETE 返回 OLD
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER orders_audit
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION log_changes();

每次 orders 表变化，自动记录完整的前后快照。

3. 数据完整性约束（Database Constraint）

注意：大部分业务规则应该放在应用层，Trigger 只作为”最后一道防线”，防止直接 SQL 操作绕过应用层校验。

例如，强制某些字段的取值范围（当 CHECK 约束无法表达时）：

CREATE OR REPLACE FUNCTION check_status_transition()
RETURNS TRIGGER AS $$
BEGIN
    -- 防止订单状态从 'completed' 改回 'pending'
    IF OLD.status = 'completed' AND NEW.status = 'pending' THEN
        RAISE EXCEPTION 'Cannot revert completed order to pending';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER orders_status_check
    BEFORE UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION check_status_transition();

这种状态机约束在数据库层面保证，即使有人直接执行 SQL 也会被拒绝。

4. 自动同步关联表

订单状态变化时，更新用户的订单统计：

CREATE OR REPLACE FUNCTION sync_user_stats()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE users
    SET total_orders = (
        SELECT COUNT(*) FROM orders WHERE user_id = NEW.user_id
    )
    WHERE id = NEW.user_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER orders_sync_stats
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION sync_user_stats();

使用场景

场景	适合 Trigger	建议
自动更新时间戳	✅	简单、无副作用、不需要业务逻辑介入
审计日志	✅	必须捕获所有变更，包括直接 SQL 操作
数据库级约束	✅	状态机、跨表一致性（最后防线）
业务规则校验	⚠️	应该放在应用层，Trigger 仅作兜底
复杂业务逻辑	❌	难调试、难测试、难维护
外部服务调用	❌	数据库不应该发 HTTP 请求
大量计算	❌	会拖慢所有写操作

推荐实践

✅ DO：保持简单

Trigger 应该是”薄薄的一层”，执行时间控制在毫秒级：

-- 好：只做一件事
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();
 
-- 坏：复杂计算 + 多次查询
CREATE OR REPLACE FUNCTION complex_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- 查询外部表
    SELECT ... FROM other_table WHERE ...;
    
    -- 复杂计算
    FOR i IN 1..1000 LOOP
        ...
    END LOOP;
    
    -- 更新多张表
    UPDATE table1 ...;
    UPDATE table2 ...;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

✅ DO：显式命名

命名规范：{table}_{event}_{purpose}

-- 清晰
CREATE TRIGGER users_update_timestamp ...
CREATE TRIGGER orders_after_audit ...
CREATE TRIGGER products_before_validate ...
 
-- 模糊
CREATE TRIGGER trigger1 ...
CREATE TRIGGER t_users ...

✅ DO：记录日志

Trigger 执行时不会输出到应用日志，出问题难排查。使用 RAISE NOTICE 或专门的日志表：

CREATE OR REPLACE FUNCTION my_trigger()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Trigger executed: %, row: %', TG_OP, NEW.id;
    -- 实际逻辑
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

✅ DO：考虑批量操作

如果使用 FOR EACH ROW，批量插入 10,000 行会触发 10,000 次 Trigger：

-- 每行触发一次
CREATE TRIGGER my_trigger
    AFTER INSERT ON orders
    FOR EACH ROW
    EXECUTE FUNCTION log_changes();
 
-- 整个 SQL 只触发一次（但无法访问 NEW/OLD）
CREATE TRIGGER my_trigger
    AFTER INSERT ON orders
    FOR EACH STATEMENT
    EXECUTE FUNCTION notify_changes();

根据场景选择 FOR EACH ROW 还是 FOR EACH STATEMENT。

DON’T：在 Trigger 里调外部服务

Trigger 运行在数据库事务中，外部服务（HTTP API、消息队列）不可控：

-- 非常危险
CREATE OR REPLACE FUNCTION send_email()
RETURNS TRIGGER AS $$
BEGIN
    -- HTTP 请求卡住 → 整个事务卡住
    -- API 失败 → 整个事务回滚
    -- 网络抖动 → 数据库写入变慢
    PERFORM http_post('https://api.example.com/notify', ...);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

正确做法：Trigger 只写一条”待处理”记录到队列表，后台任务再异步调用外部服务。

DON’T：隐藏关键业务逻辑

如果核心业务规则藏在 Trigger 里，新人看代码时完全不知道：

# 应用代码
order.status = 'paid'
order.save()
 
# 实际发生：Trigger 自动扣库存、发邮件、更新积分……
# 开发者：？？？为什么库存变了？

原则：

应用层优先：业务规则（权限、格式校验、业务流程）都应该在代码里
Trigger 作为防线：只处理”基础设施级”自动化（时间戳、审计）和数据库级约束（防止绕过应用层的直接 SQL 操作）

DON’T：Trigger 套 Trigger

A 表的 Trigger 更新 B 表 → B 表的 Trigger 更新 C 表 → C 表的 Trigger 又更新 A 表……

-- A 更新时触发 B
CREATE TRIGGER a_trigger ... UPDATE b ...;
 
-- B 更新时触发 C
CREATE TRIGGER b_trigger ... UPDATE c ...;
 
-- C 更新时触发 A（循环依赖！）
CREATE TRIGGER c_trigger ... UPDATE a ...;

调试时你会疯掉。保持 Trigger 的依赖关系扁平化。

排查 Trigger 问题

怀疑 Trigger 导致性能问题时：

-- 查看某张表的所有 Trigger
SELECT 
    tgname AS trigger_name,
    tgtype AS trigger_type,
    tgenabled AS enabled
FROM pg_trigger
WHERE tgrelid = 'orders'::regclass;
 
-- 临时禁用 Trigger
ALTER TABLE orders DISABLE TRIGGER orders_audit;
 
-- 重新启用
ALTER TABLE orders ENABLE TRIGGER orders_audit;
 
-- 删除 Trigger
DROP TRIGGER orders_audit ON orders;

何时不用 Trigger？

如果符合以下情况，考虑在应用层实现：

需要复杂的外部依赖（发消息、调 API、读配置文件）
逻辑经常变化（Trigger 修改需要数据库权限 + 停机）
需要单元测试（测试 Trigger 比测试应用代码难得多）

个人感觉：Trigger 适合做”数据库内部的自动化”，不适合做”业务流程的自动化”。

Holly

Explorer

SQL - Trigger 让人爱恨交加的钩子

Trigger 的工作原理

语法细节

`$$` 字符串分隔符

`NEW` 和 `OLD` 的字段从哪来？

常见用法

1. 自动更新时间戳

2. 审计日志（Audit Log）

3. 数据完整性约束（Database Constraint）

4. 自动同步关联表

使用场景

推荐实践

✅ DO：保持简单

✅ DO：显式命名

✅ DO：记录日志

✅ DO：考虑批量操作

DON’T：在 Trigger 里调外部服务

DON’T：隐藏关键业务逻辑

DON’T：Trigger 套 Trigger

排查 Trigger 问题

何时不用 Trigger？

Graph View

Table of Contents

Holly

Explorer

SQL - Trigger 让人爱恨交加的钩子

Trigger 的工作原理

语法细节

$$ 字符串分隔符

NEW 和 OLD 的字段从哪来？

常见用法

1. 自动更新时间戳

2. 审计日志（Audit Log）

3. 数据完整性约束（Database Constraint）

4. 自动同步关联表

使用场景

推荐实践

✅ DO：保持简单

✅ DO：显式命名

✅ DO：记录日志

✅ DO：考虑批量操作

DON’T：在 Trigger 里调外部服务

DON’T：隐藏关键业务逻辑

DON’T：Trigger 套 Trigger

排查 Trigger 问题

何时不用 Trigger？

Graph View

Table of Contents

`$$` 字符串分隔符

`NEW` 和 `OLD` 的字段从哪来？