CODEWITHSUNDEEP
sqlsql-server-2005t-sqltriggers
Zachary Scott
Zachary Scott

Reputation: 21188

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

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?

Upvotes: 7

Views: 4735

Answers (3)

Daniel
Daniel

Reputation: 155

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.

Upvotes: 2

Chains
Chains

Reputation: 13167

Yes -- create an "old_primary_key" field in the table you're updating, and populate it first.

Nothing you can do to match-up the inserted and deleted psuedo table record keys -- even if you store their data in a log table somewhere.

I guess alternatively, you could create a separate log table that tracked changes to primary keys (old and new). This might be more useful than adding a field to the table you're updating as I suggested right at first, as it would allow you to track more than one change for a given record. Just depends on your situation, I guess.

But that said -- before you do anything, please go find a chalk board and write this 100 times:

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

:-) (just kidding)

Upvotes: 1

gbn
gbn

Reputation: 432639

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)

Upvotes: 9

Related Questions