Re: Multiple row update with trigger

[Jon Sime <jsime(at)mediamatters(dot)org>]

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/