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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
