Re: auto update dates

Check out some code I wrote at
http://www.greentechnologist.org/political/Voter/dev/5_trig.sql. There are
functions to keep a Created attribute invariant and update Modified as
well.

I just created a table Audit which exists only to provide common
attributes for a number of other tables. I get to re-use the same
functions for each table and just re-apply them as triggers where they are
needed.

Joshua b. Jore ; http://www.greentechnologist.org ; 10012 11010 11022
10202 1012 2122 11020 10202 10202 11002 1020 1012 11102 11102 11102 1201
11001 11002 10211 11020 10202 10202 11002 11021 1201 11010 11020 10211

On Thu, 23 May 2002, Rory Campbell-Lange wrote:

> Sorry - I seem to be bombarding the list. I AM reading my copy of Bruce
> Momjian's book quite closely, I promise!
>
> I'm trying to make a rule to automatically update the time, date and
> timestamp 'modified' fields when a row is updated.
>
> This is what I have tried:
>
>     create rule ideas_insert_datetime_mod as on update to ideas
>     do
>         update ideas
>         set d_modified = CURRENT_DATE,
>         t_modified = CURRENT_TIME,
>         ts_modified = CURRENT_TIMESTAMP
>
> This creates a cycle condition, and the rule does not operate.
>
> Do I instead need to write a function and then trigger it after each
> update action? I have 3 tables each with similar columns, with the same
> column names, which I wish to update in the same way.
>
> Thanks for any help.
> Rory
> --
> Rory Campbell-Lange
> <rory@campbell-lange.net>
> <www.campbell-lange.net>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>