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: