Re: Trigger question

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(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com