Postgres Pro
Downloads
Home   >   mailing lists

Thread: Audit Logs, Tables and Triggers using PLTCL

Audit Logs, Tables and Triggers using PLTCL

From
"Hogan, James F. Jr."
Date:

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:

 

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