Auditing with ASP.NET and SQL Server

Question

What is the best approach to audit the database for:

1. Make a copy of the deleted and updated records.
2. Save the date and user ID of the ones who did the DML which is (the ID) was saved on a session in ASP.NET

My manager told me to add 3 extra columns to each table one for the ID of whom updated the record, one for whom deleted the record and the third has a boolean value 0 for deleted and 1 for active (not deleted yet), but I think this is a work around and I'm pretty sure there is a better way to do it .

I was thinking of creating history tables and write an AFTER DELETE trigger that saves all the DML.

Is this it or is there a more plain forward way?

Answer 1

SQL Server 2016 onwards you can do this using Temporal tables:

A system-versioned temporal table is a type of user table designed to keep a full history of data changes, allowing easy point-in-time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (that is, the database engine).

If what you are really trying to do is to record who changed what, a better approach is to use roles and groups to prevent users altering anything they shouldn't.