[GENERAL] Trigger disable for table
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger disable for table
am Thu, dem 02.10.2008, um 12:26:20 +0200 mailte Frank Durstewitz folgendes: 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. I think, you can do that, but an 'ALTER TABLE' produce a AccessExclusiveLock on this table. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger disable for table
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 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger disable for table
Andreas and Terry, thanks for answering and pointing me in the direction. Unfortunately i found out, that even without the trigger the updates take too much time for interactive applications. So i go for a batch-update. Kindly regards, Frank -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
