Re: 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