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 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.
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 had much success executing SQL from inside a plpgsql function at the moment - and wouldn't it create a cycle of triggers by updating a table's modtime, which in turn causes another modtime update in all the other tables, which in turn perpetuates the 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
Import Notes
Reply to msg id not found: MessagefromRajitSinghsingh.raj@studychoice.comofFri12Jan2001104003GMT.20010112104003.A18207@studychoice.com | Resolved by subject fallback
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/>