SQL Server temporal tables - storing the user who deleted a row

169 Views Asked by At

I am going to use temporal tables to keep a full audit trail of all changes to records in my key tables. One thing I have already implemented is each of those key record models implements:

public interface IAuditTrail
{
    /// <summary>
    /// The IdentityUser.Id (AppUser.AspNetUserId) of the user who updated (or created) this record.
    /// </summary>
    public string? UpdaterUserId { get; set; }

    /// <summary>
    /// The IP address of the user who updated (or created) this record.
    /// </summary>
    public string? UpdaterIpAddress { get; set; }
}

And just before calling SaveChangesAsync() I first call (method added to my DbContext class):

public void SetAuditFields(IAuditTrail? primaryRecord, string? userId, string? userIpAddress)
{

    // if we have a primary, set it
    if (primaryRecord != null)
    {
        primaryRecord.UpdaterUserId = userId;
        primaryRecord.UpdaterIpAddress = userIpAddress;
    }

    var entries = ChangeTracker.Entries();
    foreach (var entry in entries)
    {
        if (entry.State != EntityState.Added && entry.State != EntityState.Modified)
            continue;
        if (entry.Entity is not IAuditTrail entity) 
            continue;
        entity.UpdaterUserId = userId;
        entity.UpdaterIpAddress = userIpAddress;
    }
}

This means for the INSERT and every UPDATE I have the logged in user who performed the action and the IP address they performed it from. This part is great.

But for a DELETE I don't have this. Is there a way with temporal tables to have a final row written that will have the UpdaterUserId and UpdaterIpAddress of the individual doing the delete? Is there some call I can make to add a row to the temporal table for this?

0

There are 0 best solutions below