Обсуждение: Auto-update a field when record is changed

Folks,
I was wandering if there was a feasible way to automatically update a field 
in a table, say 'revision_date' in a record whenever any other field in the 
record is changed.  My attempts to use a trigger caused repeating loops and 
bombed with error.  I would like to be able to update such a field 
automatically anytime a record was updated.  Any help would be appreciated.

Thanks,

Stuart

On Fri, 22 Aug 2003, Stuart wrote:

> Folks,
>
>     I was wandering if there was a feasible way to automatically update a field
> in a table, say 'revision_date' in a record whenever any other field in the
> record is changed.  My attempts to use a trigger caused repeating loops and
> bombed with error.  I would like to be able to update such a field
> automatically anytime a record was updated.  Any help would be appreciated.

That seems like a job for a before trigger and changing the to be
update/inerted record before the action occurs in general.  Don't
use an update statement but change the record variables given as the NEW
row.

> On Fri, 22 Aug 2003, Stuart wrote:
> 
> > Folks,
> >
> >     I was wandering if there was a feasible way to automatically update a field
> > in a table, say 'revision_date' in a record whenever any other field in the
> > record is changed.  My attempts to use a trigger caused repeating loops and
> > bombed with error.  I would like to be able to update such a field
> > automatically anytime a record was updated.  Any help would be appreciated.


Unless I'm misunderstanding you, this is really easy. Here's what
I use in nearly every database I build:

create or replace function timestamp_fn() returns opaque as '       begin       NEW.moddate = now();       return NEW;
    end
 
' language 'plpgsql';

create table blah (
...       createdate timestamp default now(),       moddate timestamp,

create trigger blah_timestamp_tr before insert or update on blah       for each row execute procedure timestamp_fn();


Make the obvious changes for only doing this on updates.

Or am I misunderstanding your goal?

-j

-- 
Jamie Lawrence                                        jal@jal.org
"One of the great things about books is that sometimes there 
are some fantastic pictures."  - George H. W. Bush