Re: Multiple row update with trigger

[Derrick Betts <list(at)blueaxis(dot)com>]

Jon Sime wrote:
> 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
Thank you for your insights Jon,
The duplicate data among the unique Primary Key'd rows of data could be 
stored in a separate table (many to 1 relationship), and if I did that, 
the problem would be solved.  This can be done and is a very good 
solution, except that it would take a very long time to re-code the 
already existing set of SQL commands and result sets inside the client 
application.  I am hoping to avoid that.  If I can find a way to use 
what has already been created without having to go back and re-code, 
that would be my preference. Thus the hope for a database solution, if 
possible.

Derrick