In last week’s post, I gave you a trigger that populated a history table with all changes to the Users.AboutMe column. It was your job to write a T-SQL query that turned these Users_Changes audit table rows:

Into the full before & after data for each change, like the earlier query from the blog post:

The trigger will always log the “before” values into the Users_Changes table, but the “after” value could be in a few different places:

• If other changes have been made to that row, then the “after” value will be the next change for this Users.Id in the Users_Changes table
• If no other changes have been made to that row, then the “after” value is the current state of the row in the Users table
• If the row has since been DELETED, the “after” value isn’t anywhere!

To test your own code alongside mine, run these changes:

I didn’t explicitly call the DELETE scenario out in the challenge, and to be honest, I didn’t expect anyone to think of it when they were writing their answers. The post was all about catching mangled changes to the Users.AboutMe column, and the post’s trigger focused on updates of Users rows, not deletions. However, it’s still perfectly valid for someone to delete a row – and ideally, our query should be able to handle that situation. We didn’t get an answer that returned accurate data in that scenario, and I don’t blame y’all.

It’s an edge case – and it’s even harder of an edge case to handle than it might seem once you factor in NULLs. Let’s think about possible changes:

• The UPDATE might have set the row’s contents to NULL, in which case we want the AboutMe_After to show NULL.
• The UPDATE might have set the row’s contents to something, but then the row was subsequently deleted (and our trigger didn’t catch that) – in which case we want the AboutMe_After to reflect that we don’t know what the UPDATE did. NULL isn’t a valid answer here because it implies that the UPDATE set the AboutMe to NULL, which isn’t necessarily true. We want to show something like a “(Unknown, Row Has Been Deleted)” warning.

So that means we can’t just slap a COALESCE in there and call it a day. We’re gonna need some tricky CASE statements.

Here’s the query I came up with, and note that I’m including the different AboutMe_v2 and AboutMe_Current data just for diagnostic purposes so you can see how NULL handling is tricky:

Which, when auditing this series of changes:

Comes up with these results:

Notice how rows 2 & 3 both have nulls for the v2 and Current columns, but they have different results for AboutMe_After? That’s because the 2nd update statement actually did set AboutMe to NULL, and we do have a Users_Change row that knows about it (from Users_Change_Id = 3.)

In production, if I was faced with this problem, I’d probably want to change the trigger itself to log deleted rows as well as updated ones. However, I found this to be a really fun exercise because I was faced with this exact problem at a client – we were auditing past data whose changes had already been made, and I couldn’t build the historical data retroactively after the changes had been made.