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 }}';