SQL历史表和触发器

Question

我需要在调用 UPDATE 时保留对特定表所做的更改历史记录，但只关心特定列。

所以，我创建了一个历史表:

CREATE TABLE [dbo].[SourceTable_History](
    [SourceTable_HistoryID] [int] IDENTITY(1,1) NOT NULL,
    [SourceTableID] [int] NOT NULL,
    [EventDate] [date] NOT NULL,
    [EventUser] [date] NOT NULL,
    [ChangedColumn] VARCHAR(50) NOT NULL,
    [PreviousValue] VARCHAR(100) NULL,
    [NewValue] VARCHAR(100) NULL

    CONSTRAINT pk_SourceTable_History PRIMARY KEY ([SourceTable_HistoryID]),
    CONSTRAINT fk_SourceTable_HistoryID_History_Source FOREIGN KEY ([SourceTableID]) REFERENCES SourceTable (SourceTableId)
)

我的计划是在 SourceTable 上创建一个 Update 触发器。业务只关心对某些列的更改，因此，在伪代码中，我计划做类似的事情

If source.Start <> new.start
Insert into history (PrimaryKey, EventDate, EventUser, ColumnName, OldValue, NewValue)
(PK, GETDATYE(), updateuser, "StartDate", old.value, new.value)

我们想要历史记录的每列都会有一个类似的 block 。

我们不允许使用 CDC，因此我们必须推出自己的 CDC，这是我目前的计划。

这看起来是一个合适的计划吗？

我们需要监控 7 个表，每个表的列数在 2 到 5 列之间。

我只需要弄清楚如何获取触发器来首先比较特定列的前后值，然后写入新行。

我认为这很简单:

CREATE TRIGGER tr_PersonInCareSupportNeeds_History
ON PersonInCareSupportNeeds
FOR UPDATE
AS
BEGIN
    IF(inserted.StartDate <> deleted.StartDate)
    BEGIN
        INSERT INTO [dbo].[PersonInCareSupportNeeds_History]
        ([PersonInCareSupportNeedsID], [EventDate], [EventUser], [ChangedColumn], [PreviousValue], [NewValue])
        VALUES
        (inserted.[PersonInCareSupportNeedsID], GETDATE(), [LastUpdateUser], 'StartDate', deleted.[StartDate], deleted.[StartDate])
    END
END

Answer 1

我们有基于触发器的审计系统，我们基本上是通过分析用于生成审计触发器的第三方工具 ApexSQL Audit 如何创建触发器和管理存储来创建它，并基于此开发了我们自己的系统。

我认为您的解决方案通常是好的，但您需要考虑稍微修改存储并计划扩展。

如果企业决定跟踪所有表中的所有列怎么办？如果他们决定也跟踪插入和删除怎么办？您的解决方案能够适应这种情况吗？

存储:使用两个表来保存数据。一张表用于保存有关事务的所有信息(时间、人物、应用程序名称、表名称、模式名称、受影响的行等)。另一个表保存实际数据(前后值、主键等)。

触发器:我们最终得到了一个用于插入、更新和删除触发器的模板以及非常简单的 C# 应用程序，我们在其中输入表和列，以便应用程序输出 DDL。这节省了我们很多时间。