ESAP达摩院 思想有多远,我们就能走多远

ESAP中级教程|订单修改精确识别


客户需求

在日常业务中,销售订单等经常会修改重审,如何让复审人快速知道修改了哪些字段值。

需求分析

通常,我们可能会考虑备份订单,也就是每次保存都生成一个副本,然后根据上一次的副本来逐个字段对比。

这种方式的缺点有两个:

  1. 是对比工作量过大,特别是订单明细里有大量数据。

  2. 设计更新工作量大,如果订单模板有设计更新,那么备份模板也要进行一次更新。

本文将采用一种动态即时对比法,通过定点触发器,通用存储过程配合完成被修改字段的精确识别。

最终效果如下图:

详细步骤

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


近似文章