报告对数据的单个更改

本文关键字：单个更 数据 报告 | 更新日期: 2023-09-27 18:32:31

我需要对数据集的单个更改生成每日提取。因此，有 14 个包含产品和客户信息的表，数据提取必须仅显示新输入的每个表中的记录或已更新任何列的记录。

请问最好的方法是什么？

我们使用SQL Server 2008 R2，并使用C#进行开发。

报告对数据的单个更改

当我想跟踪用户的更改以便从他们的错误中恢复时，我创建了一个包含所有 pks 的非规范化"审计"表，并创建了触发器以在修改基表时向其添加行。

触发因素之一是：

CREATE TRIGGER [dbo].[programGroupProcedureCodeModRateDate_insdel]
   ON  [dbo].[programGroupProcedureCodeModRateDate] 
   AFTER insert, delete
AS 
BEGIN
    SET NOCOUNT ON;
    insert into archive
        select 
            procedureCodeModRate, 
            null as programGroup_svc, null as serviceID, null as serviceDesc, null as isExcludedFromIncreases,
            null as unitID,
            null as rateMaskID, null as programGroup_unpub, null as isUnpublished,
            programGroup_rate, startID, Rate, isDiscontinued, comment, 
            getdate() as lastupdated, Current_User as lastUpdatedBy, 'i' as operation
        from inserted
    insert into archive
        select 
            procedureCodeModRate, 
            null as programGroup_svc, null as serviceID, null as serviceDesc, null as isExcludedFromIncreases,
            null as unitID,
            null as rateMaskID, null as programGroup_unpub, null as isUnpublished,
            programGroup_rate, startID, Rate, isDiscontinued, comment, 
            getdate() as lastupdated, Current_User as lastUpdatedBy, 'd' as operation
        from deleted
END

我会为另一个表设置一个触发器，并且任何插入或更新都会记录在另一个表中，其中包含更改的字段以及更改发生的时间。

相关文章：