Thread: on update set default

Hi All

Im want that on each insert, update to a table the column:
changed_on TIMESTAMP DEFAULT now()
will be set to now();

any one can hint me ?
I tried few rulles and triggers for hours with no luck :(

--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
http://sites.canaan.co.il
--------------------------

"Ben-Nes Michael" <miki@canaan.co.il> writes:

> Hi All
>
> Im want that on each insert, update to a table the column:
> changed_on TIMESTAMP DEFAULT now()
> will be set to now();
>
> any one can hint me ?
> I tried few rulles and triggers for hours with no luck :(

An AFTER trigger to do this should be pretty easy.  Why don't you post
what you've tried and we'll see where you went wrong?

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Doug McNaught <doug@wireboard.com> writes:
>> Im want that on each insert, update to a table the column:
>> changed_on TIMESTAMP DEFAULT now()
>> will be set to now();

> An AFTER trigger to do this should be pretty easy.

It had better be a BEFORE trigger; AFTER is too late to affect the row
that will be stored.  But I agree that this should be a very trivial
trigger to write.  The body need be no more than

    new.changed_on := now();
    return new;

> Why don't you post
> what you've tried and we'll see where you went wrong?

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Doug McNaught <doug@wireboard.com> writes:
>
> > An AFTER trigger to do this should be pretty easy.
>
> It had better be a BEFORE trigger; AFTER is too late to affect the row
> that will be stored.

Ack, you're right--I was thinking in terms of updating a field in a
different table (don't ask me why).  Thanks for the correction.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

On 2001-12-09 "Ben-Nes Michael" <miki@canaan.co.il> wrote:
>Im want that on each insert, update to a table the column:
>changed_on TIMESTAMP DEFAULT now()
>will be set to now();

In the PostgreSQL 7.1 Programmer's guide

http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html

I guess it's more than you need.

--
AXXESSIT ASA                  http://www.axxessit.no
Rolf Østvik / Test technician