Mike Nolan [EMAIL PROTECTED] 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
Mike Nolan wrote:
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
Mike Nolan [EMAIL PROTECTED] writes:
Yes it does. OK, that means Tom's original suggestion of checking
the other table for the same value before updating it should prevent
an infinite loop, providing that's done from a pair of 'after update'
triggers, using the NEW.column entries in the
Actually, I wasn't thinking very clearly. The easiest way to break
the loop is to avoid updating the other table when OLD.x = NEW.x
in the trigger's arguments. The other way requires a rather-redundant
SELECT to see what is in the other table.
If I have to update the other table for any
Mike Nolan [EMAIL PROTECTED] writes:
Actually, I wasn't thinking very clearly. The easiest way to break
the loop is to avoid updating the other table when OLD.x = NEW.x
in the trigger's arguments. The other way requires a rather-redundant
SELECT to see what is in the other table.
If I have