Thread: Trigger loop question
I have some information that that I need to keep synchronized which can be found in two different tables. (Yes, it's not fully normalized.) 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? -- Mike Nolan
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.
regards, tom lane
> 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
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 the > transaction is complete. I could be mistaken here, but... I don't believe that is the case. The transaction can see what's going on inside of itself. Everything outside of the transaction typically won't see what is inside the transaction, until it is committed anyway. > 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. I think this has more to do with whether the first trigger was fired BEFORE or AFTER the UPDATE. If the first trigger is fired BEFORE the UPDATE, then the second trigger (fired on the UPDATE) will not see the AFTER values of the first trigger. If you fire the first trigger as AFTER, I bet you'll see the changes. Greg
> I think this has more to do with whether the first trigger was fired > BEFORE or AFTER the UPDATE. If the first trigger is fired BEFORE the > UPDATE, then the second trigger (fired on the UPDATE) will not see the > AFTER values of the first trigger. If you fire the first trigger as > AFTER, I bet you'll see the changes. 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 triggered table to update the other table. This tested OK in both directions. Of course, if I need to do any manipulation of the fields in the table being updated by the SQL command, that will require a separate 'before update' trigger. -- Mike Nolan
Mike Nolan <nolan@gw.tssi.com> 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 triggered table to update
> the other table.
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.
regards, tom lane
> 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 other purpose as part of that trigger, or if some other trigger updates that table, couldn't that result in an infinite loop? It seems like the select-and-check method, even though it may be redundant most of the time, is the belt-and-suspenders way of avoiding an infinite loop. Here's a really weird question. If in the trigger for table A I have more than one statement that updates table B, or if more than one trigger procedure updates table B, does that cause multiple firings of either before or after update triggers on table B? -- Mike Nolan
Mike Nolan <nolan@gw.tssi.com> 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 to update the other table for any other purpose as part of
> that trigger, or if some other trigger updates that table, couldn't that
> result in an infinite loop?
Well, I'm assuming that your update logic converges to a fixed state;
if it doesn't, seems like you've got problems anyway ...
> Here's a really weird question. If in the trigger for table A I have
> more than one statement that updates table B, or if more than one trigger
> procedure updates table B, does that cause multiple firings of either
> before or after update triggers on table B?
Yes, and yes. You get one firing per row update event, IIRC, no matter
where that update came from.
regards, tom lane