Collecting changes using triggers requires more work, but it also allows much more flexibility:
- Write a trigger function to collect new (and if needed, old) values from tuples, and save them to auditing table(s)
- Add suitable triggers to those tables for which changes need to be tracked
This can be achieved by using a handy universal audit trigger; it logs both old and new values of rows in any table, serialized as hstore data type values. The latest version of the trigger and its documentation are both available at https://github.com/2ndQuadrant/audit-trigger.
All of the audit trigger code is included in a single SQL file, which can be loaded as follows, using a database superuser account:
psql -f audit.sql ...
