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 ...