Geek asks and answers

Auditing SQL Server Database Change Records

Using only Microsoft-based technologies (MS SQL Server, C #, EAB, etc.), if you need to keep track of changes made to records in the database, what strategy would you use? Triggers, AOP on DAL, Other? And how will you display the collected data? Is there a sample about this? Is there a tool or infrastructure that helps to implement such a solution?

+3
source share
3 answers

The problem with the Change Data record is that it is not flexible enough for a real audit. You cannot add the required columns. In addition, it by default deletes entries every three days (you can change this, but I don’t think you can keep it permanently), so you need to complete a task that writes entries to a real audit table if you need to save data for a long time the time that is typical of the need to audit records (we never reset our audit records).

. , , , , . , , , . , . - , , , , , .

. , , .

+2

Sql Server 2008 R2 Change Data Capture

+2

, , , .

I prefer stored procedures for all database entries. If an audit is required, it is directly in the stored procedure. There is no magic outside the code; everything that happens is documented right at the point where the recordings take place.

If the table needs to change in the future, you need to go to the stored procedure to make changes. The need to update the audit is recorded there. And since we used the stored procedure, to simplify the “version” of both the table and its audit table.

+1
source

More articles:


All Articles