database 如何跟踪数据库表中的数据更改

How to track data changes in a database table

提问by user3711

What is the best way to track changes in a database table?

跟踪数据库表更改的最佳方法是什么？

Imagine you got an application in which users (in the context of the application not DB users ) are able to change data which are store in some database table. What's the best way to track a history of all changes, so that you can show which user at what time change which data how?

想象一下，您有一个应用程序，其中用户（在应用程序的上下文中，而不是数据库用户）能够更改存储在某个数据库表中的数据。跟踪所有更改历史记录的最佳方法是什么，以便您可以显示哪个用户在什么时间更改了哪些数据？

采纳答案by Jeremy McGee

In general, if your application is structured into layers, have the data access tier call a stored procedure on your database server to write a log of the database changes.

通常，如果您的应用程序是分层结构的，则让数据访问层调用数据库服务器上的存储过程来写入数据库更改的日志。

In languages that support such a thing aspect-oriented programmingcan be a good technique to use for this kind of application. Auditing database table changes is the kind of operation that you'll typically want to log for all operations, so AOP can work very nicely.

在支持这种东西的语言中，面向方面的编程可能是用于此类应用程序的一种很好的技术。审计数据库表更改是您通常希望为所有操作记录的那种操作，因此 AOP 可以很好地工作。

Bear in mind that logging database changes will create lots of data and will slow the system down. It may be sensible to use a message-queue solution and a separate database to perform the audit log, depending on the size of the application.

请记住，记录数据库更改会创建大量数据并降低系统速度。根据应用程序的大小，使用消息队列解决方案和单独的数据库来执行审计日志可能是明智的。

It's also perfectly feasible to use stored procedures to handle this, although there may be a bit of work involved passing user credentials through to the database itself.

使用存储过程来处理这个问题也是完全可行的，尽管将用户凭据传递给数据库本身可能需要一些工作。

回答by Kevin Fairchild

One method I've seen quite often is to have audit tables. Then you can show just what's changed, what's changed and what it changed from, or whatever you heart desires :) Then you could write up a trigger to do the actual logging. Not too painful if done properly...

我经常看到的一种方法是使用审计表。然后，您可以显示更改的内容、更改的内容以及更改的内容，或者您​​想要的任何内容:) 然后您可以编写一个触发器来进行实际的日志记录。如果操作得当，不会太痛苦...

No matter how you do it, though, it kind of depends on how your users connect to the database. Are they using a single application user via a security context within the app, are they connecting using their own accounts on the domain, or does the app just have everyone connecting with a generic sql-account?

但是，无论您如何操作，这都取决于您的用户如何连接到数据库。他们是通过应用程序内的安全上下文使用单个应用程序用户，他们是使用域上自己的帐户进行连接，还是应用程序只是让每个人都连接到通用 sql 帐户？

If you aren't able to get the user info from the database connection, it's a little more of a pain. And then you might look at doing the logging within the app, so if you have a process called "CreateOrder" or whatever, you can log to the Order_Audit table or whatever.

如果您无法从数据库连接中获取用户信息，那就有点麻烦了。然后您可能会考虑在应用程序中进行日志记录，因此如果您有一个名为“CreateOrder”或其他名称的流程，您可以登录到 Order_Audit 表或其他任何内容。

Doing it all within the app opens yourself up a little more to changes made from outside of the app, but if you have multiple apps all using the same data and you just wanted to see what changes were made by yours, maybe that's what you wanted... <shrug>

在应用程序内完成所有操作会让您更容易接受应用程序外部所做的更改，但是如果您有多个应用程序都使用相同的数据，而您只想查看您的应用程序进行了哪些更改，也许这就是您想要的... <耸肩>

Good luck to you, though!

不过祝你好运！

--Kevin

——凯文

回答by TomNysetvold

In researching this same question, I found a discussion herevery useful. It suggests having a parallel table set for tracking changes, where each change-tracking table has the same columns as what it's tracking, plus columns for who changed it, when, and if it's been deleted. (It should be possible to generate the schema for this more-or-less automatically by using a regexed-up version of your pre-existing scripts.)

在研究同样的问题时，我发现这里的讨论非常有用。它建议设置一个用于跟踪更改的并行表，其中每个更改跟踪表具有与其跟踪的相同的列，以及更改者、更改时间以及是否已删除的列。（通过使用预先存在的脚本的正则表达式版本，应该可以或多或少地自动生成模式。）

回答by thelsdj

You've got a few issues here that don't relate well to each other.

