Trigger for Audit Table

[Bill Moseley <moseley@xxxxxxxx>]

I'm asking for a sanity check:


This is a very simple audit table setup where I use a BEFORE UPDATE
trigger to save an existing record.

The table stores templates (for a CMS) and looks something like this:

    create table template (
        id                  SERIAL PRIMARY KEY,
        path                text UNIQUE NOT NULL,
        content             text NOT NULL,
        last_updated_time   timestamp(0) with time zone NOT NULL default now()
    );

And then an audit table:

    create table template_history (
        id                  SERIAL PRIMARY KEY,
        template_id         integer NOT NULL REFERENCES template ON DELETE CASCADE,
        path                text NOT NULL,
        content             text NOT NULL,
        last_updated_time   timestamp(0) with time zone NOT NULL
    );

(The "path" is not the primary key because the template's path
might get renamed (moved), but I still want to track its history.)


My trigger is very simple:

    CREATE OR REPLACE FUNCTION audit_template() RETURNS TRIGGER AS '
        BEGIN
            INSERT INTO template_history
                        ( template_id, path, content, last_updated_time, person )
                        select
                            id, path, content, last_updated_time, person
                        from
                            template where id = 1;

            RETURN NEW;
        END'
    language 'plpgsql';


    CREATE TRIGGER template_history_add BEFORE UPDATE ON template
        for each row execute procedure audit_template();


I realize this is a *BEFORE* UPDATE trigger, but I have this vague
memory of seeing a post stating that you can't be sure the existing
row has not been updated yet. Perhaps that was just a concern if
another trigger was to modify the row.  But, I can't seem to find that
post now which is why I'm asking for the sanity check.

Are there potential problems with this setup?


-- 
Bill Moseley
moseley@xxxxxxxx