Re: Trigger for Audit Table

Bill Moseley <moseley(at)hank(dot)org> writes:
> I'm asking for a sanity check:

> 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
> );

Why would you want ON DELETE CASCADE? Or for that matter to have a
foreign key here at all? Surely the point of an audit table is to
remember history. If the audit entries all disappear the instant
the main-table entry is deleted, it's not much of an audit tool.

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

This is not going to work because the row's not there yet (I won't
bother pointing out the thinko in the WHERE clause); and even if it did
work it'd be unnecessarily inefficient. Just use the NEW row that's
passed to the trigger:

INSERT INTO template_history(...) VALUES(NEW.id, NEW.path, ...)

If you have other BEFORE triggers on this table that can change the
NEW row, then it might be better to make this an AFTER trigger so it can
be sure the NEW row it sees won't change anymore. But AFTER triggers
are distinctly less efficient, so if you're not intending to add more
triggers then using a BEFORE trigger is probably the way to go.

regards, tom lane