跟踪数据库列值的变化
我正在使用 Zend 框架开发一个基于 Web 的应用程序。
我正在寻找一种方法来保存对一堆列的更新历史记录。
用例： 用户可以添加具有 5 个属性的项目。允许用户更新 5 个属性。我需要跟踪他对这 5 个属性所做的所有更新。
我想到的方法是添加一个名为 log 的新表，它存储旧值、新值、列名、itemID 和时间戳。
最好的方法是什么？是否有任何现有的方法/选项/示例？
谢谢，
I am working on a web based application using Zend framework.
I am looking for a way to keep history of updates made to a bunch of columns.
Use-case:
User can add an item with 5 properties. The user is allowed to update the 5 properties. I need to track all the updates he makes to those 5 properties.
On way I thought of was adding a new table named log which stores the old value, new value, column name, itemID and timestamp.
What is the best way to do this? Are there any existing methods/options/examples ?
Thanks,
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可能正在寻找的是审核日志。您可以在 MySQL 数据库上使用触发器创建一个。
如何执行此操作的示例如下： http:// ronaldbradford.com/blog/auditing-your-mysql-data-2008-07-15/
在下面的评论中，您补充说数据库不是“固定的”。在这种情况下，您必须复制对表的任何更改，以便将它们应用到审计表，这样任何新添加的列也会添加到日志中。
您可以考虑将其记录在您的应用程序中（因为您为此问题选择的标签似乎是建议） - 但请记住，这意味着在某些情况下您的日志可能无法提供完整的答案。数据库中的触发器、存储过程和任何手动干预都不会被记录...因此，如果您选择走这条路，请务必小心。
What you're probably looking for is an audit log. You can create one using Triggers on your MySQL database.
An example of how to do this is here: http://ronaldbradford.com/blog/auditing-your-mysql-data-2008-07-15/
In your comment below you added that the database is not 'fixed'. In that case, you'd have to replicate any alters to the table so that they are applied to the audit table in such a way that any newly added columns are added to the log as well.
You can consider logging this in your application (as your tags chosen for this question seem to suggestion) - but keep in mind that this means there can be situations where your log does not provide the complete answer. Triggers, stored procedures and any manual interventions in the database will not be logged... so be careful if you choose to go down that path.
触发器是进行审核的最常见方法，也是捕获已完成操作（无论是从用户界面还是其他地方完成）的唯一真正可靠的方法。不过，它们的编写方式确实因数据库而异。如果您知道将支持的数据库后端的可能类型，则可以为每种类型编写单独的触发器。
如果您必须在没有触发器的情况下处理此问题，那么最好的选择是拥有一个写入审核表并进行更新更改的进程。它可能足够复杂，需要由 Zend 框架调用存储过程，而不是依赖框架本身来执行。 （我不熟悉 Zend，所以我不知道这是否可以设置，我知道存储过程可以处理这个问题。）
Triggers are the most common way to do auditing and the only really reliable way to capture what was done whether done from the user interface or elsewhere. They do vary by database in how they would be written though. If you know the possible types of database backends you will support, you could write separate triggers for each.
If you must handle this without triggers, then your best bet is to have a process that writes to the audit table as well as makes the update change. It might be complex enough to warrant a stored proc called by the Zend framework rather than relying on the framework itself to do. (I'm not familair with Zend so I don't know if this is something that could be set up, I know a stored proc could handle this.)
这里有一个更好的..
请参阅 弹出审计跟踪
Here is a better one..
See Pop On the Audit Trail
我刚刚创建了一个名为 Comp_Hist_Log 的新表，然后在
更新前钩子
$oldData = $array('fieldname1', 'fieldname2')
然后在我的数据库 gui 挂钩文件中的 AFTER_UPDATE 挂钩处..我添加了此代码
sql("INSERT INTO
Comp_Hist_Log(Com_Rec_Id< /code>,old_data,new_data,ChangedDate,ChangedBy)VALUES('{$data['Record_Id') ]}', '{$oldData}', '{$messageData}', '{$data['LastUpdated']}', '{$memberInfo['用户名']}')", $eo);
返回真；
希望它有帮助..它确实有效。
I just created a new table called it Comp_Hist_Log and then defined the old data in the
BEFORE UPDATE hook
$oldData = $array('fieldname1', 'fieldname2')
Then at the AFTER_UPDATE hook in my database gui hook file.. I added this code
sql("INSERT INTO
Comp_Hist_Log(Com_Rec_Id,old_data,new_data,ChangedDate,ChangedBy)VALUES('{$data['Record_Id']}', '{$oldData}', '{$messageData}', '{$data['LastUpdated']}', '{$memberInfo['username']}')", $eo);
return TRUE;
Hope it helps.. it does work.