Re: creating audit tables

Here's what I do... It's not pretty but it works.

create table auditlog (
auditwhen timestamp not null default CURRENT_TIMESTAMP,
auditwhat char(10) not null,
audittable varchar not null,
auditkeyval int not null,
auditfield varchar not null,
oldval text null,
newval text null);

CREATE OR REPLACE FUNCTION "tsp_audit_atrig" () RETURNS trigger AS '

if {[string match $TG_op INSERT]} {
foreach field $TG_relatts {
if {[info exists NEW($field)]} {
set sql "insert into auditlog (auditwhat, audittable,
auditkeyval, "
append sql "auditfield, newval) "
append sql "values (''INSERT'', ''$1'', ''$NEW($2)'',
''$field'', "
append sql "''$NEW($field)'')"
spi_exec "$sql"
}
}
} elseif {[string match $TG_op DELETE]} {
foreach field $TG_relatts {
if {[info exists OLD($field)]} {
set sql "insert into auditlog (auditwhat, audittable,
auditkeyval, "
append sql "auditfield, oldval) "
append sql "values (''DELETE'', ''$1'', ''$OLD($2)'',
''$field'', "
append sql "''$OLD($field)'')"
spi_exec "$sql"
}
}
} elseif {[string match $TG_op UPDATE]} {
foreach field $TG_relatts {
# Was data changed or is this the key field?

if {([info exists NEW($field)] &&
[info exists OLD($field)] &&
![string match $OLD($field) $NEW($field)])} {
set sql "insert into auditlog (auditwhat, audittable,
auditkeyval, "
append sql "auditfield, oldval, newval) "
append sql "values (''UPDATE'', ''$1'', ''$NEW($2)'',
''$field'', "
append sql "''$OLD($field)'', ''$NEW($field)'')"
spi_exec "$sql"

# Is this a field replacing a null?

} elseif {[info exists NEW($field)] && ![info exists
OLD($field)]} {
set sql "insert into auditlog (auditwhat, audittable,
auditkeyval, "
append sql "auditfield, newval) "
append sql "values (''UPDATE'', ''$1'', ''$NEW($2)'',
''$field'', "
append sql "''$NEW($field)'')"
spi_exec "$sql"

# Is this a field being replaced with null?

} elseif {![info exists NEW($field)] && [info exists
OLD($field)]} {
set sql "insert into auditlog (auditwhat, audittable,
auditkeyval, "
append sql "auditfield, oldval) "
append sql "values (''UPDATE'', ''$1'', ''$NEW($2)'',
''$field'', "
append sql "''$OLD($field)'')"
spi_exec "$sql"

}
}
}

return "OK"

' LANGUAGE 'pltcl';

drop trigger trig_timecardaudit_atrig on timecard;
CREATE TRIGGER "trig_timecardaudit_atrig" AFTER INSERT OR DELETE OR
UPDATE ON "timec
ard" FOR EACH ROW EXECUTE PROCEDURE "tsp_audit_atrig" ('timecard',
'timecardid');

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding(at)tpchd(dot)org
Phone: (253) 798-3549
Pager: (253) 754-0002

>>> Scott Cain <cain(at)cshl(dot)org> 10/14/04 11:19 AM >>>
Heck! So much for feeling close. It is somewhat frustrating to me that
such an obviously useful tool (having and using audit tables) should be
so difficult to implement. I thought I had a reasonable chance of doing
it in plpgsql because I've written functions in that before--I have no
idea how to do it in tkl.

If someone would show me a simple example for doing this for one table,
I will happily make available the script I am writing that will generate
audit tables and the functions and triggers for using them
automatically, given any ddl file. It is based on the Perl module
SQL::Translator.

Thanks,
Scott

On Thu, 2004-10-14 at 14:07, Richard Huxton wrote:
> Scott Cain wrote:
> > I feel like I am getting very close, but I am still not quite there.
I
> > rewrote the trigger function below to use execute, but now I get the
> > following error:
> >
> > ERROR: OLD used in query that is not in a rule
> > CONTEXT: PL/pgSQL function "audit_update" line 5 at execute
statement
> >
> > It seems that I am not able to use OLD in this context, but that is
> > exactly what I need to do, to get the contents of the old row in the
> > original table to put it in the audit table. Here is the function
now:
> >
> > CREATE FUNCTION audit_update() RETURNS trigger
> > AS '
> > DECLARE
> > audit_table text;
> > BEGIN
> > audit_table = ''audit_''||TG_RELNAME;
> > EXECUTE ''INSERT INTO ''
> > ||quote_ident(audit_table)
> > ||'' VALUES (''
> > ||OLD.*
> > ||'',''
> > ||now()
> > ||'',''''U'''')'';
> > return NEW;
> > END
> > '
> > LANGUAGE plpgsql;
>
> Looks like people were fixing your errors, not looking at what you
were
> trying to do. Apologies, but it's easy to fixate on an error message.
>
> Unless something is changing in 8.0 you're using the wrong tool for
the
> job here. Plpgsql isn't good at dynamic queries, and can't unwrap OLD
> for you. Try a different language - tcl would be an obvious choice.
--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain(at)cshl(dot)org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster