title: SQL Server Changelog creation, Identity Syntax, and Invalid COlumns
body: <p>I'm currently working on creating an SQL Server trigger, which after an update, should populate a change-log with any salary changes made, and by which user.</p>
  
  <p>The changelog table that I made which should receive updates can be seen here:</p>
  
  <pre><code>CREATE TABLE ChangeLog
  (
  ChangeID int identity(1,1) primary key,
  EmpID int,
  [User] nvarchar(30),
  [Date] smalldatetime,
  OldRate money,
  NewRate money
  )
  </code></pre>
  
  <p>Everything is fine with table as far as I can tell, however when I go to create the trigger, i'm starting to receive a few errors (and a boatload of confusion.) I've tried researching several methods of doing a trigger that updates a different table with a new row, and as far as I can tell, the structure of it is correct, however I am unsure how I have messed up the contents. </p>
  
  <pre><code>CREATE TRIGGER payrate_change ON Employees FOR UPDATE
  As
  DECLARE @originalPay VARCHAR(100)
  DECLARE @newPay VARCHAR(100)
  If UPDATE (PayRate)
  BEGIN
  SELECT @originalPay = (SELECT PayRate FROM deleted)
  SELECT @newPay = (SELECT PayRate FROM inserted)
  INSERT INTO [dbo].[ChangeLog]
          (ChangeID, EmpID, [User], [Date], OldRate, NewRate)
      VALUES
          identity(1,1), 
          Employees.EmployeeID, 
          SUSER_SNAME(), 
          SYSDATETIME(), 
          @originalPay, 
          @newPay
      FROM inserted
  END
  </code></pre>
  
  <p>The payrate portion of the two select statements give an error saying they are invalid columns, even though two lines above it calls PayRate just fine. Then for the values portion of the Insert Into statement, identity claims to have incorrect syntax despite me using it the exact same way in the creation of the table.</p>
  
  <p>Does anyone have any ideas on what may be the issue with this trigger? Any input would be greatly appreciated.</p>
  
  <p>EDIT:</p>
  
  <p>After the suggested changes below, I re-added ChangeID (primary key) to the trigger, however now IDENTITY is giving me an incorrect syntax error:</p>
  
  <pre><code>CREATE TRIGGER payrate_change ON Employees FOR UPDATE
  As
  
  If UPDATE (PayRate)
  BEGIN
  
  INSERT INTO [dbo].[ChangeLog]
          (ChangeID, EmpID, [User], [Date], OldRate, NewRate)
      select
          IDENTITY(1,1),
          i.EmployeeID, 
          SUSER_SNAME(), 
          SYSDATETIME(), 
          d.PayRate, 
          i.PayRate
      FROM inserted i
      JOIN deleted d on i.EmployeeID = d.deleted
  END
  </code></pre>
  
  <p>EDIT2: Adding the final result for anyone who is interested:</p>
  
  <pre><code>CREATE TRIGGER payrate_change2 ON Employees FOR UPDATE
  AS
  
  If UPDATE (Pay)
  BEGIN
  
  INSERT INTO [dbo].[ChangeLog]
          (EmpID, [User], [Date], OldRate, NewRate)
      select
          i.EmployeeID, 
          SUSER_SNAME(), 
          SYSDATETIME(), 
          d.Pay, 
          i.Pay
      FROM inserted i
      JOIN deleted d on i.EmployeeID = d.EmployeeID
  END
  </code></pre>