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_USERvariable, so we know who was logged in and not which role he had potentially assumed usingSET ROLE event_time_utcwill contain the event time converted to Coordinated Universal Time (UTC) so that all complex date arithmetic calculations around daylight saving change times can be avoidedtable_namewill be in theschema.tableformat- The operation will be directly from
TG_OP, although it could be just the first character (I/U/D/T), without the loss of any information - Finally, the
beforeandafterimages of rows are stored as rows converted tojson, which is available as its own data type starting in PostgreSQL Version 9.2 for easy human-readable representation of the row values
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS trigger AS $$
DECLARE
old_row json := NULL;
new_row json := NULL;
BEGIN
IF TG_OP IN ('UPDATE','DELETE') THEN
old_row = row_to_json(OLD);
END IF;
IF TG_OP IN ('INSERT','UPDATE') THEN
new_row = row_to_json(NEW);
END IF;
INSERT INTO audit_log(
username,
event_time_utc,
table_name,
operation,
before_value,
after_value
) VALUES (
session_user,
current_timestamp AT TIME ZONE 'UTC',
TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
TG_OP,
old_row,
new_row
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql; Note
The conditional expressions that check the operations at the beginning of the function are needed to overcome the fact that NEW and OLD are not NULL for the DELETE and INSERT triggers correspondingly. Rather, they are unassigned. Using an unassigned variable in any other way except assigning to it in PL/pgSQL results in an error. Any error in the trigger will usually abort the trigger and the current transaction.
We are now ready to define our new logging trigger, as shown here:
CREATE TRIGGER audit_log AFTER INSERT OR UPDATE OR DELETE ON notify_test FOR EACH ROW EXECUTE PROCEDURE audit_trigger();
Let's run a small test: we remove our original notify triggers from the notify_test table and perform a few simple operations:
postgres=# DROP TRIGGER notify_trigger ON notify_test;
DROP TRIGGER
postgres=# DROP TRIGGER notify_truncate_trigger ON notify_test;
DROP TRIGGER
postgres=# TRUNCATE notify_test;
TRUNCATE TABLE
postgres=# INSERT INTO notify_test VALUES (1);
INSERT 0 1
postgres=# UPDATE notify_test SET i = 2;
UPDATE 1
postgres=# DELETE FROM notify_test;
DELETE 1
postgres=# SELECT * FROM audit_log;
-[ RECORD 1 ]--+---------------------------
username | postgres
event_time_utc | 2013-04-14 13:14:18.501529
table_name | public.notify_test
operation | INSERT
before_value |
after_value | {"i":1}
-[ RECORD 2 ]--+---------------------------
username | postgres
event_time_utc | 2013-04-14 13:14:18.51216
table_name | public.notify_test
operation | UPDATE
before_value | {"i":1}
after_value | {"i":2}
-[ RECORD 3 ]--+---------------------------
username | postgres
event_time_utc | 2013-04-14 13:14:18.52331
table_name | public.notify_test
operation | DELETE
before_value | {"i":2}
after_value | This works well. Depending on your needs, this function will likely need some tweaking. Enough of just watching and recording DML, it is time to start influencing what goes in there.