客户需求
在日常业务中，销售订单等经常会修改重审，如何让复审人快速知道修改了哪些字段值。
需求分析
通常，我们可能会考虑备份订单，也就是每次保存都生成一个副本，然后根据上一次的副本来逐个字段对比。
这种方式的缺点有两个：
-
是对比工作量过大，特别是订单明细里有大量数据。
-
设计更新工作量大，如果订单模板有设计更新，那么备份模板也要进行一次更新。
本文将采用一种动态即时对比法，通过定点触发器，通用存储过程配合完成被修改字段的精确识别。
最终效果如下图：
详细步骤
1.定义触发器
我的订单明细为订单D，在订单D表上创建触发器t_order_log
该触发器主要将update过程中的deleted表(旧数据)和inserted表(新数据)存入临时复制表(abak)。
然后执行对比存储过程(pChkChg)，输出对比结果到日志表，代码如下：
create TRIGGER t_order_log
ON 订单d
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
--清理旧abak表
if object_id('abak') is not null
drop table abak;
--复制新旧数据到abak表，tt为标识，旧0新1
select 0 as tt,*
into abak
from deleted
union all
select 1 as tt,*
from inserted
--对abak执行对比
exec pChkChg 'abak'
END
2.定义通用存储过程
通用对比存储过程(pChkChg)包含一个入参@tname，可对任意特征表进行处理。
-- =============================================
-- Author: <一零院长>
-- Create date: <2020-4-22>
-- Description: <订单修改追踪>
-- =============================================
alter proc pChkChg
@tname varchar(100)
as
begin
declare @sql nvarchar(max)
declare @hfld nvarchar(max) --表值转换
declare @fld nvarchar(max) --表字段
--从系统表获取备份表的所有字段
set @fld=(select (SELECT ','+syscolumns.name
FROM syscolumns
WHERE syscolumns.id = object_id(@tname) and syscolumns.name not in('tt','sequence','recordid')
for xml path('')))
set @fld=stuff(@fld,1,1,'')
--从系统表拼接所有字段转换，类型统一为varchar(max)
set @hfld=(select (SELECT ',' + syscolumns.name + '=cast(' + syscolumns.name+ ' as nvarchar(max))'
FROM syscolumns
WHERE syscolumns.id = object_id(@tname) and syscolumns.name<>'tt'
for xml path('')))
set @hfld=stuff(@hfld,1,1,'')
--拼接字符串，列转行对比后将差异存入日志表
set @sql='
with tb as (select tt,' + @hfld + ' from ' + @tname + '),
t1 as (select * from tb unpivot (值 for 列名 in(' + @fld + '))upvt where tt=0) ,
t2 as (select * from tb unpivot (值 for 列名 in(' + @fld + '))upvt where tt=1)
insert 修改日志表(recordid,createtime,行号,描述)
select recordid,getdate(),sequence+1
,(select ''【''+t1.列名+''】''+t1.值+''改为''+t2.值 from t1 left join t2 on t1.列名=t2.列名 and t1.RecordID=t2.RecordID and t1.Sequence=t2.Sequence where t1.值<>t2.值 and t1.RecordID=tb.RecordID and t1.Sequence=tb.Sequence for xml path(''''))
from tb where tt=0'
--执行拼接sql
exec(@sql)
end
3.创建日志表
日志表很简单，只有两个用户字段
4.增加订单修改可视
修改销售订单模板，增加一个明细，3个字段，被修改行号，修改日期，修改描述
修改描述建议设置为大文本(text)。
拓展
将上述第1步的触发器挂在其他表，即可实现对其他表修改记录的精确记录。
可以将对比结果插入到esap_提醒，此部分请读者自行完成。
小结
-
利用触发器转存被修改的数据的前后记录。
-
利用通用存储过程处理转存的记录。
-
采用unpivot进行列转行，更方便对比输出，此功能sql2005+支持。
-
此功能仅JU/NX支持，并且需要明细表也设有主键。
2020-4-22