Hi!
when it comes to portability the right place to implement such
auditing is in the OR-mapper.
Most commercial mappers already provide such a feature.
However, if you don't like "black-boxed" third party
software, writing your own mapper isn't a big deal.
It's worth the effort!
Some hints:
- writing a mapper usually ends up with methods like
MySpecialDbObject.save() or insert() or update() or alike.
- your mapper should provide a method to turn on/off logging
on a per class basis.
- one solution to the auditing problem is an extra "logging table"
per "data table". The logging table holds _all_ the columns of the
data table _plus_ a serial number (incremented each time the
corresponding data tuple is modified) and things like userid/name,
timestamp and so on.
- you should store the serial-number in the data-table too.
At least, this is how we did it and it works fine.
If you don't mind portability postgres probably provides some
auditing support, but I don't know.
Anyway, you should either use a dbms-inherent feature _or_ do it
in your application. Mixing both levels is not a good idea, imho.
Hope it helps,
Harald.