Thread: Trigger question

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?

Thanks!

One person suggested setting now() as default.

That would work sometimes, but I have a situation where the user program is
updating the default field with 0.  now() never gets into the field.

So, regardless of what the user enters, I want now() to be updated, and I
guess it would be via a trigger...

Thanks!

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?
>
> Thanks!

Dave,

Please look at the docs for creating triggers.
(http:/www.postgresql.org/docs/).
Hopefully, someone on this list with more experience with Update and
Insert triggers can give you some help in avoiding the classic problems,
such as infinite loops.

Let me ask the obvious question:
If the InsertTime field is not open to user input, why are you providing
users with a data-entry interface for this field at all?  Why not hide
it, or display it read-only?
                -Josh Berkus
-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology      josh@agliodbs.com   and data management solutions       (415) 565-7293  for law firms, small
businesses      fax  621-2533   and non-profit organizations.       San Francisco
 

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

Actually, a default of now() would be perfectly providing you created a rule to
trap insert's and updates and remove the setting for that time/date from being
inserted so as to have the db use the dfault which I think is far easier that
making a silly trigger for something so simple.

Dan

Dave Wedwick wrote:

> One person suggested setting now() as default.
>
> That would work sometimes, but I have a situation where the user program is
> updating the default field with 0.  now() never gets into the field.
>
> So, regardless of what the user enters, I want now() to be updated, and I
> guess it would be via a trigger...
>
> Thanks!
>
> 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?
> >
> > Thanks!