审核表中更改的最佳方法

I'm designing a .NET application where user can copy, edit, update or delete a record from a DataGrid element that is getting data from a table.

我正在设计一个.NET应用程序,用户可以从一个从表中获取数据的DataGrid元素复制,编辑,更新或删除记录。

In the design I need to be able to maintain the versions to check all the changes that were made to the table by users. Can someone suggest what would be the best way to implement this requirement.

在设计中,我需要能够维护版本以检查用户对表所做的所有更改。有人可以建议实施此要求的最佳方式。

Thanks a lot.

非常感谢。

2 个解决方案

#1

-1

If you are talking about a database table, then it's the best practice to actually never delete data from it. You should set a flag column in the table which denotes the operation performed in the corresponding row.

如果您正在谈论数据库表,那么实际上永远不会从中删除数据是最佳实践。您应该在表中设置一个标志列,表示在相应行中执行的操作。

You can set it to 0 for active rows, 1 if the row is deleted. So, basically, you will perform an Update operation while deleting a row. Same can be followed for other CRUD operations.

您可以将其设置为0表示活动行,如果删除行,则设置为1。因此,基本上,您将在删除行时执行更新操作。其他CRUD操作也可以遵循相同的操作。

#2

As mentioned by Sean Lange, this is a complex topic and there is no silver bullet.

正如Sean Lange所提到的,这是一个复杂的话题,没有银弹。

Change Data Capture is designed explicitly for this problem.

Change Data Capture是针对此问题明确设计的。

If you don't want to enable CDC, then triggers are usually your best bet.

如果您不想启用CDC,那么触发通常是您最好的选择。

Here is an example of an auditing trigger utilizing rowversion\timestamp:

以下是使用rowversion \ timestamp的审计触发器的示例:

CREATE TABLE [dbo].[Table1](
    [Id] [int] NOT NULL,
    [Data] [nvarchar](50) NULL,
    [Version] [timestamp] NOT NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Table1_History](
    [Id] [int] NOT NULL,
    [Data] [nvarchar](50) NULL,
    [Version] [binary](8) NOT NULL,
    [ModDate] [datetimeoffset](7) NOT NULL,
    [ModUser] [nvarchar](50) NOT NULL,
    [Operation] CHAR(1) NOT NULL
    ) ON [PRIMARY]

GO

CREATE TRIGGER [dbo].[trgTable1_History] 
   ON  [dbo].[Table1]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @now DATETIMEOFFSET(7) = SYSDATETIMEOFFSET()
    INSERT INTO dbo.Table1_History
    (Id, Data, Version, ModDate, ModUser, Operation)
    SELECT Id, Data, Version, @now, SYSTEM_USER, 'I' from inserted 

    INSERT INTO dbo.Table1_History
    (Id, Data, Version, ModDate, ModUser, Operation)
    SELECT Id, Data, Version, @now, SYSTEM_USER, 'D' from deleted 

END

The timestamp column will automatically update upon every change to the row.

时间戳列将在每次更改行时自动更新。

This provides you current + history in your audit table(which simplifies reporting). The Version column gives you an easy lookup between Table1 and Table1_History, in the event that you want to know the exact audit details of the current row. Updates are designated by a DELETE(D) and INSERT(I) occurring simultaneously in the audit.

这为您提供了审计表中的当前+历史记录(简化了报表)。如果您想知道当前行的确切审计详细信息,则“版本”列可让您在Table1和Table1_History之间轻松查找。更新由DELETE(D)和INSERT(I)在审计中同时指定。

#1

-1