Insus.net solves this problem by creating another table that stores the tables that the user decides to track, and the fields in the table that need to be tracked.
You also create another table [Audit], which is the table that stores the trace records:
Copy Code code 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) is 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_l Ocks = on, allow_page_locks = on) on [PRIMARY]
) on [PRIMARY]
Go
SET ansi_padding off
Go
ALT ER 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
Resolves who updates the data by using this method: Get the user who is logged in to the program in a SQL trigger or stored procedure
Next, write an UPDATE trigger trigger for the trace table.
An exception invalid object name ' inserted ' or invalid object name ' deleted ' to access the internal temporary touch of inserted or deleted in a trigger can be referred to in this article:
EXEC function accesses inserted or deleted's internal temporary touch publication
The following is a table Update trigger (section) with comments:
Copy Code code as follows:
--@n and @o Two variables, one for storing updated data values, one for existing data values
DECLARE @sql NVARCHAR (MAX), @n decimal (18,0), @O decimal (18,0)
The--@i variable is the field that the user needs to track
SET @sql = N ' SELECT @n = [' + CONVERT (NVARCHAR (MAX), @i) + '] from #inserted '
-Executes the dynamic SQL statement.
EXECUTE sp_executesql @sql,
N ' @n DECIMAL (18,0) OUTPUT ',
@n OUTPUT;
--The following SQL code, which retrieves 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 (18,0) OUTPUT ',
@O OUTPUT;
--Compare two data values, update the value and the original value, if not the same, 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 above code also has a stored procedure, because if more than one table or a table has updates or deletes need to insert the tracking data into the audit log table, in order to better maintain and code redundancy, so insert the audit log table process, write a stored procedure:
Copy Code code 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)
