Thread: avoiding endless loop in an UPDATE trigger
Hello, I am attempting to write a trigger function or rule in pl/pgsql that runs on UPDATE to a table named "nodes". From inside this trigger, I would like to UPDATE the same "nodes" table. How can I do this without entering into an endless loop? At the moment, I have a trigger which does the following: <snip> UPDATE pg_class SET reltriggers = 0 WHERE relname = ''nodes''; </snip> At the end of the function, I again update pg_class.reltriggers to its previous value, thus reenabling triggers. The problem with this is that it disables all of my FOREIGN KEY constraints during function execution, which is undesirable. I am sure there must be a more elegant way to accomplish this, but I have not found it. Could anyone offer some advice? Thanks! Regards, Norm -------------------------------------- Norman Clarke Combimatrix Corp Software Development Harbour Pointe Tech Center 6500 Harbour Heights Pkwy, Suite 301 Mukilteo, WA 98275 tel: 425.493.2240 fax: 425.493.2010 --------------------------------------
On Mon, 26 Feb 2001, Norman J. Clarke wrote: > The problem with this is that it disables all of my FOREIGN KEY > constraints during function execution, which is undesirable. Foreign Key constraints are implemented as triggers, IIRC. I'll let you figure out the implication(s) yourself. -- Dominic J. Eidson "Baruk Khazad! Khazad ai-menu!" - Gimli ------------------------------------------------------------------------------- http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
Right. I already figured this out, which is why in my email I said "the problem with this is..." If you have any suggestions on how to avoid getting into this kind of loop without disabling triggers (so that my foreign keys still work) I'd very much appreciate your suggestions. Thanks! Norman On Mon, 26 Feb 2001, Dominic J. Eidson wrote: > On Mon, 26 Feb 2001, Norman J. Clarke wrote: > > > The problem with this is that it disables all of my FOREIGN KEY > > constraints during function execution, which is undesirable. > > Foreign Key constraints are implemented as triggers, IIRC. I'll let you > figure out the implication(s) yourself. > > >
On Mon, Feb 26, 2001 at 11:53:51AM -0800, Norman J. Clarke wrote: > Hello, > > I am attempting to write a trigger function or rule in pl/pgsql that runs > on UPDATE to a table named "nodes". From inside this trigger, I would like > to UPDATE the same "nodes" table. How can I do this without entering into > an endless loop? What I did is add a no_update bool to the table and test: if new.no_update = true then new.no_update = false; else <do your update here> new.no_update = true; endif -- Jesus is coming! Everyone look busy!
On Mon, Feb 26, 2001 at 11:53:51AM -0800, Norman J. Clarke wrote: > Hello, > > I am attempting to write a trigger function or rule in pl/pgsql that runs > on UPDATE to a table named "nodes". From inside this trigger, I would like > to UPDATE the same "nodes" table. How can I do this without entering into > an endless loop? What I did is add a no_update bool to the table and test: if new.no_update = true then new.no_update = false; else <do your update here> new.no_update = true; endif -- Observe, reason, and experiment. (if you're too dumb, just pray)