Audit Logs, Tables and Triggers using PLTCL

I have been playing with some code I found at a post by Ian Harding on Oct 14^th 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