Use triggers to automatically record data changes

I, Demand generation

Data-based enterprise applications sometimes need to record changes in sensitive data. For example, the personal information of employees must be accurate and secure, and cannot be changed at will. Each change requires a detailed record of the modification time and the content of the fields before and after modification, operator. Next let's take a look at howSQL ServerDatabase. My application environment isWinformOfC/SStructure. No other environments are tested.
II,How to effectively store information generated by data changes

If you create another data table with the same structure as the original data table to save data changes, this is obviously not a good idea. The data redundancy is large, which is not conducive to future viewing and cannot record additional information. We can design a general data table structure to save the data change information:

Create Table[DBO].[Datarecorder](

[ID] [int]Identity(1,1) Not Null,

[Tablename] [nvarchar](50) Not Null,

[KeyValue] [varchar](50) Not Null,

[Fieldname] [nvarchar](50) Not Null,

[Oldvalue] [nvarchar](500) Null,

[Newvalue] [nvarchar](500) Null,

[Recorddate] [smalldatetime]Not Null Constraint[Df_liiinsuinfo_recorddate]Default (Getdate()),

[RECORDER] [nvarchar](20) Not Null)

Field description:

[Tablename]It refers to the name of a data table with data changes. Since it is a common table, it is necessary to distinguish which table is recorded.

[KeyValue]It refers to the key field value in a data table with data changes. It is used to know which record is recorded.

[Fieldname]Indicates which field has data changes.

[Oldvalue]It is important to record the value before data changes. If you want to change it back to the previous value, this must be recorded.

[Newvalue]Record the new value after the data changes.

[RECORDER]Record who performed the operation. Hey, do you want to secretly raise your salary? Note: remember it in the background.
III,How to automatically record and reduce without human impactProgramDesign complexity to improve versatility

Since automatic recording, the first thing that comes to mind is the use of triggers.

The first difficulty is how to record operators. Based on previous practices, to create a user login data table (userlogin), you only need three fields to record the username and hostname of the login machine) and logintime ). it is assumed that the data operation is performed by the user who recently logged on to the same PC.

The second problem is the field name. We sometimes need an intuitive field name. If the abbreviated field name is directly given, it may cause confusion. here we can use the description in the column attribute to solve this problem, when creating a data table, specify an intuitive and meaningful name in the description of each column attribute. You only need to extract this description to save the meaningful field name.

The third problem is that some fields do not need to be recorded, such as some large descriptive fields with more content. It must be excluded from the program.

Okay. Let's take a look at the main parts of the trigger:

Create Trigger [DBO]. [tr_update]
On
After update
As
Begin
Set nocount on;
Select mid = Identity ( Int , 1 , 1 ), * Into # I from inserted
If @ rowcount = 0 Return
Select mid = Identity ( Int , 1 , 1 ), * Into # D from deleted

Declare @ tablename nvarchar ( 10 ), @ Recorder nvarchar ( 20 ), @ S nvarchar ( 4000 )

Select top 1 @ Tablename = ' Employee information table ' , @ Recorder = Username from userlogin
Where hostname = Host_name () order by logintime DESC

Declare # TB cursor local for select
' Insert datarecorder (tablename, keyValue, fieldname, oldvalue, newvalue, recorder) Select @ tablename, cast (I. employeeid as nvarchar ), '''
+ Cast (isnull (B. [value], A. Name) Char ( 50 )) + ''' , Cast (D .[ ' + A. Name + ' ] As nvarchar), cast (I .[ ' + A. Name + ' ] As nvarchar), @ recorder from # d, # I where I. Mid = D. Mid and I .[ '   + A. Name + ' ] <> D .[ ' + A. Name + ' ] '  
From syscolumns a left join SYS. extended_properties B on A. ID = B. major_id and A. colid = B. minor_id
Where a. ID = Object_id ( ' Employee ' ) And (substring (columns_updated (), (A. colid - 1 ) / 8 + 1 , 1 ) & Power ( 2 , (A. colid - 1 ) % 8 )) = Power ( 2 , (A. colid - 1 ) % 8 ) And A. name not in ( ' Remarks ' , ' Address ' )
Order by A. colid

Open # TB
Fetch # TB into @ s
While @ fetch_status = 0
Begin
Exec sp_executesql @ s, n ' @ Tablename nvarchar (10), @ recorder nvarchar (20) ' , @ Tablename, @ Recorder
Fetch # TB into @ s
End
Close # TB
Deallocate # TB
End