Re: Multiple row update with trigger

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