title: Determine Old primary key in a SQL Trigger
body: <p>I've done this before somewhere I'm sure of it!</p>
  
  <p>I have a SQL Server 2000 table that I need to log changes to fields on updates and inserts into a second Logging table. A simplified version of the structure I'm using is below:</p>
  
  <pre><code>MainTable
  ID varchar(10) PRIMARY KEY
  DESCRIPTION varchar(50)
  
  LogTable
  OLDID varchar(10)
  NEWID varchar(10)
  </code></pre>
  
  <p>For any other field something like this would work great:</p>
  
  <pre><code>Select i.DESCRIPTION As New, d.DESCRIPTION As Old 
  From Inserted i
  LEFT JOIN Deleted d On i.ID=d.ID
  </code></pre>
  
  <p>...But obviously the join would fail if ID was changed.</p>
  
  <p>I cannot modify the Tables in way, the only power I have in this database is to create a trigger.</p>
  
  <p>Alternatively is there someone who can teach me time travelling and I'll go back into the past and ask myself back then how I did this? Cheers :)</p>
  
  <hr>
  
  <p><strong>Edit:</strong></p>
  
  <p>I think I need to clarify a few things here. This is <strong>not actually my database</strong>, it is a pre-existing system that I have almost no control of, other than writing this trigger.</p>
  
  <p>My question is how can I retrieve the old primary key if said primary key was changed. I don't need to be told that I shouldn't change the primary key or about chasing up foreign keys etc. That's not my problem :)</p>