基于触发器的审计日志：实现时间旅行与行级撤销

记录所有数据变更历史，并允许回滚到任意时间点。 · 难度：入门 · +10XP

基于触发器的审计日志：实现时间旅行与行级撤销

普通审计教程只记录谁做了什么，但本课教你如何利用触发器存储完整的旧值+新值，并使用窗口函数实现行级版本回溯。你将构建一个audit_log表，记录每次UPDATE/DELETE的前后镜像，然后通过编写一个undo函数，根据主键和时间戳重建指定行在历史某一时刻的状态。我们还探讨了如何避免触发器在批量操作时的性能灾难（使用transition tables）。

-- 审计日志表
CREATE TABLE audit_log (
    table_name text,
    row_id int,
    operation text,
    old_data jsonb,
    new_data jsonb,
    changed_at timestamptz DEFAULT now()
);
-- 撤销到特定时间的函数
CREATE OR REPLACE FUNCTION rollback_to_time(target_time timestamptz)
RETURNS void AS $$
BEGIN
    -- 用窗口函数找出目标时间点最近的有效状态
    WITH latest AS (
        SELECT DISTINCT ON (row_id) row_id, old_data
        FROM audit_log
        WHERE changed_at <= target_time
        ORDER BY row_id, changed_at DESC
    )
    UPDATE main_table
    SET data = latest.old_data->>'data'
    FROM latest
    WHERE main_table.id = latest.row_id;
END;
$$ LANGUAGE plpgsql;