如何编写PostgreSQL触发器实现多表审计（含新旧值及表名）

1. 创建审计表 #

先按需求创建audit_details表，适配字段类型以兼容多场景：

CREATE TABLE audit_details (
    date_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    table_name TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    "user" TEXT NOT NULL DEFAULT CURRENT_USER,
    primary_key_of_table TEXT NOT NULL
);

说明：采用JSONB而非JSON，支持索引与高效查询；date_time带时区保证时间准确性；primary_key_of_table存文本格式，兼容不同表的主键类型（整数、UUID等）。

2. 编写通用触发器函数 #

这个函数可自动适配不同表结构，动态提取审计所需信息：

CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    pk_columns TEXT[];
    pk_value TEXT;
BEGIN
    -- 动态获取当前表的主键列名
    SELECT ARRAY_AGG(attname)
    INTO pk_columns
    FROM pg_constraint con
    JOIN pg_attribute att ON att.attrelid = con.conrelid AND att.attnum = ANY(con.conkey)
    WHERE con.contype = 'p' AND con.conrelid = TG_RELID;

    -- 根据操作类型提取主键值
    IF TG_OP = 'DELETE' THEN
        SELECT string_agg((row_to_json(OLD)->>col)::TEXT, ', ')
        INTO pk_value
        FROM unnest(pk_columns) col;
    ELSE
        SELECT string_agg((row_to_json(NEW)->>col)::TEXT, ', ')
        INTO pk_value
        FROM unnest(pk_columns) col;
    END IF;

    -- 插入审计记录
    INSERT INTO audit_details (table_name, old_data, new_data, primary_key_of_table)
    VALUES (
        TG_TABLE_NAME,
        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,
        pk_value
    );

    RETURN NULL; -- 返回NULL不影响原表操作逻辑
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

说明：

• TG_RELID是触发器关联表的OID，用于动态查询主键；
• TG_TABLE_NAME直接获取触发操作的表名；
• SECURITY DEFINER确保触发器以创建者权限执行，避免跨用户操作的权限问题；
• 按操作类型（INSERT/UPDATE/DELETE）分别赋值old_data和new_data，避免无效数据写入。

3. 为目标表绑定触发器 #

假设你的5张表为table_a、table_b、table_c、table_d、table_e，为每张表创建触发器：

-- 为table_a创建审计触发器
CREATE TRIGGER audit_table_a
AFTER INSERT OR UPDATE OR DELETE ON table_a
FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- 为table_b创建审计触发器
CREATE TRIGGER audit_table_b
AFTER INSERT OR UPDATE OR DELETE ON table_b
FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- 重复上述逻辑，为table_c/table_d/table_e创建触发器

4. 验证效果 #

执行表操作后，查询审计表查看结果：

SELECT * FROM audit_details ORDER BY date_time DESC;

示例返回结果 #

内容的提问来源于stack exchange，提问作者Ranjit Chavan