Triggers for audit history
Introπ
It is quite common to have some kind of audit trail for changes in master data tables. Just setting the values in 'update_user' and 'update_timestamp' fields does not tell you what was actually changed.
Postgresql can offer a very powerfull solution by combining the JSON datatype and triggers.
How to do it?π
Hans-JΓΌrgen SchΓΆnig presents a [nice solution on his blog.
Basically you set a trigger on every table and the changes are stored in a JSON field
in a special logging table
.
1. Define the tableπ
file: origional_table.sql
CREATE SCHEMA logging; CREATE TABLE logging.t_history( id serial, tstamp timestamp DEFAULT NOW( ), schemaname text, tabname text, operation text, who text DEFAULT CURRENT_USER, new_val json, old_val json );
2. create the user-defined-function:π
file: origional_function.sql
CREATE FUNCTION change_trigger() RETURNS trigger AS $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO logging.t_history (tabname, schemaname, operation, new_val) VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW)); RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN INSERT INTO logging.t_history (tabname, schemaname, operation, new_val, old_val) VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), row_to_json(OLD)); RETURN NEW; ELSIF TG_OP = 'DELETE' THEN INSERT INTO logging.t_history (tabname, schemaname, operation, old_val) VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD)); RETURN OLD; END IF; END; $$ LANGUAGE 'plpgsql' SECURITY DEFINER;
i
3. Attach the trigger:π
file: origional_trigger.sql
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON t_trig FOR EACH ROW EXECUTE PROCEDURE change_trigger( );
How can we improve it?π
1. Define some types:π
With Enum's we can limit the allowed values.
file: improved_type.sql
CREATE TYPE operation_t AS ENUM( 'INSERT', 'UPDATE', 'DELETE' ); CREATE TYPE table_t AS ENUM( 'history' );
We could have chosen to use a table with one unique key on a/the column (table_names) and a foreign key as reference. This is as practical.
2. Create our table:π
file: improved_table.sql
CREATE TABLE history( id SERIAL PRIMARY KEY, schema_name TEXT NOT NULL, table_name table_t NOT NULL, timestamp TIMESTAMP NOT NULL DEFAULT NOW( ), operation operation_t NOT NULL, who TEXT NOT NULL DEFAULT CURRENT_USER, new_val JSON NULL DEFAULT NULL, old_val JSON NULL DEFAULT NULL ); CREATE INDEX "u_logging-schema_name-table_name" ON history( schema_name, table_name, timestamp );
3. Create the user-defined-function:π
file: improved_function.sql
CREATE FUNCTION change_trigger() RETURNS trigger AS $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO history ( schema_name, table_name, operation, new_val ) VALUES ( LOWER( TG_TABLE_SCHEMA ), CAST( LOWER( TG_RELNAME ) AS table_t ), CAST( TG_OP AS operation_t ), row_to_json( NEW ) ); RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN INSERT INTO history ( schema_name, table_name, operation, new_val, old_val ) VALUES ( LOWER( TG_TABLE_SCHEMA ), CAST( LOWER( TG_RELNAME ) AS table_t ), CAST( TG_OP AS operation_t ), row_to_json( NEW ), row_to_json( OLD ) ); RETURN NEW; ELSIF TG_OP = 'DELETE' THEN INSERT INTO history ( schema_name, table_name, operation, old_val ) VALUES ( LOWER( TG_TABLE_SCHEMA ), CAST( LOWER( TG_RELNAME ) AS table_t ), CAST( TG_OP AS operation_t ), row_to_json( OLD ) ); RETURN OLD; END IF; END; $$ LANGUAGE 'plpgsql' SECURITY DEFINER;
4. Attach the trigger:π
file: improved_trigger.sql
CREATE TRIGGER history_{{ page.extra.table_name }}_t BEFORE INSERT OR UPDATE OR DELETE ON {{ page.extra.table_name }} FOR EACH ROW EXECUTE PROCEDURE change_trigger();
5. Register the table:π
file: improved_register.sql
ALTER TYPE table_t ADD VALUE '{{ page.extra.table_name }}';