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

Ultimately I have resolved my trouble...

(THANK YOU FOR THE CODE Ian)

I not exactly sure what ultimately resolved the issue...

Had to be a "'" or something somewhere that I missed while copy past
from your original post...

Go figure...I know I dropped and recreated at least a dozen times

Anyway...it is working now with "exists"

I appreciate your reply and the use of your code

Jim

-----Original Message-----
From: Ian Harding [mailto:iharding(at)tpchd(dot)org]
Sent: Tuesday, May 02, 2006 2:05 PM
To: Hogan, James F. Jr.
Subject: Re: Audit Logs, Tables and Triggers using PLTCL (plain text)

It looks like the latest version of tcl has changed the "info exists"
command to "info exist"

Try changing that and recreating the functions.

- Ian

>>> "Hogan, James F. Jr." <JHogan(at)seton(dot)org> 05/01/06 3:19 PM >>>
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