Judge whether the field is updated and new data is written into the Audit Log table.

To solve this problem, Insus. NET only creates another table, which stores the table to be tracked and the fields to be tracked in the table.
Create another table [Audit], that is, the table storing the tracking records:
Copy codeThe Code is as follows:
Audit
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
Create table [dbo]. [Audit] (
[Audit_nbr] [int] IDENTITY (1, 1) not null,
[AuditType] [char] (1) not null,
[TableName] [nvarchar] (128) not null,
[FieldName] [nvarchar] (128) NULL,
[OldValue] [nvarchar] (4000) NULL,
[NewValue] [nvarchar] (4000) NULL,
[UserName] [nvarchar] (128) NULL,
[CreateDate] [datetime] not null,
PRIMARY KEY CLUSTERED
(
[Audit_nbr] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Alter table [dbo]. [Audit] with check add check ([AuditType] = 'D' OR [AuditType] = 'U' OR [AuditType] = 'I '))
GO
Alter table [dbo]. [Audit] add default (getdate () FOR [CreateDate]
GO


This method is used to solve the problem of who updates the data: Obtain the user logged on to the program in the SQL trigger or stored procedure.
Next, write an update Trigger for the trail table.
When you access the internal temporary trigger table of INSERTED or DELETED in a trigger, an exception invalid object name 'inserted' or invalid object name 'deleted' is returned. To solve this problem, refer to this article:
The EXEC (EXECUTE) function accesses the internal temporary trigger table of INSERTED or DELETED.
The following is a table update trigger (partial) with annotations:
Copy codeThe Code is as follows:
-- @ N and @ O are two variables. One is to store the updated data value and the other is the original data value.
DECLARE @ SQL NVARCHAR (MAX), @ N DECIMAL (18,0), @ O DECIMAL (18,0)
-- @ I variable is the field to be tracked by the user
SET @ SQL = n' SELECT @ N = ['+ CONVERT (NVARCHAR (MAX), @ I) +'] FROM # inserted'
-- Execute dynamic SQL statements.
EXECUTE sp_executesql @ SQL,
N' @ n decimal () output ',
@ N output;
-- The following SQL code obtains the original data value from the deleted table.
SET @ SQL = n' SELECT @ O = ['+ CONVERT (NVARCHAR (MAX), @ I) +'] FROM # deleted'
EXECUTE sp_executesql @ SQL,
N' @ o decimal () output ',
@ O output;
-- Compare two data values. If the updated value is different from the original value, insert the data into the Audit Log table.
IF (ISNULL (@ N, 0) <> ISNULL (@ O, 0 ))
EXECUTE [dbo]. [usp_Audit_Insert] 'U', '<tableName>', '<fieldName>', @ O, @ N, @ UserName


The code above also has a stored procedure, because if multiple tables or tables are updated or deleted, You need to insert the tracked data into the Audit Log table, in order to better maintain and code redundancy, therefore, the process of inserting the Audit Log table is written as a stored procedure:
Copy codeThe Code is as follows:
Usp_Audit_Insert
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter procedure [dbo]. [usp_Audit_Insert]
(
@ AuditType [char] (1 ),
@ TableName [nvarchar] (128 ),
@ FieldName [nvarchar] (128 ),
@ OldValue [nvarchar] (4000 ),
@ NewValue [nvarchar] (4000 ),
@ UserName [nvarchar] (128)
)
AS
Insert into [dbo]. [Audit]
([AuditType], [TableName], [FieldName], [OldValue], [NewValue], [UserName])
VALUES
(@ AuditType, @ TableName, @ FieldName, @ OldValue, @ NewValue, @ UserName)