Re: Updating from update trigger

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