39、PostgreSQL触发器：行级触发器、语句级触发器、事件触发器、审计日志实现

触发器这东西，说白了就是数据库里的「自动回调函数」。你往表里插一条数据，它自动跑一段逻辑；你删一条记录，它再跑一段逻辑。我在项目中见过太多人把触发器当成万能工具，结果搞出一堆性能坑。今天咱们就把PostgreSQL的几种触发器掰开揉碎讲清楚。

触发器的三种基本形态

PostgreSQL支持三种触发器：行级、语句级、事件级。我习惯这么理解——行级是「每行触发一次」，语句级是「每条SQL触发一次」，事件级是「数据库启动、关闭、DDL变更时触发」。

类型 触发时机 典型场景
行级触发器 每行数据变更时触发 审计日志、数据校验、字段自动填充
语句级触发器 每条SQL语句执行后触发一次 批量操作后的汇总统计、权限检查
事件触发器 数据库事件（DDL、启动、崩溃恢复） 禁止删表、记录表结构变更

核心区别：如果你用UPDATE一次更新了1000行，行级触发器会执行1000次，语句级触发器只执行1次。性能差距就在这里。

行级触发器实战：审计日志

先来个最常用的——记录谁在什么时候改了哪张表的哪条数据。我在项目中做过一个金融系统，所有资金流水表的变更都必须留痕，行级触发器是首选方案。

