Post reply

Triggers

  • October 27, 2004 at 4:19 pm

    #59544

    Before I begin, yes, I do realize Primary Keys should not be changed...

    If I want to track Updates to a Table, and I want to track when the Primary Key changes, how can this be done??  I would like to track what the Old and New Values are for that PKey but I don't know how it can be done.  Apparently, an Update Trigger has both an "inserted" and "deleted" table...but if the Keys are different...how can I Join the two to get what info is changing??

  • October 27, 2004 at 6:04 pm

    #528236

    You need some other unique identifier in the table. An identity column is best because no one can change it.

    If you expect only one row is updated at a time, there's only one row in inserted and deleted tables, and the old and new values are easy to get.

    Without that identity, you could save all primary keys in the deleted table that are not found in the inserted table (call that "old"), and all primary keys in the inserted table that are not found in the deleted table (call that "new"). Save the primary keys in a table with a column containing the "old" or "new" label. Also add a column containing a unique identifier for the transaction (an incrementing id or GUID).

    It may be good enough for auditing. You may not be able to match old to new automatically; but you may be able to figure it out. Perhaps include in the audit table some other identfying data from the inserted/deleted tables.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply