Re: Updating from update trigger

Answering my own half-way thought solution: of course you won't avoid
the infinite loop if you take the location info in a separate table,
cause if you do, then you can't update the location by just updating the
cd table.
So having this solution invalid, I just can think of using some query
rewriting rules.
I have absolutely no experience with this, so check out the docs:
http://www.postgresql.org/docs/7.3/static/rules.html

HTH,
Csaba.

On Thu, 2003-07-31 at 14:26, Csaba Nagy wrote:
> You should probably rethink the application logic.
> If there's no way to do it differently, you might be able to place the
> location details in a separate table, and update them there - this way
> on update of the "cd" table you will update also the "cd_locations"
> table, avoiding the loop.
> You should join the 2 tables using a sequence generated key, which
> should be never changed once allocated to a cd/location pair.
>
> In any case this trigger approach will not work correctly in multi-user
> environments, unless you implement some kind of locking for the
> insert/update access to the cd table. In the best case you will get
> rolled back transactions, worst case - the locations get f**ed up.
>
> HTH,
> Csaba.
>
> On Thu, 2003-07-31 at 12:46, Jonathan Mezach wrote:
> > Is there anyone who has a solution to this problem?
> >
> > Greetings,
> >
> > Jonathan Mezach aka Taz
> > News Writer for HL2Zone (www.hl2zone.net)
> >
> >
> > -----Oorspronkelijk bericht-----
> > Van: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] Namens Jonathan Mezach
> > Verzonden: vrijdag 25 juli 2003 21:07
> > Aan: pgsql-general@postgresql.org
> > Onderwerp: [GENERAL] Updating from update trigger
> >
> > Hello,
> >
> > I'm writing a program with which I can keep track of all the CD's I
> > have. As a backend, I'm using Postgres to store my data. Now, I have
> > this trigger on a table (a scheme of the database at the bottom) which
> > executes a function called movecdsdown. From this function, the same
> > table is updated. This gives no problems when the trigger is called
> > because of an insert, and it does what it should do. However, I also
> > want to update the table when that table is updated. This of course
> > gives me problems, cause it will begin an endless loop. So, somehow I
> > need to disable the trigger, or the function must now if the function
> > was already called, but I have no idea on how to do this.
> >
> > Here is a scheme of the database I'm using:
> >
> > cd            location        locationdetails    rental
> > renter
> >
> > title (pk)        location (pk)    fk_cd (pk)
> > startdate (pk)    firstname (pk)
> > creationdate    width            fk_location (pk)
> > followupnr (pk)    lastname (pk)
> > type            height        part            fk_cd
> > phonenumber
> > fk_storedloc    parts            column        fk_firstname
> > email
> > fk_currentloc    fitshalfsize    row            fk_lastname
> > size            stack
> > enddate
> >             homelocation
> >
> > I think the foreign and primary keys speak for themselves. The trigger
> > is defined on the locationdetails table. The trigger function then
> > updates the row field of all rows where row > NEW.row. It works
> > perfectly for INSERT, but it doesn't work for UPDATE.
> >
> > Greetings,
> >
> > Jonathan Mezach
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> >       subscribe-nomail command to majordomo@postgresql.org so that your
> >       message can get through to the mailing list cleanly
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>