Hi,
Citing "Jim C. Nasby" <decibel@decibel.org>:
> ON INSERT: force created and updated to be current_timestamp
> ON UPDATE: deny updated created. force updated to be set to
> current_timestamp
[snip]
> Does anyone have an example of the best way to handle this scenario?
Something along the lines of the following should work (but test first
anyways, though I have copied smaller parts of this from the definitions
in one of my databases here, I have made modifications to fit your
specific task, so typos/errors might have sneaked in):
create function update_trigger() returns trigger as
'begin
new.created := old.created;
new.updated := CURRENT_TIMESTAMP;
return new;
end;'
language 'plpgsql';
create trigger update_trigger BEFORE UPDATE ON your_table_name
FOR EACH ROW EXECUTE PROCEDURE update_trigger();
create function insert_trigger() returns trigger as
'begin
new.created := CURRENT_TIMESTAMP;
new.updated := CURRENT_TIMESTAMP;
return new;
end;'
language 'plpgsql';
create trigger insert_trigger BEFORE INSERT ON your_table_name
FOR EACH ROW EXECUTE PROCEDURE insert_trigger();
HTH,
Regards,
Daniel
