触发器写数据库操作日志

在实际业务中，经常需要 记录某条数据在更新时，具体哪些字段被修改、修改前后的值是什么。
SQL Server 提供了 COLUMNS_UPDATED() 方法，但它返回的是 位掩码（varbinary），需要对其解析才能得到被更新的列。

本文提供以下完整方案：

创建一个函数用于解析 COLUMNS_UPDATED() 的位掩码
创建触发器，自动捕获 Insert / Update
创建存储过程，将修改前后内容写入日志表

你可将本文内容直接复制到你的项目或 WordPress。

一、解析 COLUMNS_UPDATED()：创建 GetColumnOrderList 函数

该函数放到 master 库，方便在任意数据库调用。

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GetColumnOrderList]
(
    @b varbinary(max)
)
RETURNS 
@t TABLE 
(
    _col int
)
AS
BEGIN
    declare @i int,@v int,@l int
    set @i = 0
    while @i < datalength(@b)
        begin
            set @i = @i + 1
            set @l = 0
            set @v = convert(int,substring(@b,@i,1))
            while @v > 0
                begin
                    if @v % 2 = 1
                        begin
                            insert into @t
                            select @l + @i * 8 - 7
                        end
                    set @l = @l + 1
                    set @v = @v / 2
                end
        end    
    RETURN 
END

该函数会返回被更新列的 colorder 列序号。

二、创建触发器：捕获 Insert 和 Update

创建在目标表上，例如：Tecul_CustomerBaseInfo。

Create trigger [dbo].[Tecul_CustomerBaseInfo_I_U_trig]
on [dbo].[Tecul_CustomerBaseInfo] 
after update, insert 
as 
Begin 
	declare @stuCount int, @User varchar(200), @Id varchar(200), @Methods varchar(10) = 'U';
	declare @tb int, @db int, @cu varbinary(max), @pk varchar(50);
	
	select 
		@pk = 'Id', 
		@db = db_id(), 
		@tb = (select parent_obj from sysobjects where id = @@PROCID), 
		@cu = COLUMNS_UPDATED()

	IF EXISTS(select * from tempdb..sysobjects where id in (object_id('tempdb..##ins'), object_id('tempdb..##del'))) 
	BEGIN 
		DROP TABLE ##ins 
		DROP TABLE ##del 
	END

	SELECT * INTO ##ins FROM inserted
	SELECT * INTO ##del FROM deleted

	IF NOT EXISTS(SELECT 1 FROM deleted) 
	BEGIN 
		SET @Methods = 'I'
		INSERT INTO ##del(Id) select Id from ##ins 
	END 

	EXEC pro_I_GlobalLogs 'Tecul_CustomerBaseInfo', @tb, @db, @cu, @pk, @Methods, ''
END

三、创建日志记录存储过程

用于生成 XML、对比字段、生成 JSON、写入日志表 Tecul_OperationLogs。

ALTER PROCEDURE [dbo].[pro_I_GlobalLogs]
	@TableNameMaster varchar(100),    
	@tb int,
	@db int,
	@cu varbinary(MAX),
	@pk varchar(50),
	@Methods varchar(10),
	@TableNameSublist varchar(100)
