Dave Wedwick wrote:
> Hi!
>
> I have a table with an int4 field called inserttime. Regardless of what
> the user enters in this field, I want a trigger to put now() into it.
>
> What's the syntax for the trigger?
Sample:
CREATE TABLE t1 ( id serial PRIMARY KEY, inserttime integer, description
text );
CREATE FUNCTION t1_before_insert () RETURNS opaque AS ' BEGIN NEW.inserttime :=
date_part(''epoch'',now()); RETURN NEW; END;' LANGUAGE 'plpgsql';
CREATE TRIGGER t1_before_insert BEFORE INSERT ON t1 FOR EACH ROW EXECUTE PROCEDURE
t1_before_insert();
CREATE FUNCTION t1_before_update () RETURNS opaque AS ' BEGIN NEW.inserttime := OLD.inserttime;
RETURN NEW; END;' LANGUAGE 'plpgsql';
CREATE TRIGGER t1_before_update BEFORE UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE
t1_before_update();
These two triggers ensure that the field 'inserttime' is set to the number of seconds since Jan. 1st 1970 on
INSERT and will never be changed after. Close enough to what you want?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