-- 创建审计日志表
CREATE TABLE audit_log (
    log_id SERIAL 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 = 'INSERT' THEN
        INSERT INTO audit_log(table_name, operation, new_data)
        VALUES (TG_TABLE_NAME, 'INSERT', row_to_json(NEW)::jsonb);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log(table_name, operation, old_data, new_data)
        VALUES (TG_TABLE_NAME, 'UPDATE', 
                row_to_json(OLD)::jsonb, 
                row_to_json(NEW)::jsonb);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log(table_name, operation, old_data)
        VALUES (TG_TABLE_NAME, 'DELETE', row_to_json(OLD)::jsonb);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 绑定到目标表
CREATE TRIGGER trg_audit_accounts
AFTER INSERT OR UPDATE OR DELETE ON accounts
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

我的习惯：触发器函数名用 _func 后缀，触发器名用 trg_ 前缀。这样在pgAdmin里一眼就能区分函数和触发器，排查问题时省不少时间。

语句级触发器：批量操作的守护者

你想想看，如果一张表每天凌晨批量导入10万条数据，行级触发器要写10万次审计日志，磁盘IO直接爆炸。这时候就该语句级触发器上场了。

-- 创建汇总统计表
CREATE TABLE batch_summary (
    table_name TEXT PRIMARY KEY,
    last_batch_time TIMESTAMPTZ,
    rows_affected INT
);

-- 语句级触发器函数
CREATE OR REPLACE FUNCTION batch_summary_func()
RETURNS TRIGGER AS $$
BEGIN
    -- 只记录批量操作后的汇总信息
    INSERT INTO batch_summary(table_name, last_batch_time, rows_affected)
    VALUES (TG_TABLE_NAME, now(), 1)
    ON CONFLICT (table_name) 
    DO UPDATE SET last_batch_time = now(),
                  rows_affected = batch_summary.rows_affected + 1;
    RETURN NULL;  -- 语句级触发器必须返回NULL
END;
$$ LANGUAGE plpgsql;

-- 绑定为语句级触发器
CREATE TRIGGER trg_batch_summary
AFTER INSERT ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION batch_summary_func();

我曾经踩过的坑：语句级触发器里不能访问 OLD 和 NEW 记录，因为它是针对整个语句的，不是针对某一行。如果你在语句级触发器里写了 NEW.id，PostgreSQL会直接报错。这个错误我至少帮三个同事排查过。

事件触发器：数据库的看门狗

事件触发器是PostgreSQL 9.3引入的特性，它能捕获DDL操作。比如你想禁止任何人删除生产环境的表，或者记录所有表结构变更，事件触发器就是最佳选择。

-- 创建事件触发器函数
CREATE OR REPLACE FUNCTION prevent_drop_table()
RETURNS EVENT_TRIGGER AS $$
BEGIN
    IF TG_EVENT = 'sql_drop' THEN
        RAISE EXCEPTION '禁止删除表：% 操作被事件触发器拦截', 
                         TG_TAG;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 创建事件触发器
CREATE EVENT TRIGGER evt_prevent_drop
ON sql_drop
EXECUTE FUNCTION prevent_drop_table();

嗯，这里要注意：事件触发器的作用域是整个数据库，不是某个表。你创建之后，任何DROP TABLE操作都会被拦截。我在测试环境吃过这个亏——想删一张临时表，结果死活删不掉，查了半天才发现是事件触发器在作祟。

完整的审计日志实现方案

把上面几种触发器组合起来，就能搭建一套完整的审计系统。我个人推荐这种分层设计：

-- 1. 审计配置表（控制哪些表需要审计）
CREATE TABLE audit_config (
    table_name TEXT PRIMARY KEY,
    enabled BOOLEAN DEFAULT true,
    log_level TEXT DEFAULT 'normal'  -- normal, verbose, minimal
);

-- 2. 通用审计函数（读取配置表决定行为）
CREATE OR REPLACE FUNCTION smart_audit_func()
RETURNS TRIGGER AS $$
DECLARE
    v_enabled BOOLEAN;
    v_log_level TEXT;
BEGIN
    SELECT enabled, log_level INTO v_enabled, v_log_level
    FROM audit_config WHERE table_name = TG_TABLE_NAME;
    
    IF NOT v_enabled THEN
        RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
    END IF;
    
    -- 根据日志级别决定记录哪些字段
    IF v_log_level = 'minimal' THEN
        INSERT INTO audit_log(table_name, operation)
        VALUES (TG_TABLE_NAME, TG_OP);
    ELSE
        INSERT INTO audit_log(table_name, operation, old_data, new_data)
        VALUES (TG_TABLE_NAME, TG_OP,
                CASE WHEN TG_OP IN ('UPDATE','DELETE') 
                     THEN row_to_json(OLD)::jsonb ELSE NULL END,
                CASE WHEN TG_OP IN ('INSERT','UPDATE') 
                     THEN row_to_json(NEW)::jsonb ELSE NULL END);
    END IF;
    
    RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
END;
$$ LANGUAGE plpgsql;

设计思路：通过配置表控制审计行为，避免硬编码。生产环境可以动态开启/关闭某张表的审计，不需要重新创建触发器。这个方案我在一个日活百万的电商系统里跑了一年多，没出过问题。

触发器的性能陷阱与避坑指南

触发器用不好，数据库性能直接腰斩。我总结了几条铁律：

  • 不要在触发器里做复杂查询——触发器里的SQL会放大N倍（行级触发器就是N倍），一个慢查询在触发器里可能拖垮整个库。
  • 避免触发器嵌套——A表的触发器修改B表，B表的触发器又修改A表，死循环就来了。PostgreSQL虽然有max_stack_depth限制，但排查起来非常痛苦。
  • 批量操作时慎用行级触发器——如果一定要用，考虑用pg_trigger_depth()函数控制递归深度。
-- 检查触发器嵌套深度的函数
CREATE OR REPLACE FUNCTION safe_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    IF pg_trigger_depth() > 2 THEN
        RAISE WARNING '触发器嵌套过深，跳过本次操作';
        RETURN NULL;
    END IF;
    -- 正常逻辑...
END;
$$ LANGUAGE plpgsql;

我曾经接手过一个遗留系统，里面一张表的触发器嵌套了5层，每次插入一条数据要等3秒钟。后来我用pg_trigger_depth()做了限制，加上异步日志写入，性能提升了20倍。

知识体系总览

下面这张图把PostgreSQL触发器的核心脉络梳理清楚了，建议你保存下来对照学习：

PostgreSQL触发器知识体系 行级触发器 语句级触发器 事件触发器 FOR EACH ROW 可访问 OLD / NEW 性能敏感：N行触发N次 FOR EACH STATEMENT 不可访问 OLD / NEW 批量操作首选 DDL事件 / 数据库事件 作用域：整个数据库 禁止删表 / 记录DDL 核心原则：能用语句级不用行级，能用配置不用硬编码

触发器的世界其实不大，但每个细节都可能成为性能瓶颈或安全隐患。记住一条：触发器是数据库的「最后一道防线」，不是「第一选择」。能用应用层逻辑解决的问题，尽量别推到数据库层。但一旦决定用触发器，就要把它的行为、性能影响、递归风险全部考虑清楚。

公众号:蓝海资料掘金营，微信deep3321

← 上一章 📖 返回目录 下一章 →