Use triggers to automatically record data changes

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 how to implement this function on the SQL Server database. My application environment is the C/S structure of winform, and 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] 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] indicates 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] records the value before data changes. It is important. To change it back to the previous value, this must be recorded.

[Newvalue] The new value after the data changes.

[RECORDER] records who perform operations. Hey, do you want to secretly raise your salary? Note: remember it in the background.
Iii. How to automatically record without human impact, reduce complexity of program design, and improve Universality

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 info 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],. name) as char (50) + ''', cast (D. ['+. name + '] As nvarchar), cast (I. ['+. name + '] As nvarchar), @ recorder from # d, # I where I. mid = D. mid and I. ['+. name + '] <> D. ['+. name + ']'
From syscolumns a left join SYS. extended_properties B on A. ID = B. major_id and A. colid = B. minor_id
Where. id = object_id ('Employee') and (substring (columns_updated (), (. colid-1)/8 +) & Power (2, (. colid-1) % 8) = power (2, (. colid-1) % 8) and. name not in ('referes', '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
The above procedures were completed earlier. At that time, I took a look at the work of senior masters (which of the following heroes can't be remembered), and I would like to express my gratitude to senior elders.