The audit trigger

One of the most common uses of triggers is to log data changes to tables in a consistent and transparent manner. When creating an audit trigger, we first must decide what we want to log.

A logical set of things that can be logged are who changed the data, when the data was changed, and which operation changed the data. This information can be saved in the following table:

CREATE TABLE audit_log (
    username text, -- who did the change
    event_time_utc timestamp, -- when the event was recorded
    table_name text, -- contains schema-qualified table name
    operation text, -- INSERT, UPDATE, DELETE or TRUNCATE
    before_value json, -- the OLD tuple value
    after_value json -- the NEW tuple value
);

Here's some additional information on what we will log:

• The username will get the SESSION_USER variable, so we know who was logged in and not which role he had potentially assumed using SET ROLE

• event_time_utc will contain the event time converted to Coordinated Universal Time...