Re: Updating column on row update

On Sunday 22 November 2009 12:09:04 pm Thom Brown wrote:
> 2009/11/22 Aaron Burnett <aburnett@bzzagent.com>
>
> > this is how I do it if this helps:
> >
> > column_name timestamp without time zone NOT NULL DEFAULT
> > ('now'::text)::timestamp(6) without time zone
> >
> > Hi Aaron.  Thanks for the reply, but that would only insert the current
>
> date upon insertion into the table, not when the row is updated.
>
> For example
>
> CREATE TABLE timetest(
> id SERIAL NOT NULL,
> stuff text,
> stamp timestamp NOT NULL DEFAULT now()
> );
>
> INSERT INTO timetest (stuff) VALUES ('meow');
>
> The table would contain:
>
>  id | stuff |           stamp
> ----+-------+----------------------------
>   1 | meow  | 2009-11-22 20:04:51.261739
>
> But then I'd execute:
>
> UPDATE timetest SET stuff = 'bark' WHERE id = 1;
>
>  id | stuff |           stamp
> ----+-------+----------------------------
>   1 | bark  | 2009-11-22 20:04:51.261739
>
> You can see the time hasn't changed.  But I'd want that stamp column to
> update to the current time without referring to that column in the update
> statement.
>
> Thanks
>
> Thom

You will need to use an UPDATE trigger with associated function.

--
Adrian Klaver
aklaver@comcast.net