AS
BEGIN
	declare @sql nvarchar(max),@UserCode nvarchar(50),@empid nvarchar(50),@empid1 INT,@id nvarchar(50),
	@UserName nvarchar(50),@UpdateAfter AS NVARCHAR(MAX),
	@UpdateBefore AS NVARCHAR(MAX),@LineId  NVARCHAR(200)

    CREATE TABLE #temp
    (
        id int ,pkval  varchar(50),col varchar(50),ins varchar(50),del varchar(50)	
    )
	   
    CREATE TABLE #temp1
    (
        LastUpdater nvarchar(50)
    )

	SET @sql = 'declare @ins xml,@del xml,@handle int,@prepare int;'
	
	SET @sql = @sql + 'select @ins = (select ' + @pk + 
		(select ',' + name 
			from master.dbo.GetColumnOrderList(@cu) a 
			left join syscolumns b on a._col=b.colorder 
			where 
				id=@tb and name<>@pk 
				AND name!=''LastUpdater'' 
				AND name!=''LastUpdateDate'' 
				AND name!=''StateId''   
				and name<>''ScopeAuthOrganizationId'' 
				and name<>''CreateType'' 
				and name<>''LockType'' 
				and name<>''Creater'' 
				and name<>''CreateDate''
		for xml path('')) 
	+ ' from ##ins for xml raw,root(''ins''),type,elements XSINIL);'
	
	SET @sql = @sql + 'select @del = (select ' + @pk + 
		(select ',' + name 
			from master.dbo.GetColumnOrderList(@cu) a 
			left join syscolumns b on a._col=b.colorder 
			where 
				id=@tb and name<>@pk 
				AND name!=''LastUpdater'' 
				AND name!=''LastUpdateDate'' 
				AND name!=''StateId''   
				and name<>''ScopeAuthOrganizationId'' 
				and name<>''CreateType'' 
				and name<>''LockType'' 
				and name<>''Creater'' 
				and name<>''CreateDate''
		for xml path('')) 
	+ ' from ##del for xml raw,root(''del''),type,elements XSINIL);'

	SET @sql = @sql + 'exec @prepare = sp_xml_preparedocument @handle output,@ins;select * into #tb_ins from openxml(@handle,''/ins'',1);'
	SET @sql = @sql + 'exec @prepare = sp_xml_preparedocument @handle output,@del;select * into #tb_del from openxml(@handle,''/del'',1);'

	SET @sql = @sql + 'with nd as (select id from #tb_ins where nodetype=1 and parentid=0)
	select row_number() over(order by a.nodetype,a.parentid,a.id) as rowid,a.id,a.localname as col,
	convert(nvarchar(max),b.text) as text,a.parentid into #_ins 
	from #tb_ins a 
	left join #tb_ins b on b.parentid=a.id 
	where a.parentid in (select id from nd);'

	SET @sql = @sql + 'with nd as (select id from #tb_del where nodetype=1 and parentid=0)
	select row_number() over(order by a.nodetype,a.parentid,a.id) as rowid,a.id,a.localname as col,
	convert(nvarchar(max),b.text) as text into #_del 
	from #tb_del a 
	left join #tb_del b on b.parentid=a.id 
	where a.parentid in (select id from nd);'

	SET @sql = @sql + 'select a.id,val.text as pkval,a.col,a.text as ins,b.text as del 
	from #_ins a 
	inner join #_del b on a.rowid=b.rowid 
	and (a.text<>b.text or (case when a.text is null then 1 else 0 end)<>(case when b.text is null then 1 else 0 end)) 
	left join #tb_ins pk on a.parentid=pk.parentid and pk.localname=''' + @pk + ''' 
	left join #tb_ins val on pk.id=val.parentid;'		

	INSERT INTO #temp exec sp_executesql @sql

	SET @id=(SELECT TOP 1 Id FROM ##ins);

	IF(@TableNameSublist!='')
	BEGIN
		SET @sql='SELECT top 1 T2.LastUpdater FROM '+@TableNameMaster+' T1 INNER JOIN '+@TableNameSublist+' T2 ON T1.id=t2.id WHERE T1.id='''+@id+'''';
		INSERT INTO #temp1 exec sp_executesql @sql ;
		SET @id=(SELECT * FROM #temp1)

		SELECT  @UserCode=ItemCode,@UserName=ItemName FROM Tecul_Employee WHERE Id=''+@id+''
	END
	ELSE
	BEGIN
		SET @sql='SELECT top 1 LastUpdater FROM  '+@TableNameMaster+' WHERE id='''+@id+'''';
		INSERT INTO #temp1 exec sp_executesql @sql;

		SET @id=(SELECT * FROM #temp1)
		SELECT  @UserCode=ItemCode,@UserName=ItemName FROM Tecul_Employee WHERE Id=''+@id+''
	END

    DECLARE C_Employees CURSOR FAST_FORWARD FOR
    SELECT pkval FROM #temp GROUP BY pkval;
 
    OPEN C_Employees;
    FETCH NEXT FROM C_Employees INTO @empid;

    WHILE @@FETCH_STATUS=0
		BEGIN 
			SET @UpdateAfter='{'
			SET @UpdateBefore='{'

			DECLARE C_Employees2 CURSOR  LOCAL FOR
			SELECT id FROM  #temp WHERE pkval=@empid; 

		    OPEN C_Employees2;		
		    FETCH NEXT FROM C_Employees2 INTO @empid1;

			WHILE @@FETCH_STATUS=0
				BEGIN
					SELECT  
						@UpdateAfter+='"'+col+ '":"'+ ISNULL(ins,'NULL') + '",', 
						@UpdateBefore+='"'+col+ '":"'+ ISNULL(del,'NULL') + '",' 
					FROM  #temp WHERE id=@empid1; 

					FETCH NEXT FROM C_Employees2 INTO @empid1;
				END

	       	CLOSE C_Employees2;
			DEALLOCATE C_Employees2;

	 		SELECT TOP 1 @LineId=pkval FROM #temp WHERE pkval=@empid; 

			IF (@LineId!='')
			BEGIN	
				INSERT INTO Tecul_OperationLogs 
					(IsDelete,TableName,LineId,UpdateAfter,UpdateBefore,UpdateUser,UpdateUserCode,Methods)    
				SELECT 
					'0',@TableNameMaster AS TableName,@LineId,
					left(@UpdateAfter,len(@UpdateAfter)-1)+'}',
					left(@UpdateBefore,len(@UpdateBefore)-1)+'}',
					@UserName,@UserCode,@Methods
			END

          FETCH NEXT FROM C_Employees INTO @empid;
       END

	CLOSE C_Employees;
END

四、日志效果

最终会写入一条记录，内容类似：

{
  "CustomerName": { "before": "张三", "after": "李四" },
  "Phone": { "before": "18800000000", "after": "19900000000" }
}

字段精确、变化清晰。

一、解析 COLUMNS_UPDATED()：创建 GetColumnOrderList 函数

二、创建触发器：捕获 Insert 和 Update

三、创建日志记录存储过程

四、日志效果

发送评论 编辑评论

发送评论编辑评论