One of the most common uses of triggers is logging 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 what 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
);Some additional explanations on what we will log are as follows: