Delete trigger for audit table

2
votes

I'm working on a web application that executes CRUD operations on some table in a SQL Server database. There will be a logged user in the application executing these operations.

(By the way, I'm using Entity Framework)

Let's say table is

MyTable

  • MyTableId
  • SomeColumn
  • LastModifiedUserId
  • LastModifiedDate

And I a have an audit table like

MyTableHistory

  • MyTableHistoryId
  • MyTableId
  • SomeColumn
  • ActionType --ins/upd/del
  • ActionUserId
  • ActioDate

And I'm using triggers to insert data on the audit table.

Inserts and updates are easy by consulting the Inserted and Updated tables to find the userid who modified the record.

But what about deletes? Any idea how I might get that info?

2
sql-servertriggersaudit
there is the INSERTED pseudo table, as you said, but not an UPDATED one. There's the DELETED pseudo table too – Lamak
But DELETED will simply have the LastModifiedUserId that last updated the record, not the one who deleted the record. That's the one I want – DJPB
suser_sname() is the account name who run the query. – Alex Kudryashev
suser_sname() will only return the login for the whatever account connected to SQL Server. If it is being used by a web service, it won't identify the end user. – yelxe

2 Answers

0
votes

There is no such thing as updated table. The two pseudo tables available in DML triggers are inserted and deleted. In the case of insert table deleted is empty, in the case of delete table inserted is empty, in the case of update both tables are populated.
You can create three separate triggers for each action (to distinguish ActionType) or try to combine all in one trigger.
Note: take into account multiple row actions.

0
votes

Assuming you are using EF to add the information about which user is updating records, the easiest way to capture that information is to have EF perform a 2-step process (UPDATE, DELETE) on the data you wish to delete. You will then need to interpret the two audit rows as part of the same operation.

There is a much more involved solution that "might" work, but I have not tested it. There is more information available below if you wish to explore it.

Another option altogether is to abandon trigger auditing (which is problematic for this very reason) and use Entity Framework instead. Below is an example of how one might accomplish this by overriding the SaveChanges method:

public virtual IEnumerable<System.Data.Entity.Infrastructure.DbEntityEntry> ChangedEntries()
{
    return ChangeTracker.Entries().Where(x =>
        x.State == EntityState.Added ||
        x.State == EntityState.Deleted ||
        x.State == EntityState.Modified);
}

public virtual int SaveChanges(string userName)
{
    var changes = ChangedEntries();

    foreach (var entry in changes)
    {
        var eventType = entry.State == EntityState.Added ? "A" : entry.State == EntityState.Deleted ? "D" : "U";
        var entityType = ObjectContext.GetObjectType(entry.Entity.GetType()).Name;

        var oldValues = entry.State == EntityState.Added ? null : JsonConvert.SerializeObject(entry.OriginalValues.ToObject());
        var newValues = entry.State == EntityState.Deleted ? null : JsonConvert.SerializeObject(entry.CurrentValues.ToObject());

        oldValues = oldValues?.Substring(0, Math.Min(oldValues.Length, 4000));
        newValues = newValues?.Substring(0, Math.Min(newValues.Length, 4000));

        AuditItems.Add(
            new AuditItem
            {
                EventTime = DateTime.Now,
                UserName = userName,
                EntityType = entityType,
                EventType = eventType,
                OldValues = oldValues,
                NewValues = newValues
            }
        );
    }

    return base.SaveChanges();
}