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
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?