Re: Trigger for Audit Table

You can/should create it as an AFTER UPDATE trigger. The OLD row will
contain the previous values.

eg:
INSERT INTO template_history
( template_id, path, content, last_updated_time, person )
values
(OLD.id, OLD.path, OLD.content, OLD.last_updated_time, OLD.person);

On Mar 9, 2:45 pm, mose(dot)(dot)(dot)(at)hank(dot)org (Bill Moseley) wrote:

> 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
> mose(dot)(dot)(dot)(at)hank(dot)org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings