如何跟踪数据库表中多个列的更改以进行审核？ [英] How to track changes in multiple columns in database table for auditing purposes?

问题描述

此问题已经多次回答SO：这里，这里和外部链接此处和 here 。

This question has been answered on SO several times : here, here and external links here and here.

我理解上面的线程中描述的方法，我打算使用此方法。

I understand the approaches described in above threads and I plan to use this approach.

但我在实现中有几个基本的疑问。

But I've got few basic doubts in implementing that.

在我的例子中，一行中的多个列可以同时更新，因此按照正确的方式实现：

In my case, multiple columns in a row could be updated at the same time, so is following the correct way to implement:

1. 查找操作类型（INSERT / UPDATE / DELETE）


2. 查找更新的列


3. 在更新前阅读整行


4. 在审核表中为每个使用旧值和新值（以及其他详细信息）更改的列插入一行


5. 更新表格

1. Find out type of operation (INSERT/UPDATE/DELETE)
2. Find out columns which are getting updated
3. Read the full row before updating
4. Insert one row in Audit table for each column being changed with old and new value (along with other details)
5. update the table

推荐答案

假设您使用的是最新版本的mySQL我会亲自使用触发。

Assuming you are using a sufficiently recent version of mySQL I would use triggers, personally.

假设他们的工作或多或少是我熟悉的在其他产品（例如Oracle），你的问题变得更简单，在一个意义上，你把一个更新触发器的行和使用它可以更新您感兴趣的每个字段的审计表。

Assuming they work more or less as the ones I am familiar with in other products (e.g. Oracle) your problem becomes simpler, in the sense that you put an "update" triggers on the row and use it to update the audit table for each field you are interested in.

可能的注意事项：如果您的应用程序作为一个用户登录数据库连接池），记录实际用户身份可能很棘手。

Possible caveat: if your application logs on the DB as just one user (a common approach if you use connection pooling, for example) it may be tricky to log the actual user identity.

这篇关于如何跟踪数据库表中多个列的更改以进行审核？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！