How can I maintain a log of the data in my DB?
I have to maintain a log of every change made to each row. That means that I can't allow DELETE and UPDATE to be performed.
How can I keep such a log?
Ideas on database design for capturing audit trails
Viewed 29668
5 Answers
Use "Insert Only Databases"
The basic idea is that you never update or delete data.
Each table has 2 datetime columns from and to.
They start with the value null in each (beginning of time to end of time)
When you need to "change" the row you add a new row, at the same time you update the to in the previous row to Now and the from in the row you are adding to Now.
You read data out of the table via a view that has a where to = null in it.
This method also gives you a picture of the state of your database at any point in time.
EDIT
Just to clarify in response to the comment: The sequence would be given by the primary key of the table, which would be an autoincrement number.
See if my answer to another database logging question contains the information you need. Find it here...
History tables pros, cons and gotchas - using triggers, sproc or at application level
Related