良好的策略为DB应用程序留下审计跟踪/更改历史记录？ [英] Good strategy for leaving an audit trail/change history for DB applications?

问题描述

人们在一个相当复杂的数据库中维护数据变更历史的成功策略是什么？我经常使用和开发的应用程序之一可以真正受益于更全面的方式跟踪记录随时间的变化。例如，现在记录可以有多个时间戳和修改的用户字段，但是我们当前没有用于记录多个更改的方案，例如，如果操作回滚。

What are some strategies that people have had success with for maintaining a change history for data in a fairly complex database. One of the applications that I frequently use and develop for could really benefit from a more comprehensive way of tracking how records have changed over time. For instance, right now records can have a number of timestamp and modified user fields, but we currently don't have a scheme for logging multiple change, for instance if an operation is rolled back. In a perfect world, it would be possible to reconstruct the record as it was after each save, etc.

在DB上的一些信息：

• 需要每周增加数千条记录的能力


• 50-60表


• 使用PostgreSQL 8.x




li>

• Needs to have the capacity to grow by thousands of records per week
• 50-60 Tables
• Main revisioned tables may have several million records each
• Reasonable amount of foreign keys and indexes set
• Using PostgreSQL 8.x

推荐答案

过去我使用触发器来构造数据库更新/插入/删除日志。

In the past I have used triggers to construct db update/insert/delete logging.

您可以在每次对特定表执行上述操作之一时将记录插入记录表中，该表记录操作，数据库用户执行了什么操作，时间戳，它预先创建的表，以及之前的值。

You could insert a record each time one of the above actions is done on a specific table into a logging table that keeps track of the action, what db user did it, timestamp, table it was preformed on, and previous value.

这可能是一个更好的答案，因为这将要求您在实际删除或更新之前缓存值我想。但你可以使用这个做回滚。

There is probably a better answer though as this would require you to cache the value before the actual delete or update was preformed I think. But you could use this to do rollbacks.

这篇关于良好的策略为DB应用程序留下审计跟踪/更改历史记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！