From: Jan Wieck <janwieck@Yahoo.com>
Message-Id: <200102141951.OAA04373@jupiter.jw.home>
Subject: Re: Trigger question
In-Reply-To: <3A8754BB.382CBDBE@wedwick.com> from Dave Wedwick at "Feb 11,
 2001
	08:12:59 pm"
To: Dave Wedwick <news@wedwick.com>
Date: Wed, 14 Feb 2001 14:51:40 -0500 (EST)
CC: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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