通过触发器审计SQL Server手动变更与EF Core 6应用变更

要同时覆盖EF Core 6应用发起的变更和SSMS手动SQL操作的审计，需要结合EF Core拦截器和SQL Server触发器两种方案，以下是具体实现步骤：

一、捕获EF Core 6应用的数据变更 #

利用EF Core的拦截器机制，在SaveChanges前后自动捕获实体变更，无需修改业务代码。

1. 定义UserAudit实体
   先创建审计表对应的实体类，包含核心审计字段：

public class UserAudit
{
    public int AuditId { get; set; }
    public string TableName { get; set; }
    public string OperationType { get; set; } // INSERT/UPDATE/DELETE
    public string ChangedBy { get; set; }
    public DateTime ChangedAt { get; set; }
    public string OldValues { get; set; } // 旧值JSON
    public string NewValues { get; set; } // 新值JSON
}

在你的DbContext中添加DbSet<UserAudit>，并通过迁移生成数据库表。

2. 实现审计拦截器
   继承SaveChangesInterceptor，重写方法捕获变更并写入UserAudit：

public class AuditInterceptor : SaveChangesInterceptor
{
    private readonly IHttpContextAccessor _httpContextAccessor;

    public AuditInterceptor(IHttpContextAccessor httpContextAccessor)
    {
        _httpContextAccessor = httpContextAccessor;
    }

    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData, 
        InterceptionResult<int> result, 
        CancellationToken cancellationToken = default)
    {
        var context = eventData.Context;
        if (context == null) return base.SavingChangesAsync(eventData, result, cancellationToken);

        var currentUser = _httpContextAccessor.HttpContext?.User.Identity?.Name ?? "EF_Core_App";
        var now = DateTime.UtcNow;

        // 捕获新增实体
        foreach (var entry in context.ChangeTracker.Entries().Where(e => e.State == EntityState.Added))
        {
            context.Set<UserAudit>().Add(new UserAudit
            {
                TableName = entry.Metadata.GetTableName(),
                OperationType = "INSERT",
                ChangedBy = currentUser,
                ChangedAt = now,
                NewValues = JsonSerializer.Serialize(entry.CurrentValues.ToObject())
            });
        }

        // 捕获修改实体
        foreach (var entry in context.ChangeTracker.Entries().Where(e => e.State == EntityState.Modified))
        {
            context.Set<UserAudit>().Add(new UserAudit
            {
                TableName = entry.Metadata.GetTableName(),
                OperationType = "UPDATE",
                ChangedBy = currentUser,
                ChangedAt = now,
                OldValues = JsonSerializer.Serialize(entry.OriginalValues.ToObject()),
                NewValues = JsonSerializer.Serialize(entry.CurrentValues.ToObject())
            });
        }

        // 捕获删除实体
        foreach (var entry in context.ChangeTracker.Entries().Where(e => e.State == EntityState.Deleted))
        {
            context.Set<UserAudit>().Add(new UserAudit
            {
                TableName = entry.Metadata.GetTableName(),
                OperationType = "DELETE",
                ChangedBy = currentUser,
                ChangedAt = now,
                OldValues = JsonSerializer.Serialize(entry.OriginalValues.ToObject())
            });
        }

        return base.SavingChangesAsync(eventData, result, cancellationToken);
    }

    // 同步方法逻辑与异步一致，重写SaveChanges即可
    public override InterceptionResult<int> SavingChanges(DbContextEventData eventData, InterceptionResult<int> result)
    {
        // 复制异步版核心逻辑，省略重复代码
        return base.SavingChanges(eventData, result);
    }
}

3. 注册拦截器
   在Program.cs中注册拦截器和IHttpContextAccessor（用于获取当前业务用户）：

builder.Services.AddHttpContextAccessor();
builder.Services.AddDbContext<YourDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("YourConnString"))
           .AddInterceptors(new AuditInterceptor(builder.Services.BuildServiceProvider().GetRequiredService<IHttpContextAccessor>())));

二、捕获SSMS手动执行SQL的变更 #

直接执行SQL脚本绕开了EF Core，需要在数据库层面用触发器拦截变更。

以业务表Users为例，创建对应的INSERT/UPDATE/DELETE触发器：

-- 新增操作审计触发器
CREATE TRIGGER trg_Users_Insert_Audit
ON Users
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO UserAudit (TableName, OperationType, ChangedBy, ChangedAt, NewValues)
    SELECT 
        'Users',
        'INSERT',
        SUSER_SNAME(), -- 获取执行SQL的数据库登录名
        GETUTCDATE(),
        (SELECT * FROM inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) -- 转为JSON存储
END
GO

-- 更新操作审计触发器
CREATE TRIGGER trg_Users_Update_Audit
ON Users
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO UserAudit (TableName, OperationType, ChangedBy, ChangedAt, OldValues, NewValues)
    SELECT 
        'Users',
        'UPDATE',
        SUSER_SNAME(),
        GETUTCDATE(),
        (SELECT * FROM deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        (SELECT * FROM inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
END
GO

-- 删除操作审计触发器
CREATE TRIGGER trg_Users_Delete_Audit
ON Users
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO UserAudit (TableName, OperationType, ChangedBy, ChangedAt, OldValues)
    SELECT 
        'Users',
        'DELETE',
        SUSER_SNAME(),
        GETUTCDATE(),
        (SELECT * FROM deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
END
GO

如果有多个业务表需要审计，为每个表复制上述触发器并修改表名即可。

三、关键注意事项 #

• 格式统一：EF Core和触发器都用JSON存储变更值、UTC时间，确保审计记录格式一致，方便后续查询分析。
• 避免循环触发：不要对UserAudit表本身创建触发器，防止无限循环。
• 性能考量：高频写操作的表，触发器可能影响性能，可考虑用SQL Server Service Broker实现异步审计（复杂度较高）。
• 用户区分：EF Core记录业务用户名，触发器记录数据库登录名，可清晰区分是应用操作还是手动操作。

内容的提问来源于stack exchange，提问作者Raziel