如何在ClickHouse中添加Insert/Update/Delete触发器追踪表变更并同步至另一表

ClickHouse没有原生支持Oracle那样的行级AFTER/BEFORE触发器，但可以通过以下几种替代方案，实现追踪指定表的Insert/Update/Delete变更并将数据写入另一张表的需求：

1. 物化视图（Materialized View） #

这是最常用的无侵入式方案，适配ClickHouse的MergeTree引擎特性，可追踪INSERT及基于版本逻辑的UPDATE/DELETE操作：

第一步：创建变更日志表 #

先定义存储变更记录的目标表：

CREATE TABLE change_log (
    action_type String,
    old_data JSON,
    new_data JSON,
    event_time DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY event_time;

追踪INSERT操作 #

创建物化视图监听源表的插入行为，自动同步写入变更日志：

CREATE MATERIALIZED VIEW mv_insert_log TO change_log AS
SELECT
    'insert' AS action_type,
    JSONParse('{}') AS old_data,
    JSONStringify(t) AS new_data,
    now() AS event_time
FROM source_table t;

追踪UPDATE/DELETE操作 #

ClickHouse的UPDATE/DELETE通过标记旧行失效、插入新行实现，推荐使用VersionedCollapsingMergeTree作为源表引擎，再通过物化视图捕捉新旧版本数据：

-- 源表使用VersionedCollapsingMergeTree
CREATE TABLE source_table (
    id UInt64,
    value String,
    version UInt64,
    sign Int8
) ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY id;

-- 创建物化视图捕捉全量变更
CREATE MATERIALIZED VIEW mv_full_change_log TO change_log AS
SELECT
    CASE WHEN sign = 1 THEN 'insert/update' ELSE 'delete' END AS action_type,
    JSONStringify(toJSONString(map('value', prev_value))) AS old_data,
    JSONStringify(toJSONString(map('value', current_value))) AS new_data,
    now() AS event_time
FROM (
    SELECT
        id,
        sign,
        -- 关联获取同ID的前一个版本数据
        anyIf(value, sign = -1) OVER (PARTITION BY id ORDER BY version) AS prev_value,
        anyIf(value, sign = 1) OVER (PARTITION BY id ORDER BY version) AS current_value
    FROM source_table
)
WHERE sign != 0; -- 过滤已合并的无效行

2. 应用层直接处理 #

在业务代码的写入逻辑中，每次执行Insert/Update/Delete操作时，同步向变更日志表写入记录。这种方式完全可控，适合需要自定义业务逻辑的复杂场景：

-- 执行INSERT时
INSERT INTO source_table VALUES (1, 'test', 1, 1);
INSERT INTO change_log VALUES ('insert', '{}', '{"id":1,"value":"test"}', now());

-- 执行UPDATE时（模拟ClickHouse的版本更新逻辑）
INSERT INTO source_table VALUES (1, 'new_test', 2, 1);
INSERT INTO source_table VALUES (1, 'test', 2, -1);
INSERT INTO change_log VALUES ('update', '{"id":1,"value":"test"}', '{"id":1,"value":"new_test"}', now());

3. 系统表审计追踪 #

ClickHouse的系统表如system.part_log、system.replication_queue会记录表的分区变更、复制操作等系统级信息，可通过查询这些表提取变更线索。但这种方式偏向全局审计，灵活性较低，仅适合简单的监控场景。

方案对比 #

Oracle的行级触发器是数据库内部自动触发的行级逻辑，而ClickHouse的方案更依赖引擎特性或外部逻辑配合，但都能实现类似的变更追踪效果。其中物化视图适合无侵入式的基础追踪，应用层处理适合复杂业务场景。

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