如何检索两个 SQL Server 表的审计跟踪并将它们按正确的顺序放回？

Question

背景上下文：

---

我有一个名为Project的数据库表和一个名为ProjectHistory的表，具有相同的架构。我正在使用 nhibernate，所以我还有一个名为 Project 的域对象和一个名为 ProjectHistory 的域对象（源自 Project）。

目标

---

My goal is to retrieve data across these tables and put them back in order as the events that happened and have C# code output a typical audit trail screen of last n number of changes

详细信息

---

Both tables have the following fields and data types

• Id -(int )- Project 中的主键，注意：历史表中没有 PK
• LastUpdated (datetime)
• Name (varchar)
• Description (varchar)
• Timestamp (时间戳)

Projecthistory 的目标 表的问题是，每当我对 Project 表进行更新时，ProjectHistory 表就会插入包含旧记录的新行，以便我可以进行全面审核踪迹。

我通过使用此触发器来实现此目的：

 ALTER TRIGGER ProjectTrigger
 ON Project
 AFTER UPDATE
 AS
    SET NOCOUNT ON

    insert into ProjectHistory
    select * from deleted

我的问题是，我现在需要一些 C# 代码来整理审核历史记录，其中用户选择最近的 n 个事件。

示例：

---

Here is an example of events:

1. 1 月 10 日 - 创建项目 1（尚未在历史记录中）
2. 1 月 11 日 - 创建项目 2
3. 1 月 15 日 - 编辑项目 2（将条目放入项目 2 的历史表中，最后更新日期为 1 月 11 日）
4. 1 月 25 日 - 编辑项目 1（将在项目 1 的历史表中添加 1 月 10 日日期的条目）

因此，正如您所看到的，如果我按历史表中的 LastUpdated 字段降序排序，则将事件 4 的结果放在事件 4 之前来自事件 3 的结果，该事件发生故障。

代码

---

我已尝试使用以下代码来尝试将它们组合在一起，但我将在下面解释该缺陷：

   public IEnumerable<Project> GetHistory<Project, ProjectHistory>(int numberOfChanges)
    {
        IEnumerable<Project> current;
        IEnumerable<Project> history;
            current = Session.Query<Project>()
                .OrderByDescending(r => r.LastUpdated)
                .Take(numberOfChanges);

             history = Session.Query<ProjectHistory>()
            .OrderByDescending(r => r.LastUpdated).Cast<Project>();

            IEnumerable<Project> all = current.Concat(history);
            return all.OrderByDescending(r => r.Id).ThenByDescending(r => r.LastUpdated);
        }
    } 

我对上述代码的主要问题是：

1. 历史记录中的LastUpdated时间将反映上一个项目更新的LastUpdated时间，因此我无法排序desc 历史表中的该字段并假设我将获得正确的事件降序排列。

   1. 我可以使用 timestamp 字段进行排序（因为这是输入记录的实际时间），但您似乎无法使用 C# nhibernate 对该字段进行排序，因为该字段只是一个二进制 (8)，因此它会转换为 C# 中的 byte[]，不支持 IComparable。

2. 我可以在项目表上使用lastUpdated字段，因为按lastupdate字段排序将使我按顺序获得最新的事件。我想过仅循环 project 表，然后对每个项目进行单独的查询以获取最新的历史项目，但这不支持对同一项目 ID 进行多次更改的情况。问题是我无法找到以正确顺序获取数据的方法。

我的主要观点是，我想完成我认为非常通用的事情，必须已经解决，所以我必须只是缺少这里的基本模式。

我已经有了可以将一个 Project 对象与另一个 project 对象进行比较并返回字段差异（效果完美）的代码，但我只需要一个关于弄清楚如何得到的建议这些数据以正确的顺序跨这些表从数据库中出来（来自我的查询或事后代码），这样我就可以显示“最后n个更改”，然后使用我的逻辑来显示每个更改的字段差异。

Answer 1

这是我针对一个类似项目的解决方案。创建一个视图来联合项目和项目历史表。让 SQL Server 来完成繁重的工作。

这是一个例子：

CREATE view [dbo].[VwProjectHistory] as select
    OtherFieldName,
    LastUpdated
from
    [dbo].[Project]

union all

select
    OtherFieldName,
    LastUpdated
from
    [dbo].[ProjectHistory]

Answer 2

如果我正确理解你的问题，你可以简单地对转换为长的时间戳进行排序。

// The last row in your question, 0 means start from the start of byte[]
return all.OrderByDescending(r => BitConverter.ToInt64(r.Timestamp,0));

Answer 3

选项 1：
这是一个链接，允许您使用order by 子句中的时间戳。我自己没有使用过这个，所以使用时需要您自担风险。

选项 2：
将自动增量字段添加到 ProjectHistory，然后您可以按项目 id 和自动增量 id 对结果进行排序。我没有太多使用强制转换操作，因此我不确定这些含义，但我确信您将在映射方面进行更多工作。

选项 3：
您是否研究过NHibernate Envers，它是一个支持和审计跟踪的框架？这将是一个新的学习曲线，但从长远来看，它可能会为您节省大量工作。