Mark Borins wrote:
> I am creating a system where I have a trigger on three different
> tables. There is a particular Boolean field in each of these tables
> that when it is set in table it should be set the same in the other two.
>
>
>
> So I figured I could put a trigger on each table that when the Boolean
> field was updated it would go and update the other 2.
>
>
>
> However, I am concerned about cascading trigger calls.
You could make those triggers like below and let them trigger their
equivalents on one of the other two tables:
table_a:
if old.value != new.value then
update table_b set value = new.value where id=new.id
endif;
table_b:
if old.value != new.value then
update table_c set value = new.value where id=new.id
endif;
table_c:
if old.value != new.value then
update table_a set value = new.value where id=new.id
endif;
This way, if the value is the desired value, no more updates are done.
The chain reaction stops as soon as all three tables have the desired
values.
Nothing wrong with cascading triggers, as long as you're aware of what
you're doing. It can be useful to put this on paper schematically; there
may even be some standard schematic notation for triggers and cascading
(or that would be useful if there isn't...).
> Does anyone know if it is possible to run an update statement on a table
> and for only that statement disable the trigger on the table?
--
Alban Hertroys
MAG Productions
T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl
