I have a database with dozens of tables, some of which should have audit columns.
I'd like to avoid the drudgery of creating audit columns manually, and write the
delete triggers once.
There are several different pieces of code that must come together.
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 );
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();
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;
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 );
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.
- What is the optimal (or usual) way to create a trigger that sets the deleted date on
deletequeries for all tables that inherit from the audit table (using PostgreSQL 9.2)?
- (Optional) Is this a good use for triggers?