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

David Thielen 1,761 Reputation points
2023-07-28T14:54:27.87+00:00

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

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,257 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 98,811 Reputation points
    2023-07-30T19:29:09.7+00:00

    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.


0 additional answers

Sort by: Most helpful