利用触发器写数据库表操作日志

获取Update具体更新了哪些列，建立自定义函数，把COLUMNS_UPDATED()返回值解析一下

因为是针对系统方法的方法，所以把函数放到master系统库里，方便任意数据库调用

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[GetColumnOrderList]    Script Date: 2022/3/30 15:58:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    lt
-- Create date: 2022-03-18
-- Description:    获取更新字段列表序号colorder
-- =============================================
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

创建（Insert，Update）触发器

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 

创建存储过程，写操作日志

USE [T120211230133657806]
GO
/****** Object:  StoredProcedure [dbo].[pro_I_GlobalLogs]    Script Date: 2022/3/30 16:10:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		lt
-- Create date: 20220303
-- Description:	写日志
-- =============================================
ALTER PROCEDURE [dbo].[pro_I_GlobalLogs]
	-- Add the parameters for the stored procedure here
	@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)
	PRINT @id
    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