Re: Updating from update trigger - Mailing list pgsql-general

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
>