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

Question

Hi all;

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?

thanks - dave

Answer 1

I seem to recall that this was up for discussion some months back and there was someone who had a nifty solution, but I cannot find anything right now.

In any case, what you can do is to add a bit column to_be_deleted which defaults to 0. When you are to delete a row, you first update the row to set this bit 1 and then you delete the row. Possibly, you could have a trigger on UPDATE which deletes the row if this bit is one. Yet a variation is to have INSTEAD OF trigger for DELETE, which first sets this bit and then deletes the row. In all these variations the version of the row with to_be_deleted = 1 will hold the user who deleted the row.