Обсуждение: 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.