为数据库应用程序保留审计跟踪/更改历史记录的有效策略？ [英] Effective 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.

数据库中的某些信息：

• 需要具有每周增加数千条记录的容量


• 50-60表


• 主修订表可能每个都有几百万条记录


• 合理数量的外键和索引集


• 使用PostgreSQL 8.x
  

• 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

推荐答案

在过去，我使用触发器来构造db update / insert / delete日志记录。

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 performed 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 performed I think. But you could use this to do rollbacks.

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