Audit Logs, Tables and Triggers using PLTCL (plain text)

Sorry my original post was in HTML format ...Damn Outlook



I have been playing with some code I found at a post by Ian Harding on
Oct 14th 04





Insert, Delete Logging work great however



But when I do an Update



I get the following error:



Local Windows install of PostgreSQL v8.1.3

ActiveTCL v8.4.13





(ERROR: wrong # args: should be "info exist varName"

CONTEXT: wrong # args: should be "info exist varName"

  While executing

"info exists

"

  (procedure "__PLTcl_proc_17515Ptrigger_17025 line 68)

Invoked from within .....)





Can someone please help me to understand why this error is occurring and
perhaps tell me how to fix it



Here is the Code I trimmed from Ian on his October Post:



From: "Ian Harding" <iharding ( at ) tpchd ( dot ) org>
To: <dev ( at ) archonet ( dot ) com>, <cain ( at ) cshl ( dot ) org>
Subject: Re: creating audit tables
Date: Thu, 14 Oct 2004 11:41:10 -0700

------------------------------------------------------------------------
--------

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