Auditing Changes with a Trigger

Setup for capturing changes

First, we need some setup:

• We’re going to track changes made when we update the MoMA collection, which we processed in “Batch Update, MoMA Collection.” The table we’re auditing is moma.artist.

• The changes are recorded in a table named moma.audit, defined in a pretty generic way as we can see below.

• Then we install PostgreSQL triggers on the moma.artist table to capture any change made to it and populate the moma.audit table with the before and after versions of updated rows.

  The representation of the row is recorded using the hstore format, which is very flexible and could be used to track more than one table definition; either several tables or just the same table, even in the case of schema changes done with ALTER TABLE.

Implementation

The idea is to add a row in the audit table each time the moma.artist table is updated, with the hstore representation of the data in flight before and after the change: