I have a table with a primary key for each row, and a group
identification number (groupid) which is not necessarily unique, for
each row. As such, I may have 3-5 rows with the same groupid.
Anytime a row is updated, I need a trigger to update any other rows with
the same groupid as the NEW row that is being updated.
For example, rows 1, 2 & 3 all share the same groupid:
Anytime row 1 is updated, I need row 2 and 3 updated with the same
information as row 1.
Anytime row 2 is updated, I need row 1 and 3 updated with the same
information as row 2.
Anytime row 3 is updated, I need row 1 and 2 updated with the same
information as row 3.
I would like to use a trigger, but the only way I can see updating the
"additional" rows is with the NEW variable, and this is only visible on
a FOR EACH ROW trigger. This causes problems in that the trigger will
get caught in an infinite loop as it begins updating the additional rows.
The other alternative is a FOR EACH STATEMENT trigger. However, the
values that are being updated in the NEW row aren't visible to this type
of trigger to allow me to update the other rows.
Does anyone have any good ideas as to how I might solve this problem?
Perhaps even taking a completely different approach in some way?
Thanks,
Derrick