title: SQL violation of Primary Key constraint on update
body: <p><strong>--EDITED--</strong></p>
  
  <p>I think I figured out the problem. The original row is being taken to update instead of the latest row therefore the <code>CreationDate</code> is the same and since <code>ID + CreationDate</code> is the primary key it returns a violation. Is there any way to select the latest row instead of the original row when updating records?</p>
  
  <p>Thanks :D</p>
  
  <p><strong>----------------</strong></p>
  
  <p><a href="https://i.stack.imgur.com/ysQhM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ysQhM.png" alt="enter image description here"></a></p>
  
  <p>I got the error violation of primary key constraint but I don't know why because my primary key values are unique. </p>
  
  <p>I can add the record for 'Darren' and update it once. After that I get the error. My trigger works such that when I update an existing record, both the original and edited record are inserted into the table ProcessList so that I am able to see all the changes made to all records.</p>
  
  <p>Tables:</p>
  
  <pre><code>CREATE TABLE dbo.ProcessList
  (
      TransactionID       integer         IDENTITY,
      IsEdited            bit             DEFAULT 'FALSE',
      ID                  integer         NOT NULL,
      Name                varchar(30)     NOT NULL,
      Amount              smallmoney      NOT NULL,
      CreationDate        datetime        DEFAULT GETDATE(),
      ModificationDate    datetime,
      PRIMARY KEY (ID, CreationDate)
  )
  
  CREATE TABLE dbo.ProcessListHist
  (
      TransactionID       integer         IDENTITY,
      IsEdited            bit             DEFAULT 'FALSE',
      ID                  integer         NOT NULL,
      Name                varchar(30)     NOT NULL,
      Amount              smallmoney      NOT NULL,
      CreationDate        datetime        DEFAULT GETDATE(),
      ModificationDate    datetime,
      PRIMARY KEY (ID, CreationDate)
  )
  </code></pre>
  
  <p>Trigger:</p>
  
  <pre><code>CREATE TRIGGER CloneAfterUpdate ON ProcessList
  AFTER UPDATE
  AS
      IF (UPDATE (Amount) OR UPDATE (NAME))
      BEGIN
          INSERT INTO ProcessListHist (ID, Name, Amount, CreationDate, ModificationDate, IsEdited)
              SELECT 
                  ID, Name, Amount, CreationDate, GETDATE(), 'True'
              FROM deleted
  
          UPDATE PL
          SET PL.CreationDate = PLH.ModificationDate
          FROM ProcessList PL
          INNER JOIN deleted ON PL.ID = deleted.ID 
                             AND PL.CreationDate = deleted.CreationDate
          INNER JOIN ProcessListHist PLH ON PL.ID = PLH.ID 
                                         AND PLH.CreationDate = deleted.CreationDate
  
          INSERT INTO ProcessList (ID, Name, Amount, CreationDate, ModificationDate, IsEdited)
              SELECT 
                  ID, Name, Amount, CreationDate, ModificationDate, IsEdited
              FROM ProcessListHist
  END
  </code></pre>
  
  <p>Insert/Update statements:</p>
  
  <pre><code>INSERT INTO ProcessList (ID, Name, Amount) VALUES ('1020', 'Darren', '300')
  UPDATE ProcessList SET Amount = 1000 WHERE Name = 'Darren'
  </code></pre>