您在这里遇到了一些彼此之间不太相关的问题。

At the basic database level you can track changes by having a separate table that gets an entry added to it via triggers on INSERT/UPDATE/DELETE statements. Thats the general way of tracking changes to a database table.

在基本数据库级别，您可以通过一个单独的表来跟踪更改，该表通过 INSERT/UPDATE/DELETE 语句上的触发器将条目添加到其中。这是跟踪数据库表更改的一般方法。

The other thing you want is to know which usermade the change. Generally your triggers wouldn't know this. I'm assuming that if you want to know which user changed a piece of data then its possible that multiple users could change the same data.

您想要的另一件事是知道哪个用户进行了更改。通常你的触发器不会知道这一点。我假设如果您想知道哪个用户更改了一条数据，那么多个用户可能会更改相同的数据。

There is no right way to do this, you'll probably want to have a separate table that your application code will insert a record into whenever a user updates some data in the other table, including user, timestamp and id of the changed record.

没有正确的方法可以做到这一点，您可能希望拥有一个单独的表，每当用户更新另一个表中的某些数据（包括用户、时间戳和更改记录的 ID）时，您的应用程序代码就会将记录插入到该表中。

Make sure to use a transaction so you don't end up with cases where update gets done without the insert, or if you do the opposite order you don't end up with insert without the update.

确保使用事务，这样您就不会遇到在没有插入的情况下完成更新的情况，或者如果您执行相反的顺序，您最终不会在没有更新的情况下插入。

回答by Neeraj Bansal

Suppose I have a Person Table with 10 columns which include PersonSid and UpdateDate. Now, I want to keep track of any updates in Person Table. Here is the simple technique I used:

假设我有一个包含 PersonSid 和 UpdateDate 的 10 列的人员表。现在，我想跟踪人员表中的任何更新。这是我使用的简单技术：

1. Create a person_log table

   create table person_log(date datetime2, sid int);

2. Create a trigger on Person table that will insert a row into person_log table whenever Person table gets updated:

   create trigger tr on dbo.Person
   for update
   as insert into person_log(date, sid) select updatedDTTM, PersonSID from inserted

1. 创建一个 person_log 表

   创建表 person_log(date datetime2, sid int);

2. 在 Person 表上创建一个触发器，每当 Person 表更新时，该触发器将向 person_log 表中插入一行：

   在 dbo.Person 上创建触发器 tr 以
   进行更新，
   因为插入到 person_log(date, sid) 中选择 updatedDTTM, PersonSID 从插入

After any updates, query person_log table and you will be able to see personSid that got updated. Same you can do for Insert, delete.

任何更新后，查询 person_log 表，您将能够看到更新的 personSid。您可以对插入、删除执行相同操作。

Above example is for SQL, let me know in case of any queries or use this link : http://www.4guysfromrolla.com/webtech/042507-1.shtml

以上示例适用于 SQL，如有任何疑问，请告诉我或使用此链接：http: //www.4guysfromrolla.com/webtech/042507-1.shtml

回答by Unsliced

A trace log in a separate table (with an ID column, possibly with timestamps)?

单独表中的跟踪日志（带有 ID 列，可能带有时间戳）？

Are you going to want to undo the changes as well - perhaps pre-create the undo statement (a DELETE for every INSERT, an (un-) UPDATE for every normal UPDATE) and save that in the trace?

您是否也想撤消更改 - 也许预先创建撤消语句（每个 INSERT 的 DELETE，每个正常 UPDATE 的 (un-) UPDATE）并将其保存在跟踪中？

回答by Christian Del Bianco

Let's try with this open source component:

让我们试试这个开源组件：

https://tabledependency.codeplex.com/

https://tabledependency.codeplex.com/

TableDependency is a generic C# component used to receive notifications when the content of a specified database table change.

TableDependency 是一个通用的 C# 组件，用于在指定数据库表的内容更改时接收通知。

回答by moledet

If all changes from php. You may use classto log evry INSERT/UPDATE/DELETE before query. It will be save action, table, column, newValue, oldValue, date, system(if need), ip, UserAgent, clumnReference, operatorReference, valueReference. All tables/columns/actions that need to log are configurable.

如果所有更改都来自 php.ini。您可以在查询之前使用类来记录每次插入/更新/删除。它将是 save action、table、column、newValue、oldValue、date、system（如果需要）、ip、UserAgent、clumnReference、operatorReference、valueReference。所有需要记录的表/列/操作都是可配置的。