sqlserver触发器根据COLUMNS_UPDATED内容生成动态更新列

关于动态生成select指令，根据条件选取特定的列，这个方法就不再细说了，百度一大把，基本就是拼接sql指令，然后exec sp_executesql

关于触发器，也不细说了，也是百度一大把

然后进入正题，怎么获取到当前表到底是哪几个字段更新了？更新是否有效？

进行简单的尝试后，发现一个问题，无法将 inserted 和 deleted 表作为参数传递给 exec sp_executesql，也无法在动态生成的指令中引用这两个表，所以麻烦了

搜索一番后，发现http://bbs.csdn.net/topics/70044742帖子中提到一个方法，将表放入临时表，然后在新的执行计划中使用，经过一番验证发现该方法可行，验证结果如下

1、在查询分析器中创建一个临时表 #tb_exec，在该会话(spid)未断开，且未drop该临时表时，临时表永远可用

2、创建的触发器、存储过程不对临时表表名进行验证，即便存储过程中没有生成对应的临时表，只要会话(spid)中存在，即可引用，即：在存在该临时表的会话中执行相应的存储过程，可直接引用临时表

3、在执行计划中生成的临时表无法反映到执行计划之外，即：会话中存在临时表#tb1，存储过程中生成#tb2，执行完存储过程后依旧只有临时表#tb1，但如果存储过程中drop了#tb1，则会话中#tb1也会失效

4、更深一步的测试，在会话中建立临时表#tb1，在存储过程中同样建立临时表#tb1，在存储过程中引用#tb1，为存储过程本身建立的临时表，在存储过程中动态执行(exec sp_executesql N'drop table #tb1')，再次在存储过程中引用#tb1，为会话建立的临时表，即临时表表名不冲突，也不覆盖，而是根据会话、执行计划等生成了#tb__________hex的系统临时表，然后根据执行计划一层一层的向上追述，直至会话层都没有才报错，但不会向下查询

好了，知道了数据库临时表的特性后，我们可以继续操作下边的内容了

首先，sqlserver提供了两个内容用来判断具体更新了那些字段，COLUMNS_UPDATED()系统函数返回一个2进制数据用来描述update指令所更新的列，update(columnName)系统函数用来判断是否更新了指定列，update()函数需要注意的是，只能使用 if 方法，因为它没有返回值，就如同contains、exists一样

先尝试了update()函数，发现这样的话需要写好多好多的if语句，并且每个表结构不一样，需要写的内容也不一样，麻烦，放弃

然后尝试COLUMNS_UPDATED()，看看怎么获取到底update更新了哪些列，于是先建立一个自定义函数，把COLUMNS_UPDATED()返回的值解析一下

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[GetColumnOrderList]    Script Date: 2017/10/19 11:30:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	文盲老顾
-- Create date: 2017-10-18
-- Description:	获取更新字段列表序号colorder
-- =============================================
CREATE 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

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

然后在触发器内执行以下语句

(select _col,name from master.dbo.GetColumnOrderList(COLUMNS_UPDATED()) a left join syscolumns b on a._col=b.colorder where id=(select parent_obj from sysobjects where id=@@PROCID)

很好，update指令到底更新了哪些字段直接就列取出来了，那么现在，我们可以玩的花样就多了

首先，我们可以把所有update指令相关的字段列出来了，在触发器中包含以下内容

	declare @tb int,@db int,@cu varbinary(max),@pk varchar(50)  -- @tb 为触发器绑定的表id，@db为触发器所在数据库id，@cu为更新字段,@pk为触发器绑定表主键或其他唯一性字段
	select @pk='id',@db=db_id(),@tb=(select parent_obj from sysobjects where id=@@PROCID),@cu=COLUMNS_UPDATED()
	select * into #tb from deleted       -- 将系统表扔到临时表中，以方便新的执行计划中引用
	declare @sql nvarchar(max)

        -- 动态生成数据库指令，仅列取主键和更新相关的字段
	select @sql = '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 for xml path('')) + ' from #tb;'
	exec sp_executesql @sql  -- 执行动态指令

ok，非常简单的就把相关字段内容列取出来了

继续我们的花样，怎么判断相关字段的更新是否有效？首先思考的方式是用行列转换，但是，每个表的结构不一样，字段类型不一样，转换起来太麻烦了，我们换个方式去做：把相关数据生成xml，然后解析xml，这样是否可行？来尝试下

	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()
	select * into #ins from inserted
	select * into #del from deleted
	declare @sql nvarchar(max)
	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 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 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;'
	exec sp_executesql @sql

非常好，他把所有的有效更新都列出来了，当然列出的格式是我随便定义的，如图

很不错，这样我们就可以实际更新的内容玩出更多的花了，到这一步之后，再怎么继续玩下去，就看大家自己的需求啦，文盲先自个去玩玩看