Thread: logging table changes
hi, how can i "easily" log changes to individual fields in a table without naming these fields explicitly? i looked at the auditing example in http://www.postgresql.org/docs/8.3/static/plpgsql-trigger.html, which looks promising, but i can't think of a way to "easily" query the table when i want to know when a certain field changed. i was thinking of adding a field in the audit table which contains the names of all the fields which changed, but i don't know who to "easily" get these names. for some reason i think that there might already exist well known solutions for my problem so i dare to ask here. thx for advice matthias
Matthias Leopold wrote: > hi, > > how can i "easily" log changes to individual fields in a table without > naming these fields explicitly? i looked at the auditing example in > http://www.postgresql.org/docs/8.3/static/plpgsql-trigger.html, which > looks promising, but i can't think of a way to "easily" query the table > when i want to know when a certain field changed. i was thinking of > adding a field in the audit table which contains the names of all the > fields which changed, but i don't know who to "easily" get these names. > for some reason i think that there might already exist well known > solutions for my problem so i dare to ask here. > > thx for advice > > matthias > > Postgres 9.0 will allow you to execute trigger only when the column of your choice is changed: http://www.postgresql.org/docs/9.0/static/sql-createtrigger.html Look at the "WHEN" part. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
But how is that significantly better than an update trigger that
includes:
If old.value <> new.value then
do something
End if
RobR
Rob Richardson wrote: > > But how is that significantly better than an update trigger that > includes: > > If old.value <> new.value then > do something > End if > > RobR > > It's better because the new version will not fire unless the "WHEN" condition is satisfied, which means that you will save a few trigger invocations, which should improve the performance. The present method would fire every time, and check for the values. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
So far in this thread, I haven't seen anybody answer what I consider to be a very important question about the original poster's requirements: Why are you updating the history table???? If you update the history table, you lose old history. Instead, I think you should be adding a new record to the history table for every alteration made to the old one. The history table would have to contain a field for the type of change that was made (insert, update or delete). RobR
On Thu, Sep 9, 2010 at 10:48 AM, Rob Richardson <Rob.Richardson@rad-con.com> wrote: > > If old.value <> new.value then > do something > End if If old.value IS DISTINCT FROM new.value THEN --do something End if This version will produce expected results when dealing with nulls. I learned this the hard way. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Am 2010-09-09 20:31, schrieb Rob Richardson: > So far in this thread, I haven't seen anybody answer what I consider to > be a very important question about the original poster's requirements: > > Why are you updating the history table???? > > If you update the history table, you lose old history. Instead, I think > you should be adding a new record to the history table for every > alteration made to the old one. The history table would have to contain > a field for the type of change that was made (insert, update or delete). > > RobR > thx for all the answers so far I wasn't thinking about updating the history table. "adding a field in the audit table which contains the names of all the fields which changed" meant adding this field to the history table originally, so it gets filled along with all the others when every change to a row is recorded. HOW to "easily" get the values to put there i don't know, i don't even know if this approach is sensible at all. the solutions in the other posts require me to know the field to audit in advance. i'm searching for a "easy" solution where "everything" is logged and i can make simple queries to the history table for changes in ANY field. i must admit i'm not too familiar with triggers and even functions, so maybe i'm heading in the wrong direction or i'm simply asking too much. i just thought that i can't be the only one who was looking for this kind of functionality (which of course doesn't mean that there is an "easy" solution) i hope i could make myself clear matthias