Re: Referencing columns of the fly in triggers - Mailing list pgsql-general

 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@tpchd.org>
> To: <nospam_james@hcjb.org.ec>
> Cc: <pgsql-general@tpchd.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@tpchd.org
> (253) 798-3549
>
> >>> "James F." <nospam_james@hcjb.org.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
>