What is the best approach to audit the database for:
- Make a copy of the deleted and updated records.
- 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?
CodePudding user response：
Creating history tables and using triggers to save the DML is a common and effective approach for auditing a database. This approach allows you to track and record the changes made to the database, including who made the changes and when they were made. It also allows you to maintain a copy of deleted and updated records, which can be useful for auditing and debugging purposes.
One potential downside of this approach is that it can increase the complexity and size of the database, as you need to create additional tables and triggers to track the changes. Additionally, it can potentially impact the performance of the database, as the triggers need to be executed every time a change is made to the database.
In general, auditing the database is an important task that can help ensure the integrity and reliability of the data. The approach you choose will depend on the specific requirements and constraints of your application and database. It's always a good idea to consider the trade-offs and potential drawbacks of different approaches before implementing a solution.
CodePudding user response：
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.