Inherit audit columns and triggers

Question

Background

I have a database with dozens of tables, some of which should have audit columns.

Problem

I'd like to avoid the drudgery of creating audit columns manually, and write the update and delete triggers once.

Code

There are several different pieces of code that must come together.

Audit Table

The following code creates an audit table and city table:

CREATE TABLE audit (
  created timestamp with time zone NOT NULL DEFAULT ('now'::text)::timestamp with time zone,
  deleted timestamp with time zone,
  updated timestamp with time zone
);

Example Table

A simple table to serve as an example of the problem:

CREATE TABLE city (
  name       text,
  population real,
  altitude   int
);

Parent Table Trigger

The following code creates a database trigger and a delete prevention rule on the audit table:

CREATE FUNCTION audit_delete() RETURNS trigger AS
$audit_delete$
BEGIN
  OLD.deleted := current_timestamp;
  RETURN NULL;
END;
$audit_delete$
LANGUAGE plpgsql;

CREATE TRIGGER audit_delete BEFORE DELETE ON audit
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

Adding Columns

Inheritance requires columns in the child table to match the parent table and must be created manually. Such as:

for $name in (SELECT list of tables); do
  ALTER TABLE $name ADD COLUMN
    created timestamp with time zone NOT NULL DEFAULT ('now'::text)::timestamp with time zone;
  ALTER TABLE $name ADD COLUMN
    deleted timestamp with time zone;
  ALTER TABLE $name ADD COLUMN
    updated timestamp with time zone;
done

There's probably a way to detect and auto-add the missing columns using the information schema.

Establish Inheritance Hierarchy

The following code applies the inheritance to the given table:

ALTER TABLE city INHERIT audit;

Insert Data

Add some data into the database:

INSERT INTO city ('Vancouver', 603502, 10 );
INSERT INTO city ('Seattle', 620887, 10 );
INSERT INTO city ('La Rinconada', 30000, 5100 );
INSERT INTO city ('Jericho', 20300, -260 );

Delete Data

Remove some data:

DELETE FROM city WHERE name = 'Jericho';

At this point, I was expecting the deleted column to contain the date for the value of "Jericho". Instead, the row as actually removed from the database.

The trigger for audit_delete() didn't fire.

Questions

1. What is the optimal (or usual) way to create a trigger that sets the deleted date on delete queries for all tables that inherit from the audit table (using PostgreSQL 9.2)?
2. (Optional) Is this a good use for triggers?

Thank you!

Related Links

• http://projects.nocternity.net/index.py/en/psql-inheritance
• http://www.postgresql.org/docs/current/static/tutorial-inheritance.html
• https://stackoverflow.com/questions/8469116/how-can-i-make-a-trigger-to-use-inheritance-with-that-tables
• http://tapoueh.org/blog/2010/11/24-dynamic-triggers-in-plpgsql.html
• http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
• http://archives.postgresql.org/pgsql-general/2009-08/msg00026.php
• http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html

Answer 1

From http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html:

The DELETE and UPDATE triggers need to be on the child tables. An operation on a child doesn't fire the triggers of the parent.

As such, the following code must also be executed:

CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

The audit_delete() function won't work as written in the question: setting the deleted date is not possible using OLD.deleted = current_timestamp. Instead, assuming that the table has a primary key field of id:

CREATE OR REPLACE FUNCTION audit_delete()
  RETURNS trigger AS
$BODY$
BEGIN
  EXECUTE
    'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;