Read "a complete solution for tracking data changes"

The original Article "a complete solution for tracking data changes" by artech is as follows.

Address: http://www.cnblogs.com/artech/archive/2010/11/29/audittrail.html

A complete solution for tracking data changes

In a previous article about CDC, I said that audit trail (or audit log) is an indispensable feature for most enterprise applications. This article provides you with a complete audit trail solution that not only records the information of each business operation (such as the operation time and operator ), in addition, you can track data changes caused by each business (if necessary ).

Directory
I. Data Table Design
2. Representation of data changes
Iii. Writing basic information of auditlog
4. Use sqlcdc to track data changes in the source table
5. How is the transactionid of the delete operation recorded?
Vi. Dumping auditlog details through SQL job
VII,CodeGenerated Application

I. Data Table Design

In the database, we store information about the auditlog through two tables with a primary-child relationship, as shown in the right figure. We use "Transactions" as the unit for tracking, but the "Transactions" mentioned here refer more to the concept of business processing transactions. Each tracked transaction has a matching record in the auditlog table, which indicates the basic information of the transaction: username (operator), audittime (Operation Time) activity (which can be regarded as the name of a transaction) and description (complementary description of a transaction ). The primary key transactionid uniquely identifies a transaction.

The sub-Table auditlogdata records the detailed information of the transaction, that is, the data changes caused by the transaction. A complete business logic usually involves operations on multiple data tables and multiple records. Each record of auditlogdata indicates the data changes of a transaction against a single data table, while the sourcetable field indicates the name of the source table. The datachange field represents data changes in XML format. It has the following format.

2. Representation of data changes

Data operation types are nothing more than adding, updating, and deleting data. We use different XML structures to indicate data changes caused by different operations. Specifically, for the add operation, we need to record the inserted records; For the delete operation, we need to record the original records; for the data update, you must record the updated records at the same time.

For example, assume that we have an users table with three basic fields: ID, name, and birthday. The XML below respectively indicates the data changes that need to be recorded after the add, delete, and update operations.

Add:

  <? XML version = "1.0" encoding = "UTF-8" ?>
  < CDC Operation = "Insert" >
< Current >
< ID Type = "Varchar (50 )" > 001 </ ID >
< Name Type = "Nvarchar (50 )" > Foo </ Name >
< Birthday Type = "Date" > 1981-08-10 </ Birthday >
</ Current >
  </ CDC >

 

Delete:

  <? XML version = "1.0" encoding = "UTF-8" ?>
  < CDC Operation = "Delete" >
< Current >
< ID Type = "Varchar (50 )" > 001 </ ID >
< Name Type = "Nvarchar (50 )" > Foo </ Name >
< Birthday Type = "Date" > 1981-08-10 </ Birthday >
</ Current >
  </ CDC >

Update:

  <? XML version = "1.0" encoding = "UTF-8" ?>
  < CDC Operation = "Update" >
< Original >
< ID Type = "Varchar (50 )" > 001 </ ID >
< Name Type = "Nvarchar (50 )" > Foo </ Name >  
< Birthday Type = "Date" > 1981-08-24 </ Birthday >
</ Original >
< Current >
< ID Type = "Varchar (50 )" > 001 </ ID >
< Name Type = "Nvarchar (50 )" > Bar </ Name >
< Birthday Type = "Date" > 1982-07-10 </ Birthday >
</ Current >
  </ CDC >

Of course, you can also customize the XML structure as needed.

Iii. Writing basic information of auditlog

Our goal now is to write the tracked transaction-related information to the two tables we created above. The information of the master table auditlog is easily written. For example, you can define the same auditlogger class as below.

Public   Class Auditlogger
{
Public   Void Write ( String Activity)
{}

Public VoidWrite (StringActivity,StringDescription)
{}
}

 

The write method of auditlogger passes in the activity and description, without transactionid, username, and audittime. Here, audittime is the current time, and username should be the user logging on to the system. For transactionid, we should use the context method to obtain it. The specific reason will be discussed below. If you directly use the system. Transactions transaction to implement the "transaction" We track, you can directly use the distributedidentifier or localidentifier of the current transaction (transaction. Current.

How can we record transaction changes caused by transactions to the auditlogdata table? This work is fully implemented in SQL Server.

4. Use sqlcdc to track data changes in the source table

Sqlcdc, a powerful tool for tracking changes in each business operation data, describes how to effectively record data changes based on a specific data table: sqlcdc, here we use it to record the detailed information of auditlog. After the CDC feature is enabled for a table (such as users), SQL Server creates a corresponding CT table (users_ct) for it ), by default, users_ct contains all fields in the users table. If you do not want CDC to track all fields, you can explicitly set specific fields.

In the auditlogdata table, a field transactionid indicates the specific transaction to which the record belongs. To enable the CDC to record the correct transactionid, you need to add such an additional field to each tracked table. This should not be a problem. For example, each table has six system fields: transactionid, versionno, createdby, createdtime, latestupdatedby, and latestupdatedtime.

Because each data table has a transactionid field, you must assign a value to the ID of the current transaction when submitting data, this is why I recommend that you use context to obtain the current transactionid. However, there is another problem that cannot be solved-how can the transactionid of the data deletion operation be recorded?

5. How is the transactionid of the delete operation recorded?

Because the transactionid representing the current transaction will eventually write the data table through the insert or update SQL statement, what about the delete operation? Because we directly call the delete statement to perform corresponding data operations, it indicates that the transaction where the current delete operation is located cannot be written, the data recorded by the CDC does not reflect the transaction to which the deleted record belongs.

The database operations are submitted through SQL, stored procedures, or SQL text. To solve this problem, we only need to change our SQL script and execute the update statement before executing the delete statement to write the new transactionid.

That is to say, for a delete operation, update and delete are performed first. In this case, CDC records three records for you, the first two are for the update record, and the last one is for the delete record. To distinguish whether the records tracked by the CDC are normal update or delete update, we can make some marks. For example, you can add a prefix before the value of transactionid to indicate that the update operation is performed for Delete.

Vi. Dumping auditlog details through SQL job

CDC only records data changes based on a specific table to the CT table based on this table. In the end, we need to transfer the data in these CT tables to our specified auditlogdata table, this can be achieved through sqljob. You can create an SQL job to transfer data from several CT tables to auditlogdata, and configure the job execution time or interval based on your needs (mainly real-time requirements. The picture on the right shows how the detailed information of auditlog is recorded step by step.

VII. Application of code generation

In this solution, we need an indispensable thing: code generator. It is used to automatically generate the following SQL Script: To enable the CDC feature for a table and specify the T-SQL script for the tracing field, and to perform auditlog details (clustered table to auditlogdata table) SQL job script. For more information about code generation, see several code generation solutions integrated with vs.

 

 

The key issue of this solution is how to match user information with data in CDC. At present, the CDC records the changes of the table, but does not know who or when the changes were made.

There are two clever points in this solution:

1) use XML to record user and other information

2) Put transactionid in the table to be tracked. In this way, the data in the CDC will also have transactionid. Then, the data in the other record table can also be associated with the data in the CDC.

Of course, there is also a new question about how to delete associations. In this solution, insert the file first and then delete it. In this case, I think the design is inappropriate. In fact, we can judge whether the last record in the CDC is deleted or not, and then retrieve the last record from another record. And combines them into a complete Delete record.

For Table modification records, you can directly use data records in CDC.