Re: Referencing columns of the fly in triggers

This seems like a great solution. The key is that in Tcl 'new' and 'old'
are
arrays that are referenced by field name (but the field name can be a
variable). This allows you to compare specific old and new fields at
runtime without knowing beforehand what the field names are. Also the
$TG_relatts auto-created variable is extremely useful.
Are there any plans to implement this same kind of functionality into
pl/pgsql? Or is there already and I just don't know about it?

James F
>
> ----- Original Message -----
> From: "Ian Harding" <ianh(at)tpchd(dot)org>
> To: <nospam_james(at)hcjb(dot)org(dot)ec>
> Cc: <pgsql-general(at)tpchd(dot)org>
> Sent: Tuesday, December 10, 2002 10:02 AM
> Subject: Re: [GENERAL] Referencing columns of the fly in triggers
>
>
> You already got a response in C, here is one in pltcl... I tell it which
> column is the key and the name of the table it is pointing at. That could
> probably be figured out instead of explicitly passed, but I didn't bother.
> It writes down all values on insert, the key only on delete, and the key
and
> updated values on update.
>
> 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);
>
>
> drop function tsp_audit_atrig();
> create function tsp_audit_atrig() returns opaque 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_employeeaudit_atrig on employee;
> create trigger trig_employeeaudit_atrig after insert or update or delete
on
> employee
> for each row execute procedure tsp_audit_atrig('employee',
> 'employeeid');
>
>
>
> Ian Harding
> Programmer/Analyst II
> Tacoma-Pierce County Health Department
> iharding(at)tpchd(dot)org
> (253) 798-3549
>
> >>> "James F." <nospam_james(at)hcjb(dot)org(dot)ec> 12/09/02 01:50PM >>>
> I would like to know if there is any way to create a common ON UPDATE
> trigger function that can be called from any table and be able to figure
out
> which field(s) changed. The difficulty is being able to reference at run
> time the column names of that table, short of hard-coding all of them. The
> new and old records allow you to reference the columns, but only if you
> already know the name of the column. Is there nothing equivalent to
> new[column_index] that would allow me to iterate through the columns
without
> knowing beforehand the column names? And then, given a certain column
index,
> to reference the name of that column?
>
> The goal of this trigger is to log all UPDATES to an audit log table, so
the
> table name, column name, and new column value are all needed to write to
the
> change log. Is there a better way of doing this?
>
> thanks for your help.
>
> James F
>