pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAudit Logs, Tables and Triggers using PLTCL
5+ messages / 3 participants
[nested] [flat]
* Audit Logs, Tables and Triggers using PLTCL
@ 2006-05-01 21:39 Hogan, James F. Jr. <JHogan@seton.org>
0 siblings, 0 replies; 5+ messages in thread
From: Hogan, James F. Jr. @ 2006-05-01 21:39 UTC (permalink / raw)
To: pgsql-admin; +Cc: iharding@tpchd.org
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
<mailto:iharding@DOMAIN.HIDDEN> >
* To: <dev ( at ) archonet ( dot ) com <mailto:dev@DOMAIN.HIDDEN>
>, <cain ( at ) cshl ( dot ) org <mailto:cain@DOMAIN.HIDDEN> >
* 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Audit Logs, Tables and Triggers using PLTCL (plain text)
@ 2006-05-01 22:19 Hogan, James F. Jr. <JHogan@seton.org>
0 siblings, 2 replies; 5+ messages in thread
From: Hogan, James F. Jr. @ 2006-05-01 22:19 UTC (permalink / raw)
To: pgsql-admin; +Cc: iharding@tpchd.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Audit Logs, Tables and Triggers using PLTCL (plain text)
@ 2006-05-02 02:44 Bruno Wolff III <bruno@wolff.to>
parent: Hogan, James F. Jr. <JHogan@seton.org>
1 sibling, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2006-05-02 02:44 UTC (permalink / raw)
To: Hogan, James F. Jr. <JHogan@seton.org>; +Cc: pgsql-admin; iharding@tpchd.org
On Mon, May 01, 2006 at 17:19:24 -0500,
"Hogan, James F. Jr." <JHogan@seton.org> wrote:
> Sorry my original post was in HTML format ...Damn Outlook
Actually it was multipart/alternative with both text/plain and text/html
parts. So while the size of the message was larger than need be, those of
us that set are mail readers to not display text/html parts got to see the
text/plain part.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Audit Logs, Tables and Triggers using PLTCL (plain text) RESOLVED
@ 2006-05-02 19:58 Hogan, James F. Jr. <JHogan@seton.org>
0 siblings, 0 replies; 5+ messages in thread
From: Hogan, James F. Jr. @ 2006-05-02 19:58 UTC (permalink / raw)
To: Ian Harding <iharding@tpchd.org>; pgsql-admin
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@tpchd.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@seton.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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Audit Logs, Tables and Triggers using PLTCL (plain text)
@ 2006-05-03 08:13 Manlio Perillo <manlio_perillo@libero.it>
parent: Hogan, James F. Jr. <JHogan@seton.org>
1 sibling, 0 replies; 5+ messages in thread
From: Manlio Perillo @ 2006-05-03 08:13 UTC (permalink / raw)
To: Hogan, James F. Jr. <JHogan@seton.org>; +Cc: pgsql-admin; iharding@tpchd.org
Hogan, James F. Jr. ha scritto:
> 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
>
I have just released a similar project, pglog:
http://developer.berlios.de/projects/pglog/
http://svn.berlios.de/svnroot/repos/pglog/trunk/
The functions are written in PL/Python and there is support for
reverting selected changes.
Regards Manlio Perillo
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-05-03 08:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-01 21:39 Audit Logs, Tables and Triggers using PLTCL Hogan, James F. Jr. <JHogan@seton.org>
2006-05-01 22:19 Audit Logs, Tables and Triggers using PLTCL (plain text) Hogan, James F. Jr. <JHogan@seton.org>
2006-05-02 02:44 ` Re: Audit Logs, Tables and Triggers using PLTCL (plain text) Bruno Wolff III <bruno@wolff.to>
2006-05-03 08:13 ` Re: Audit Logs, Tables and Triggers using PLTCL (plain text) Manlio Perillo <manlio_perillo@libero.it>
2006-05-02 19:58 Re: Audit Logs, Tables and Triggers using PLTCL (plain text) RESOLVED Hogan, James F. Jr. <JHogan@seton.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox