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: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] Namens Jonathan Mezach > > Verzonden: vrijdag 25 juli 2003 21:07 > > Aan: [EMAIL PROTECTED] > > 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 [EMAIL PROTECTED] so that your > > message can get through to the mailing list cleanly > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
