title: SQL 2008 Update a value calculated from the previous record
body: <p>Table like this fields: ID, InvoiceDate, SystemEntryDate, InvoiceNo, GrossTotal, Hash.
  For record with ID=1, the Hash is calculated with all fields from record 1, with the SignByAsymKey function.</p>
  
  <p>For all other records (ID>2), the hash is calculated with same function SignByAsymKey, with all the fields from the record and the Hash from the previous record.</p>
  
  <p>For example, for ID=3, the hash is calculated with the fields InvoiceDate, SystemEntryDate, InvoiceNo, GrossTotal, for that record, and with the Hash from record with ID=2.</p>
  
  <p>How do I do this? Is it better to use a trigger or a Stored procedure?</p>
  
  <p>I did a update trigger like this, but it gives the same hash to all records>1:</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER TRIGGER [dbo].[Invoice_update]
             ON  [dbo].[Invoice]
             AFTER update 
          AS 
          BEGIN
              SET NOCOUNT ON;
  update dbo.Invoice 
  set [hash]=
      (Case
          when
              dbo.Invoice.ID=1
          Then
              SignByAsymKey (
              AsymKey_Id ('SecureAsymmetricKeyINVOICE'),
              CONVERT([nvarchar],(select [InvoiceDate] from Invoice where [ID]=1),0)+';'+
              CONVERT([nvarchar],(select [SystemEntryDate] from Invoice where [ID]=1),0)+';'+
              CONVERT([nvarchar],(select [InvoiceNo] from Invoice where [ID]=1),0)+';'+
              CONVERT([nvarchar],(select [GrossTotal] from Invoice where [ID]=1),0)
              ,N'Portal2011!')
          else
              SignByAsymKey (
              AsymKey_Id ('SecureAsymmetricKeyINVOICE'),
              CONVERT([nvarchar],inserted.[InvoiceDate],0)+';'+
              CONVERT([nvarchar],inserted.[SystemEntryDate],0)+';'+
              CONVERT([nvarchar],inserted.[InvoiceNo],0)+';'+
              CONVERT([nvarchar],inserted.[GrossTotal],0)+';'+
              CONVERT([nvarchar],
              (select [hash] from dbo.Invoice
              where [id]=inserted.id-1),0)
              ,N'Portal2011!')
      End)
  from inserted
  End   
  </code></pre>