Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
You can do ALTER TABLE my_table INHERIT audit. Furthermore, in a DELETE trigger you don't have NEW, update the affected row instead with the timestamp and RETURN NULL. –  dezso Dec 9 '12 at 23:06
add comment

1 Answer

up vote 1 down vote accepted

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.