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

因此,我创建了一个History表:

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触发器。该业务只关心对某些列的更改,因此,在psudo代码中,我打算做类似的事情

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


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

我们不允许使用CDC,因此我们必须自己开发CDC,到目前为止,这是我的计划。

这似乎是一个合适的计划吗?

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

我只需要弄清楚如何获取触发器以首先在特定columnm的值之前和之后对comapr进行映射,然后编写新行。

我认为这很简单:

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

最佳答案

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

我认为您的解决方案总体来说还可以,但是您需要考虑稍微修改存储并计划扩展。

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

存储:使用两个表来保存数据。一个表,用于保存有关事务的所有信息(时间,人员,应用程序名称,表名称,架构名称,受影响的行等)。还有另一个表,用于保存实际数据(值,主键之前和之后等)。

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

09-11 18:32
查看更多