From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | "Jim C(dot) Nasby" <decibel(at)decibel(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: handing created and updated fields |
Date: | 2005-01-10 12:15:32 |
Message-ID: | 20050110121527.GC18809@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Jan 10, 2005 at 05:28:47AM -0600, Jim C. Nasby wrote:
> I think I saw something posted about this recently, but I can't find it
> in the archives now. :(
>
> I want to have created and updated fields in a table that are kept
> up-to-date by the database and can't be changed accidentally. I think
> this can be done with rules, but I'm not sure of the best way to do it.
> Basically:
>
> ON INSERT: force created and updated to be current_timestamp
> ON UPDATE: deny updated created. force updated to be set to
> current_timestamp
Nope, you want triggers. I don't remember the syntax, but the basic
structure would be...
ON INSERT DO TRIGGER set_timestamp
ON UPDATE DO TRIGGER update_timestamp
set_timestamp()
NEW.created = now()
NEW.updated = now()
update_timestamp()
if OLD.created <> NEW.created then ERROR
NEW.updated = now()
Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Rae | 2005-01-10 12:26:49 | Re: PostgreSQL 8.0.0 Release Candidate 4 |
Previous Message | Antony Paul | 2005-01-10 12:13:31 | ORDER BY in UNION query |