Обсуждение: Updating a date field

I have a date field called "updated" with a default of now.
When I INSERT data it the field displays today's date.
My problem is when I UPDATE the row it doesn't change unless
I update the field manually like this:
UPDATE person SET email='doe@foo.com',updated='now()' WHERE
person_id=614486;
Is there an automatic way?
I tried doing it with a rule, which returned
ERROR:  query rewritten 10 times, may contain cycles
I tried a CONSTRAINT and TRIGGER without any luck either.
Can anyone help?

On Mon, 19 Aug 2002, Dan C wrote:

> I have a date field called "updated" with a default of now.
> When I INSERT data it the field displays today's date.
> My problem is when I UPDATE the row it doesn't change unless
> I update the field manually like this:
> UPDATE person SET email='doe@foo.com',updated='now()' WHERE
> person_id=614486;
> Is there an automatic way?
> I tried doing it with a rule, which returned
> ERROR:  query rewritten 10 times, may contain cycles
> I tried a CONSTRAINT and TRIGGER without any luck either.

A before trigger should do it for you (untested)

create function setupdated() returns opaque as '
begin
 NEW.updated := now();
 return NEW;
end;'
language 'plpgsql';

create trigger person_setupdated before update on
 person for each row execute procedure setupdated();

On 19 Aug 2002 at 14:17, Stephan Szabo wrote:
> On Mon, 19 Aug 2002, Dan C wrote:
>
> > I have a date field called "updated" with a default of now.
> > When I INSERT data it the field displays today's date.
> > My problem is when I UPDATE the row it doesn't change unless
> > I update the field manually like this:
> > UPDATE person SET email='doe@foo.com',updated='now()' WHERE
> > person_id=614486;
> > Is there an automatic way?
> > I tried doing it with a rule, which returned
> > ERROR:  query rewritten 10 times, may contain cycles
> > I tried a CONSTRAINT and TRIGGER without any luck either.
>
> A before trigger should do it for you (untested)
>
> create function setupdated() returns opaque as '
> begin
>  NEW.updated := now();
>  return NEW;
> end;'
> language 'plpgsql';
>
> create trigger person_setupdated before update on
>  person for each row execute procedure setupdated();

I do this too (with CURRENT_TIMESTAMP), and also include the last
person who has updated the row (current_user). You can also set these
fields to DEFAULT values and use the trigger to update them when the
row is updated.