Database Reference
In-Depth Information
The audit trigger
One of the most common uses of triggers is logging data changes to tables in a con-
sistent 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:
• 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
(
UTC
) so that all strange date arithmetic around daylight
saving change times can be avoided.
•
table_name
will be in format
schema.table
.
• Operation will be directly from
TG_OP
, though it could be just the first char-
acter (I/U/D/T) without losing any information.
• Finally, the
before
and
after
images of rows are stored as rows conver-
ted to
json
which is available as its own data type starting PostgreSQL
Version 9.2 for easy human-readable representation of ROW values.
Next, the trigger function:
