Re: 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
terry(at)turbocorp(dot)com
www.turbocorp.com