Thread: timestamp update field across multiple tables
Dear List, Thanks for those who helped me with my last question. I have a slightly related question: I have five tables which are really intended to be one big table, but has been adapted to comply with Postgres's (irritating)8kb tuple size limit (ver 7.0.3). I have 'modtime' fields in each of these tables which record the last time a record was updated. But I would like the behaviourto be such that a modtime field in each of these associated tables is updated when any of the tables are updated. And I would like all of the modtime fields to take the same value if possible. The primary/foreign keys for each of the tables do not necessarily have the same name. I've currently imagined a trigger for each table that updates every other table every time it is updated. But I've not hadmuch success executing SQL from inside a plpgsql function at the moment - and wouldn't it create a cycle of triggers byupdating a table's modtime, which in turn causes another modtime update in all the other tables, which in turn perpetuatesthe circle? Any help greatly appreciated, Thanks, Rajit
Rajit Singh wrote:
>I have five tables which are really intended to be one big table, but has be
>en adapted to comply with Postgres's (irritating) 8kb tuple size limit (
>ver 7.0.3).
>
>I have 'modtime' fields in each of these tables which record the last time a
> record was updated. But I would like the behaviour to be such that a m
>odtime field in each of these associated tables is updated when any of t
>he tables are updated. And I would like all of the modtime fields to ta
>ke the same value if possible.
If the rows in these five tables are meant to be all one, why have five
separate modtime fields? That breaks the "rules" against redundant data.
Just have it in one table and put triggers to set it in all five.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"For the LORD is good; his mercy is everlasting; and
his truth endureth to all generations."
Psalms 100:5
On Fri, 12 Jan 2001 05:56:43 Oliver Elphick wrote: >Rajit Singh wrote: >>I have five tables which are really intended to be one big table, but >>has been adapted to comply with Postgres's (irritating) 8kb tuple size >>limit (ver 7.0.3). >> >>I have 'modtime' fields in each of these tables which record the last >>time a record was updated. But I would like the behaviour to be such >>that a modtime field in each of these associated tables is updated when >>any of the tables are updated. And I would like all of the modtime >>fields to take the same value if possible. > >If the rows in these five tables are meant to be all one, why have five >separate modtime fields? That breaks the "rules" against redundant data. >Just have it in one table and put triggers to set it in all five. Why not just have one modtime field in the main table and just include the primary key in the other tables? It sounds like you're going to include records from each table based on the primary key anyway. There is no need to duplicate the modtime field across the other table if the columns in these tables all actually represent a single record. -- Anthony E. Greene <agreene@pobox.com> <http://www.pobox.com/~agreene/> PGP Key: 0x6C94239D/7B3D BD7D 7D91 1B44 BA26 C484 A42A 60DD 6C94 239D Chat: AOL/Yahoo: TonyG05 ICQ: 91183266 Linux. The choice of a GNU Generation. <http://www.linux.org/>