保持每一列的更新更改 [英] Keeping update changes for each column

问题描述

我的客户要求我管理一个SQL Server表中每个列/字段的更新历史记录。我之前已经管理了整个行的批量版本，但是之前没有做过这种类型的备份/历史记录。他们希望能够跟踪表格行中每个列/字段的更改。我可以以最有效的方式使用一些帮助来对此进行跟踪。谢谢！

I've been asked by my client to manage update history for each column/field in one of our SQL Server tables. I've managed "batch" versions of entire rows before, but haven't done this type of backup/history before. They want to be able to keep track of changes for each column/field in a row of a table. I could use some help in the most efficient way to track this. Thanks!

推荐答案

答案是触发器和历史记录表，但是最佳实践取决于您的数据库保存哪些数据，以及保存频率和频率。被修改多少。

The answer is triggers and history tables, but the best practice depends on what data your database holds and how often and by how much it is modified.

基本上，每次表中的记录被更新时，更新触发器（附加到表上）都会收到通知，告知旧记录是什么样，新记录是什么样喜欢。然后，您可以将更改历史记录写入新记录到另一个表（即tblSomething_History）。注意：如果通过存储的proc完成对表的更新，则可以从那里写入历史记录，但是这样做的问题是，如果另一个存储过程也更新了您的表，则历史记录不会

Essentially every time a record in a table is updated, an update trigger (attached to the table) gets notified what the old record looked like and what the new record will look like. You can then write the change history to new records to another table (i.e. tblSomething_History). Note: if updates to your tables are done via stored procs, you could write the history from there, but the problem with this is if another stored procedure updates your table as well, then the history won't be written.

根据您想要历史记录的字段/表的数量，可以按照@ M.Al的建议进行操作，您可以将历史记录直接嵌入到基础版本，也可以为每个单独的表创建一个历史表，也可以创建一个通用的历史表，例如：

Depending on the amount of fields / tables you want history for, you may do as suggested by @M.Al, you may embedded your history directly into the base table through versioning, or you may create a history table for each individual table, or you may create a generic history table such as:

| TblName | FieldName | NewValue | OldValue | User | Date Time | 

获取修改的时间很容易，但是要取决于安全设置来确定哪个用户更改了什么。将历史记录保存在单独的表中意味着对检索当前数据的影响较小，因为已将其分开，因此您无需将其过滤掉。但是，如果您需要大部分时间显示历史记录，那么效果可能会不同。

Getting the modified time is easy, but it depends on security setup to determine which user changed what. Keeping the history in a separate table means less impact on retrieving the current data, as it is already separated you do not need to filter it out. But if you need to show history most of the time, this probably won't has the same effect.

不幸的是，您无法向数据库中的所有表添加单个触发器，您需要为每个触发器创建一个单独的触发器，但是它们可以随后调用一个存储过程来完成实际工作。

Unfortunately you cannot add a single trigger to all tables in a database, you need to create a separate trigger for each, but they can then call a single stored procedure to do the guts of the actual work.

另一个警告语也是如此：自动加载与表相关的所有历史记录会极大地增加所需的负载，具体取决于表中存储的数据类型，历史记录可能会系统地大于基表。我遇到了许多无法使用的应用程序并受到了这些应用程序的影响，因为历史记录表是在基表被加载时不必要地加载的，并且鉴于该表的更改历史记录可能会遇到每项100的变化，因此加载时间增加了多少。

Another word of warning as well: automatically loading all history associated with tables can dramatically increase the load required, depending on the type of data stored in your tables, the history may become systematically larger than the base table. I have encountered and been affected by numerous applications that have become unusable, because the history tables were needlessly loaded when the base table was, and given the change history for the table could run into 100's per item, that's how much the load time increased.

最终提示：这是一种策略，如果从头开始将其内置到您的应用程序中，则很容易吸收，但是要小心地将其连接到现有解决方案上如果不根据您的要求进行调整，可能会对性能产生重大影响。而且成本可能超出客户的预期。

Final Note: This is a strategy which is easy to absorb if built into your application from the ground up, but be careful bolting it on to an existing solution, as it can have a dramatic impact on performance if not tailored to your requirements. And can cost more than the client would expect it to.

这篇关于保持每一列的更新更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！