创建DB2历史表触发器。

Question

我想创建一个历史表，以跟踪DB2中多个表中的字段更改。通常使用复制整个表结构并给其添加后缀名称（例如user -> user_history）来完成历史记录。然后，您可以使用相当简单的触发器在UPDATE时将旧记录复制到历史表中。

然而，对于我的应用程序来说，这将使用太多空间。我认为每次字段更改都将整个记录复制到另一个表中似乎不是一个好主意（至少对我来说不是）。因此，我想我可以有一个通用的“历史”表，它将跟踪单个字段的更改：

CREATE TABLE history
(
    history_id LONG GENERATED ALWAYS AS IDENTITY,
    record_id INTEGER NOT NULL,
    table_name VARCHAR(32) NOT NULL,
    field_name VARCHAR(64) NOT NULL,
    field_value VARCHAR(1024),
    change_time TIMESTAMP,
    PRIMARY KEY (history_id)
);

好的，所以我想要跟踪的每个表都有一个单一的、自动生成的id字段作为主键，这个字段将被放入'record_id'字段中。而表中最大的VARCHAR大小是1024。显然，如果非VARCHAR字段发生变化，在将记录插入历史表之前，它必须被转换为VARCHAR。

现在，这可能是一个完全愚蠢的做法（如果是的话，请告诉我为什么），但我认为这是一种追踪需要很少引用并需要长时间存储的更改的好方法。

无论如何，我需要帮助编写触发器，在更新时向历史表添加记录。举个例子，让我们看一个假想的用户表：

CREATE TABLE user
(
   user_id INTEGER GENERATED ALWAYS AS IDENTITY,
   username VARCHAR(32) NOT NULL,
   first_name VARCHAR(64) NOT NULL,
   last_name VARCHAR(64) NOT NULL,
   email_address VARCHAR(256) NOT NULL
   PRIMARY KEY(user_id)
);

所以，有人能帮我写一个触发器，在用户表更新时将更改插入历史表吗？我的猜测是需要使用一些过程性SQL来循环遍历旧记录中的字段，将它们与新记录中的字段进行比较，如果不匹配，则向历史表添加一个新条目。

如果可能的话，最好对每个表使用相同的触发器动作SQL，而不管其字段。

谢谢！

- Matthew

Answer 1

我认为这不是一个好主意，因为你会在一个大表中产生更多的开销，而且有多个值发生变化。但这取决于你的应用程序。

此外，你应该考虑这样一个历史记录表的实际价值。你必须收集很多行才能看到值的上下文变化，并且需要编写另一个应用程序来完成复杂的历史逻辑，以供最终用户使用。对于数据库管理员来说，从历史记录中恢复值也是很麻烦的。

可能听起来有点苛刻，但这并不是本意。我们店里的一位经验丰富的程序员通过表日志记录也有过类似的想法。他让它运行起来了，但它像没有明天一样吃掉了磁盘空间。

只要想想你的历史记录表应该真正实现什么目标。

- squarefox

Answer 2

你有没有考虑将这个过程分为两步？首先实现一个简单的触发器，记录整行数据的原始和修改版本。然后编写一个单独的程序，每天运行一次，按照你上面描述的方式提取修改的字段。

这样做可以让触发器更简单、更安全、更快，并且你可以有更多选择来实现后处理步骤。

- Kevin Beck

Answer 3

我们在 SQL Server 数据库上也做了类似的事情，但审计表是针对每个被审计的单独表（一个中央表会非常庞大，因为我们的数据库有数千兆字节的大小）

你需要做的一件事是确保您还记录了谁做出了更改。您还应该记录旧值和新值（如果必要，这样可以更容易地将数据放回去），以及更改类型（插入、更新、删除）。您没有提到记录从表格中删除的内容，但我们发现这些是我们最常用表格的一些内容。

我们使用动态 SQL 来生成代码以创建审计表（通过使用存储系统信息的表），所有审计表具有完全相同的结构（使得更容易获取数据）。

当您创建用于将数据存储在历史表中的代码时，请同时创建用于恢复数据的代码（如果需要）。这将在未来需要恢复某些数据并且您需要立即完成时节省大量时间。

现在我不知道您是否计划能够从历史表中还原数据，但是一旦您进行了一次，我可以保证管理层希望以这种方式使用它。

- HLGEM

---

不需要记录旧值和新值在一起，因为旧值始终是历史记录中的上一行（假设历史记录始终被正确记录）。 - lo_fye

如果它们在同一条记录中，将旧值打包使用会更容易。如果我可以选择的话，我从不依赖于行顺序来获取信息。而且我不想进行两次连接才能获取审计表中的信息。 - HLGEM

Answer 4

CREATE TABLE HIST.TB_HISTORY ( 
    HIST_ID     BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1, NO CACHE) NOT NULL,
    HIST_COLUMNNAME     VARCHAR(128) NOT NULL,
    HIST_OLDVALUE       VARCHAR(255),
    HIST_NEWVALUE       VARCHAR(255),
    HIST_CHANGEDDATE    TIMESTAMP NOT NULL
    PRIMARY KEY(HIST_SAFTYNO)
)
GO


CREATE TRIGGER COMMON.TG_BANKCODE AFTER
UPDATE OF FRD_BANKCODE ON COMMON.TB_MAINTENANCE
REFERENCING OLD AS oldcol NEW AS newcol FOR EACH ROW MODE DB2SQL
WHEN(COALESCE(newcol.FRD_BANKCODE,'#null#') <> COALESCE(oldcol.FRD_BANKCODE,'#null#'))
BEGIN ATOMIC

    CALL FB_CHECKING.SP_FRAUDHISTORY_ON_DATACHANGED(
                newcol.FRD_FRAUDID,
                'FRD_BANKCODE',
                oldcol.FRD_BANKCODE,
                newcol.FRD_BANKCODE,
                newcol.FRD_UPDATEDBY
    );--

    INSERT INTO FB_CHECKING.TB_FRAUDMAINHISTORY(        
        HIST_COLUMNNAME, 
        HIST_OLDVALUE, 
        HIST_NEWVALUE, 
        HIST_CHANGEDDATE

- Fuangwith S.