Is it good practice to only delete through a trigger to enforce 'deleted by' auditing?

Question

We've decided to design our database such that we actually delete rows when we want to delete some data, rather than just marking them as deleted, because the latter approach means that you always have to worry about filtering out the "marked as deleted" rows, which seems like an unnecessary headache.

However, we are auditing deleted rows in separate audit tables so as to keep the deleted data separately. We have a generic audit trigger that inserts all deleted columns (and their values) to the audit table on delete. We wish, though, to also audit the user ID of who deleted the row anytime a row is deleted. My idea is to have a deleted_by_user_id column on each table, and a trigger that blocks a delete if this column is not set. The transaction should fail and rollback if the "deleted by" user is not specified.

It would also automatically delete the row when it was set during an INSERT or UPDATE, meaning the row could only be deleted by setting this column. This would mean that any delete would necessarily include the user ID that deleted it as part of the audit. The account deleting the row will always be the service account for the web app in the database. It's our internal user IDs that we're interested in as to "who" is deleting the row, and that's what we want to capture.

Would this be good practice? What would be the pros and cons of using this method, and would it impact performance much compared to just using DELETE statements for row deletion?

Answer 1

I am not sure that using an IsDeleted column would create unnecessary headache. Locking down the table, and allowing non-administrative action via a view which filters on that column, while not displaying it, isn't very difficult. Add an Instead Of Delete trigger to the view, and you're all set.

Given that it's essentially boiler plate once you have the initial pattern, you could very easily write a procedure locks down the table and (re)generates an associated view, adding the filtering and trigger when it finds it has IsDeleted. It could add user logging requirements if there was a deleted_by_user_id field as well. I'd recommend parameter that allow rebuilding the world, or a pattern that is matched against table names (and/or schema).

In addition, versus forcing a deleted_by_user_id to be directly populated, I would look at leveraging SESSION_CONTEXT. The trigger would check against an agreed upon session context variable (e.g. 'AppUser'), throw if not set, then use that for populating deleted_by_user_id from that.

On the application side, you should be able to create sub-class SqlConnection and override Open. After calling the base Open, you should be able to run a EXEC sp_set_session_context N'AppUser', '{username}';, there by setting the App user for the session.

The advantage of using IsDeleted is that you don't have to write a whole row of data when doing a delete. If you do bulk deletes, or a lot of deletes in general, this could be advantageous. Though I would couple with Partial Index, cover just undeleted records. The advantage removing and writing to another table is that after reorganization you should have much more contiguous data, which in many cases generally makes things faster.