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
I first thought of doing an ON INSERT INSTEAD rule that would ignore
NEW.created and NEW.updated, but it seems inconvenient to have to change
the rule every time the table definition, and I'm not sure if this would
properly handle the SERIAL that I have defined (the rule would need to
include the serial in the insert, but then would the default work?). So
now I'm thinking of doing an ON INSERT INSTEAD UPDATE SET created =
current_timestamp WHERE id = NEW.id, though again I'm not sure if the
serial field (id) would be handled properly.
Does anyone have an example of the best way to handle this scenario?
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"