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

关于数据库的一些信息:

Some info on the DB:

• 需要有能力每周增加数千条记录
• 50-60 桌
• 主要修订表每个可能有几百万条记录
• 合理数量的外键和索引设置
• 使用 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屋！