On Thursday 02 October 2008 06:26, Frank Durstewitz wrote: > Hi list. > > A fairly complex problem: > > - Table A has a before insert/update trigger, which look up table B and > use field C from table B. > - Table B has a after insert/update trigger, which update table A with > field C. > > The update on table B triggers the trigger from table A, so the same > thing is done twice. > Can one avoid to fire the trigger on table A, when updates are made to > table B, because i know all fields already and can build the update sql > for table A, so no need to call the trigger on table A? > > My idea is to have it like > ... > IF NEW.published = TRUE THEN > ALTER TABLE a DISABLE TRIGGER mytrigger USER; > (do update here) > ALTER TABLE a ENABLE TRIGGER mytrigger USER; > ... > > Will a construct like this disable the trigger only inside the this > function or is the trigger disabled outside (visiblility?) the function, > too, which is unacceptable. > > (Hmm, sounds very confused, and so i am...) > > A helping hand on this topic is well accepted :-) > > Thanks, Frank This should work but, if I remember correctly, it will lock table A. If that is OK in your environment, then go for it. It is not in ours. We have a table that we called override and when we want to override the firing of a certain trigger, we put code in that trigger that checks the override table for the existence of a record matching the trigger name and some other criteria. If we find it, we simply return from the trigger at that point. The trigger on table B would be responsible for inserting the record into override and then deleting the record after the update is done. We've build wrapper functions to make the inserts and deletes to override easy. HTH... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 terry@xxxxxxxxxxxxx www.turbocorp.com
