Re: Referencing columns of the fly in triggers
От | Ian Harding |
---|---|
Тема | Re: Referencing columns of the fly in triggers |
Дата | |
Msg-id | sdf59209.023@mail.tpchd.org обсуждение исходный текст |
Ответ на | Referencing columns of the fly in triggers ("James F" <nospam_james@hcjb.org.ec>) |
Список | pgsql-general |
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 itis pointing at. That could probably be figured out instead of explicitly passed, but I didn't bother. It writes downall 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 tableand be able to figure out which field(s) changed. The difficulty is being able to reference at run time the column namesof that table, short of hard-coding all of them. The new and old records allow you to reference the columns, but onlyif you already know the name of the column. Is there nothing equivalent to new[column_index] that would allow me to iteratethrough the columns without knowing beforehand the column names? And then, given a certain column index, to referencethe 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 valueare all needed to write to the change log. Is there a better way of doing this? thanks for your help. James F
В списке pgsql-general по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: MD5 passwords explained (was Re: md5 hash question (2))