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@tpchd.org
Phone: (253) 798-3549
Pager: (253) 754-0002

>>> Scott Cain <cain@cshl.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@cshl.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