代码之家 › 专栏 › 技术社区 › David

实施现场变更跟踪的模式

change-tracking design-patterns database sql

6

David · 技术社区 · 14 年前

对于我最近的一个项目,我必须实施 现场变更跟踪 . 因此,每当用户更改字段的值时,都会记录更改,以便对更改进行全面审核。

在数据库中,我将其作为单个表实现。 FieldChanges 包含以下字段:

表名
字段名
有记录的
日期变更
改变了
因特值
文本值
数据值
布尔值

保存对对象的更改的存储过程将确定每个字段是否已更改,如果已更改,则将记录插入FieldChanges:如果已更改字段的类型为 int ,它记录在 IntValue 中的字段 场变化 表等。

这意味着对于任何具有任何ID值的表中的任何字段,我可以查询fieldChanges表以获取更改列表。

这很管用,但有点笨拙。其他实现了类似功能的人能否提出更好的方法,以及为什么他们认为它更好?

我真的很感兴趣-谢谢。

戴维

4 回复 | 直到 12 年前

1

codeConcussion 14 年前

这种企业模式是为您创建的每个表创建一个修正阴影表,以显示所有列的后映像(可能是前映像)。你将需要:

创建修订表的脚本
填充它们的触发器
并在表随时间变化时维护上述内容。

但对于一个建立良好的企业来说,所有这些都应该已经到位。

我的组织仅将其用于以下方面:

DBA和支持的审核,用于手动确定发生了什么(使用SQL)。
企业数据仓库(SAS)从生产系统中抽取所有delta进行分析。

如果操作系统本身需要,我们会创建不同的表。

2

6

Ian Boyd 13 年前

触发器。

我们编写了一个GUI(内部调用 重新加载红色矩阵 )允许轻松创建/管理审核日志记录触发器。

以下是一些使用的DDL:

AuditLog表

CREATE TABLE [AuditLog] (
    [AuditLogID] [int] IDENTITY (1, 1) NOT NULL ,
    [ChangeDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLog_ChangeDate] DEFAULT (getdate()),
    [RowGUID] [uniqueidentifier] NOT NULL ,
    [ChangeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TableName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [FieldName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OldValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NewValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Username] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Hostname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AppName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [UserGUID] [uniqueidentifier] NULL ,
    [TagGUID] [uniqueidentifier] NULL ,
    [Tag] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)

日志插入触发器

CREATE TRIGGER LogInsert_Nodes ON dbo.Nodes
FOR INSERT
AS

/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'

IF @SavedUserGUID = @NullGUID
BEGIN
    SET @SavedUserGUID = NULL
END

    /*We dont' log individual field changes Old/New because the row is new.
    So we only have one record - INSERTED*/

    INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue, NewValue)

    SELECT
        getdate(), --ChangeDate
        i.NodeGUID, --RowGUID
        'INSERTED', --ChangeType
        USER_NAME(), HOST_NAME(), APP_NAME(), 
        @SavedUserGUID, --UserGUID
        'Nodes', --TableName
        '', --FieldName
        i.ParentNodeGUID, --TagGUID
        i.Caption, --Tag
        null, --OldValue
        null --NewValue
    FROM Inserted i

触发器记录更新

CREATE TRIGGER LogUpdate_Nodes ON dbo.Nodes
FOR UPDATE AS

/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'

IF @SavedUserGUID = @NullGUID
BEGIN
    SET @SavedUserGUID = NULL
END

    /* ParentNodeGUID uniqueidentifier */
    IF UPDATE (ParentNodeGUID)
    BEGIN
        INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue, NewValue)
        SELECT 
            getdate(), --ChangeDate
            i.NodeGUID, --RowGUID
            'UPDATED', --ChangeType
            USER_NAME(), HOST_NAME(), APP_NAME(), 
            @SavedUserGUID, --UserGUID
            'Nodes', --TableName
            'ParentNodeGUID', --FieldName
            i.ParentNodeGUID, --TagGUID
            i.Caption, --Tag
            d.ParentNodeGUID, --OldValue
            i.ParentNodeGUID --NewValue
        FROM Inserted i
            INNER JOIN Deleted d
            ON i.NodeGUID = d.NodeGUID
        WHERE (d.ParentNodeGUID IS NULL AND i.ParentNodeGUID IS NOT NULL)
        OR (d.ParentNodeGUID IS NOT NULL AND i.ParentNodeGUID IS NULL)
        OR (d.ParentNodeGUID <> i.ParentNodeGUID)
    END

    /* Caption varchar(255) */
    IF UPDATE (Caption)
    BEGIN
        INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue, NewValue)
        SELECT 
            getdate(), --ChangeDate
            i.NodeGUID, --RowGUID
            'UPDATED', --ChangeType
            USER_NAME(), HOST_NAME(), APP_NAME(), 
            @SavedUserGUID, --UserGUID
            'Nodes', --TableName
            'Caption', --FieldName
            i.ParentNodeGUID, --TagGUID
            i.Caption, --Tag
            d.Caption, --OldValue
            i.Caption --NewValue
        FROM Inserted i
            INNER JOIN Deleted d
            ON i.NodeGUID = d.NodeGUID
        WHERE (d.Caption IS NULL AND i.Caption IS NOT NULL)
        OR (d.Caption IS NOT NULL AND i.Caption IS NULL)
        OR (d.Caption <> i.Caption)
    END

...

/* ImageGUID uniqueidentifier */
IF UPDATE (ImageGUID)
BEGIN
    INSERT INTO AuditLog(
        ChangeDate, RowGUID, ChangeType, 
        Username, HostName, AppName,
        UserGUID, 
        TableName, FieldName, 
        TagGUID, Tag, 
        OldValue, NewValue)
    SELECT 
        getdate(), --ChangeDate
        i.NodeGUID, --RowGUID
        'UPDATED', --ChangeType
        USER_NAME(), HOST_NAME(), APP_NAME(), 
        @SavedUserGUID, --UserGUID
        'Nodes', --TableName
        'ImageGUID', --FieldName
        i.ParentNodeGUID, --TagGUID
        i.Caption, --Tag
        (SELECT Caption FROM Nodes WHERE NodeGUID = d.ImageGUID), --OldValue
        (SELECT Caption FROM Nodes WHERE NodeGUID = i.ImageGUID) --New Value
    FROM Inserted i
        INNER JOIN Deleted d
        ON i.NodeGUID = d.NodeGUID
    WHERE (d.ImageGUID IS NULL AND i.ImageGUID IS NOT NULL)
    OR (d.ImageGUID IS NOT NULL AND i.ImageGUID IS NULL)
    OR (d.ImageGUID <> i.ImageGUID)
END

日志删除触发器

CREATE TRIGGER LogDelete_Nodes ON dbo.Nodes
FOR DELETE
AS

/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'

IF @SavedUserGUID = @NullGUID
BEGIN
    SET @SavedUserGUID = NULL
END

    /*We dont' log individual field changes Old/New because the row is new.
    So we only have one record - DELETED*/

    INSERT INTO AuditLog(
            ChangeDate, RowGUID, ChangeType, 
            Username, HostName, AppName,
            UserGUID, 
            TableName, FieldName, 
            TagGUID, Tag, 
            OldValue,NewValue)

    SELECT
        getdate(), --ChangeDate
        d.NodeGUID, --RowGUID
        'DELETED', --ChangeType
        USER_NAME(), HOST_NAME(), APP_NAME(), 
        @SavedUserGUID, --UserGUID
        'Nodes', --TableName
        '', --FieldName
        d.ParentNodeGUID, --TagGUID
        d.Caption, --Tag
        null, --OldValue
        null --NewValue
    FROM Deleted d

为了知道软件中的哪个用户进行了更新,每个连接都通过调用存储过程“登录到SQL Server上”:

CREATE PROCEDURE dbo.SaveContextUserGUID @UserGUID uniqueidentifier AS

/* Saves the given UserGUID as the session's "Context Information" */
IF @UserGUID IS NULL
BEGIN
    PRINT 'Emptying CONTEXT_INFO because of null @UserGUID'
    DECLARE @BinVar varbinary(128)
    SET @BinVar = CAST( REPLICATE( 0x00, 128 ) AS varbinary(128) )
    SET CONTEXT_INFO @BinVar
    RETURN 0
END

DECLARE @UserGUIDBinary binary(16) --a guid is 16 bytes
SELECT @UserGUIDBinary = CAST(@UserGUID as binary(16))
SET CONTEXT_INFO @UserGUIDBinary


/* To load the guid back 
DECLARE @SavedUserGUID uniqueidentifier

SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

select @SavedUserGUID AS UserGUID
*/

笔记

stackoverflow代码格式删除了大多数空行,因此格式化很糟糕
我们使用的是用户表,而不是集成的安全性
此代码提供方便-不允许对我们的设计选择进行评判。纯粹主义者可能会坚持所有日志代码都应该在业务层中完成——他们可以到这里为我们编写/维护日志代码。
不能使用SQL Server中的触发器记录blob(没有blog的“before”版本-只有“before”版本)。text和ntext都是blob,这会使注释变得不可读取,或者使它们变为varchar(2000)。
标记列用作任意文本来标识行(例如,如果删除了客户,则标记将在审计日志表中显示“General Motors North America”。
tagguid用于指向行的“父级”。例如日志记录 发票行项目 指向 发票头 . 这样,任何搜索与特定发票相关的审核日志条目的人都会在审核跟踪中通过行项目的tagguid找到已删除的“行项目”。
有时,“oldvalue”和“newvalue”值会作为子选择写入,以获得有意义的字符串。即“

旧值:233d-AD34234.. 新值:883-SDF34…

在审计跟踪中不如:

OldValue: Daimler Chrysler
NewValue: Cerberus Capital Management

期末笔记 :不要做我们做的事。这对我们来说很好,但其他人都可以不使用它。

3

0

Messa 14 年前

我通过版本控制来解决这个问题。一个版本-一个表行。最新版本-最后更新日期最大的行。

4

0

zs2020 14 年前

只需创建触发器,让触发器自动跟踪更改并将其记录在审计表中。