Date: Sat, 23 Aug 2003 01:16:53 -0400
From: Jamie Lawrence <jal@jal.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Auto-update a field when record is changed
Message-ID: <20030823051653.GA6038@clueinc.net>
References: <200308222332.47526.sfbarbee@yahoo.com>
	<20030822215217.L6822-100000@megazone.bigpanda.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20030822215217.L6822-100000@megazone.bigpanda.com>
User-Agent: Mutt/1.3.28i
Organization: clue inc


> 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