Re: My brain hurts - update field based on value of another table's field

Ok, here's what they are

* area - arbitrary name for a collection of sites (optional)

* site name for a collection of buildings (may only be one building)
optional

* building - a single building not optional name for a collection of rooms

* zone - sub area of a building not optional name for a collection of rooms.

* rooms (not listed)


The main record here is Building. Areas and sites are optional ways of
grouping buildings. Zones and rooms are required.

Since the area and site are optional and arbitrary (you may change your
organizational chart) I need a way of updating the children of that record
to reflect the changes. I can do it easy enough in php, just don't know how
with postgres.

Here's an example of the php script. Probably won't indent properly...

$building_id is the current building
$sel_BuildingSite is the new site for this building

if ($but_UpdateBuilding) {
  $txt_buildingname=trim($txt_buildingname);
  if ($txt_buildingname) {
    $check=pg_Exec($connection,"select building_id from buildings where
building_id='$building_id';");
    if ($check) {
      if(pg_NumRows($check)==1) {
        $check=pg_Exec($connection,"
          update zones
          set zone_site='$sel_BuildingSite'
          where zone_building='$building_id';");
        $check=pg_Exec($connection,"
          update rooms
          set room_site='$sel_BuildingSite'
          where room_building='$building_id';");
        $check=pg_Exec($connection,"
          update buildings
          set building_name='$txt_buildingname',
          building_site='$sel_BuildingSite'
          where building_id='$building_id';");
      } else  {
        $error="Building ID Not found";
      }
    } else {
      $error="Error - check is null for this building_id!";
    }
  }
}

What I'd like to do is have the php script change the building_site field
value then have postgres update the zone_site and room_site fields by
itself. The more I read the docs (thus the brain pain) the more I'm
convinced I need a trigger and function. I just can't seem to figure out how
they work.

Yes, I will be moving to transactions and more error checking, just trying
to figure out how I'm going to do this before I get too involved 8)


"Lee Harr" <missive@frontiernet.net> wrote in message
news:9okp4i$2q58$1@news.tht.net...
> On Sun, 23 Sep 2001 03:56:50 GMT, Pat M <pmeloy@removethispart.home.com>
wrote:
> > I'm just a hobbyist so this is probably atroceous, but I'm trying to do
> > something like the following. Sorry its not in real SQL format. I know
how
> > to auto-update by referencing to a primary key of another table, but
what if
> > you want a field to change along with a non-unique field from a
different
> > table? Here's an example of what I mean
> >
> > table areas
> > -------------
> > area_id serial primary key
> > area_name text
> >
> > table sites
> > ----------
> > site_id serial primary key
> > site_name text
> > site_area int references areas on delete cascade
> >
> > table buildings
> > ------------
> > building_id serial primary key
> > building_name text
> > building_area int <--- needs to change when site_area changes
> > building_site int references sites on delete cascade
> >
>
> I think you would do this by _not_ having building_area at all.
> You have a building_site, which has a site_area. Is it possible
> to have a building_area that is _different_ from the site_area
> of the building_site? That does not make sense to me.
>
>
> > table zones
> > ------------
> > zone_id serial primary key
> > zone_name text
> > zone_area int  <--- needs to change when building_area changes
> > zone_site int  <--- needs to change when building_site changes
> > zone_building int references buildings on delete cascade
> >
>
> It is really difficult to help you not knowing what these "site" and
> "area" and "zone" things are.
>
>
> > None of the primary keys will be changing of course. But the area a
building
> > is in may change (area being an arbitrary designation, not municiple
> > boundaries), as may other fields as I work my way through building the
data.
> >
> > I know I can join things together in queries, avoiding all this, but it
gets
> > real confusing trying to join 12 tables, and slow... I want to be able
to
> > get the area from the buildings table and not have to join three tables
just
> > to find out what area it belongs to. Unless someone knows an easier way
than
> > select area_name from areas,sites,buildings where area_id=site_area and
> > site_id=building_id and building_id=1; Speed and easy queries are my
focus,
> > not disk space or ram savings.
> >
> > I looked at foreign keys, but they get uptight when the referenced field
> > isn't unique. On update cascade would have been wonderful 8(
> >
> > I looked at inheritance, but I don't think its what I had in mind.
> >
> > I'm thinking I have to delve into the horrors that are triggers and
> > functions...
> >
> > Any cool ideas floating around out there?
> >
> >