Re: Trigger loop question

> Mike Nolan <nolan@gw.tssi.com> writes:
> > If I set up an on update trigger for table 'A' that updates the
> > corresponding column in table 'B', and one for table 'B' that updates
> > the corresponding column in table 'A', does that create an endless loop?
>
> Yes.
>
> You could break the loop perhaps by not issuing an UPDATE if the data is
> already correct in the other table.

The trigger on table 'A' is obviously going to see both the old value and the
new value for the column.  If it queries table 'B', it would see
the current value there.

However, if I update table 'B' and the 2nd trigger fires, that trigger
will still see the OLD value if does a query on table 'A', since I
think transaction atomic rules require that any updated values aren't
made available to the outside world (including other triggers) until the
transaction is complete.

I tested this, and the 2nd trigger still sees the original value of
the field from the first table, which I think is the proper result.
--
Mike Nolan