实施历史记录表 [英] Implement History Table

问题描述

我想在我的应用程序中实现历史记录功能，但是我想跳过 为每个业务对象数据表创建历史记录数据表.

I want to implement history functionality in my application, but I want to skip creating history data table for every business object data table.

我正在考虑创建一个历史数据表，其中包含一个标识列和一个XML列，其数据发生了如下变化

I was thinking about creating one history data table, that contain one identity column and an XML column with changed data as follow

ID: int
XML: Data
Name: data table name

此方法是由某人实施的还是您看到有关此体系结构的任何限制?

Is this approach implemented by someone or do you see any constraints about this architecture?

推荐答案

我使用了本文的修改版本-

I have used a modified version of this article - Adding simple trigger-based auditing to your SQL Server database - for years now.

它使用旧的INFORMATION_SCHEMA视图，因为它依赖于列位置，而在任何新的DMV(例如sys.tables，sys.columns)中均不可用.除此之外，它像魅力一样飞扬和运转.

It uses the old INFORMATION_SCHEMA views since it relies on the column position which isn't available in any of the newer DMVs (e.g. sys.tables, sys.columns). Other than that it flies and works like a charm.

如果性能成为问题(而且这种情况很少见)，您可以考虑使用Service Broker来实现异步触发器以写入历史记录表.

If performance becomes a problem - and this is rare - you can consider using Service Broker to implement asynchronous triggers for writing to the history table.

这篇关于实施历史记录表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！