首先,sqlserver提供了两个内容用来判断具体更新了那些字段,COLUMNS_UPDATED()系统函数返回一个2进制数据用来描述update指令所更新的列,update(columnName)系统函数用来判断是否更新了指定列,update()函数需要注意的是,只能使用 if 方法,因为它没有返回值,就如同contains、exists一样
(select _col,namefrom master.dbo.GetColumnOrderList(COLUMNS_UPDATED()) a leftjoin syscolumns b on a._col=b.colorder where id=(select parent_obj from sysobjects where id=@@PROCID)
select @sql = 'select ' + @pk + (select',' + namefrom master.dbo.GetColumnOrderList(@cu) a leftjoin syscolumns b on a._col=b.colorder where id=@tb andname<>@pk for xml path('')) + ' from #tb;'
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',' + namefrom master.dbo.GetColumnOrderList(@cu) a leftjoin syscolumns b on a._col=b.colorder where id=@tb andname<>@pk for xml path('')) + ' from #ins for xml raw,root(''ins''),type,elements XSINIL);'
set @sql = @sql + 'select @del = (select ' + @pk + (select',' + namefrom master.dbo.GetColumnOrderList(@cu) a leftjoin syscolumns b on a._col=b.colorder where id=@tb andname<>@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;'