7

We're implementing a New system using Java/Spring/Hibernate on PostgreSQL. This system needs to make a copy of Every Record as soon as a modification/deletion is done on the record(s) in the Tables(s). Later, the Audit Table(s) will be queried by Reports to display the data to the users.

I was planning to implement this auditing/versioning feature by having a trigger on the table(s) which would make a copy of the modified row(deleted row) "TO" a TABLE called ENTITY_VERSIONS which would have about 20 columns called col1, col2, col3, col4, etc which would store the columns from the above Table(s); However, the problem is that if there is more than 1 Table to be versioned and ONLY 1 TARGET table(ENTITY_VERSIONS) to store all the tables' versions, how do I design the TARGET table ?

OR is it better that there will be a COPY of the VERSION Table for each Table that needs versioning ?

It will be bonus if some pointers towards PostgreSQL Triggers (and associated Stored Procedure ) code for implementing the auditing/versioning can be shared.

P.S : I looked at Suggestions for implementing audit tables in SQL Server? and kinda like the answer except I would NOT know what type should OldValue and NewValue be ?

P.P.S : If the Tables use SOFT DELETEs (phantom deletes) instead of HARD deletes, do any of your advice change ?

Community
  • 1
  • 1
anjanb
  • 11,266
  • 16
  • 69
  • 99

2 Answers2

4

I would have a copy of each table to hold the versions of that table you wish to keep. It sounds like a bit of a nightmare to maintain and use a global versioning table.

This link in the Postgres documentation shows some audit trigger examples in Postgres.

rfusca
  • 6,437
  • 1
  • 27
  • 33
  • +1 thanks. I wonder what the strengths of having a "GLOBAL audit TABLE" vs "Audit Table for each Table" are w.r.t querying the data for reporting ? – anjanb Jul 07 '10 at 12:30
  • 1
    If you somehow manage to have a GLOBAL audit table, I foresee ALOT of type casting. I mean...what type would you store all the columns in? – rfusca Jul 07 '10 at 16:44
2

In global table all columns can be stored in single column as hstore type. I just tried audit and I it is works great, I recommend it. Awesome audit table example tracks all changes in single table by simply adding a trigger onto the tables you want to begin to keep audit history on. all changes are stored in as hstore type- works for v 9.1+ this link

roger
  • 21
  • 1