Re: Multiple row update with trigger

Derrick Betts wrote:
> 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 don't have a direct answer to the question you asked, but I am
wondering...

What's the actual reason for having data duplicated within the same
table like this? From what you've said so far, it just sounds like you
have a table that is improperly denormalized and you're trying to hack
something on top of the design to fix what should be solved by
normalizing the data in the table.

Does the PK contain significant data, or is it arbitrary (e.g. a
sequence)? If the latter, it really sounds like you should be using this
groupid column as your PK and get rid of the current PK column -- or at
the very least, put a unique constraint/index on the groupid column.

If the former, my guess is that you should still be using the groupid as
the PK and what you currently have as the PK should instead be in a
separate table that allows you to do a 1-to-many groupid-formerPK
relationship.

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/