如何在sql server中创建审计表?我需要创建每个审计表，或者可以管理单个表的所有审计

I am using MVC5. created Entity framework. i have totally 15 tables . now i want Recent activity in my Project . i have plan use audit table . i need to create audit each table or single table can mange ? what are fields come on inside audit table ?

我用MVC5。实体框架创建的。我总共有15张桌子。现在我想在我的项目中加入最近的活动。我有计划使用审计表。我需要创建审核每个表或单个表可以吗?审计表中的字段是什么?

I have TP_users table

我有TP_users表

Id  int Unchecked
UserName    nvarchar(50)
UserEmail   nvarchar(50)    
DisplayName nvarchar(50)    
Password    nvarchar(50)    
RoleId      int
IsActive    bit 
ClientId    int

Now i want create audit table TP_users table .whare are fields come inside audit table ? how to use Audit table entity framework ?

现在我想要创建审核表TP_users表。如何使用审计表实体框架?

2 个解决方案

#1

We use an entity like below:

我们使用如下实体:

public class Audit
{
    [Required]
    [StringLength(6)]
    public string Action { get; set; }
    public string Changes { get; set; }
    public string PK { get; set; }
    [Required]
    public DateTime RevisionStamp { get; set; }
    [Required]
    [StringLength(50)]
    public string TableName { get; set; }
    [Required]
    [StringLength(50)]
    public string Username { get; set; }
}

We use this for auditing all of our tables. Action property says what type of change has occurred like 'update', 'insert' or 'delete'. Column values before and after the change are saved in Changes column as a JSON like below:

我们用这个来审计我们所有的表。Action属性表示发生了什么类型的更改，如“更新”、“插入”或“删除”。更改前后的列值作为JSON保存在Changes列中，如下所示:

[{"FieldName":"ID","ValueBefore":"2","ValueAfter":"2"},{"FieldName":"SettingTypeIndex","ValueBefore":"FiscalYear","ValueAfter":"FiscalYear"},{"FieldName":"Value","ValueBefore":"2015","ValueAfter":"2016"},{"FieldName":"Year","ValueBefore":"0","ValueAfter":"0"},{"FieldName":"DateInserted","ValueBefore":"2016-04-11 8:45:08 AM","ValueAfter":"2016-04-11 8:45:08 AM"},{"FieldName":"DateModified","ValueBefore":"2016-07-28 9:45:46 AM","ValueAfter":"2016-07-28 9:47:17 AM"},{"FieldName":"CreatorUserID","ValueBefore":"b44792db-1fbd-44d2-9f7d-cf05c6a2f922","ValueAfter":"b44792db-1fbd-44d2-9f7d-cf05c6a2f922"},{"FieldName":"UpdaterUserID","ValueBefore":"b44792db-1fbd-44d2-9f7d-cf05c6a2f922","ValueAfter":"b44792db-1fbd-44d2-9f7d-cf05c6a2f922"}]

“”“”:“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”“我”},{“字段名”:“CreatorUserID”,“ValueBefore”:“b44792db-1fbd-44d2-9f7d-cf05c6a2f922”,“ValueAfter”:“b44792db-1fbd-44d2-9f7d-cf05c6a2f922”},{“字段名”:“UpdaterUserID”,“ValueBefore”:“b44792db-1fbd-44d2-9f7d-cf05c6a2f922”,“ValueAfter”:“b44792db-1fbd-44d2-9f7d-cf05c6a2f922”}]

So this way we can have the rollback functionality (if ever needed). The primary key of the changed record is saved in PK property. The date of the change is saved in RevisionStamp propery and finally the table name and username are saved in TableName and Username properties respectively. Of course this entity is mapped to a table in database and the data is persisted in that table.

这样我们就可以拥有回滚功能(如果需要的话)。更改记录的主键保存在PK属性中。更改的日期保存在RevisionStamp propery中，最后表名和用户名分别保存在TableName和username属性中。当然，这个实体映射到数据库中的一个表，数据保存在这个表中。

If you are using entityframework you can override DbContext.SaveChanges() method and get all the ChangeTracker.Entries().Where(e=>e.State != EntityState.Unchanged) items and create your audit data using those items.

如果您正在使用entityframework，您可以重写DbContext.SaveChanges()方法，并获得所有的ChangeTracker.Entries(). where (e=>e)。状态!= entitystate .不变)项，并使用这些项创建审计数据。

#2

here you go for some help 1. http://www.4guysfromrolla.com/webtech/041807-1.shtml

这是给你的帮助。http://www.4guysfromrolla.com/webtech/041807 - 1. shtml

#1