SQL Trigger: On update of primary key, how to determine which "deleted" record cooresponds to which "inserted" record?

Question

Assume that I know that updating a primary key is bad.

There are other questions which imply that the inserted and updated table records match by position (the first of one matches the first of the other.) Is this a fact or coincidence?

Is there anything that could join the two tables together when the primary key changes on an update?

Answer 1

There is no match of inserted+deleted virtual table row positions.

And no, you can't match rows

Some options:

• there is another unique unchanging (for that update) key to link rows
• limit to single row actions.
• use a stored procedure with the OUTPUT clause to capture before and after keys
• INSTEAD OF trigger with OUTPUT clause (TBH not sure if you can do this)
• disallow primary key updates (added after comment)

Answer 2

Each table is allowed to have one identity column. Identity columns are not updateable; they are assigned a value when the records are inserted (or when the column is added), and they can never change. If the primary key is updateable, it must not be an identity column. So, either the table has another column which is an identity column, or you can add one to it. There is no rule that says the identity column has to be the primary key. Then in the trigger, rows in inserted and updated that have the same identity value are the same row, and you can support updating the primary key on multiple rows at a time.