Insert into Change Log based on Trigger After Update

Question

I need to code an AFTER UPDATE trigger. When the pay rate of the employees table is modified. It needs to take that information and create a line in the ChangeLog table. My tables are below:

Employees:

EmployeeID INT (PK),
PayPerHour MONEY

ChangeLog:

ChangeID INT (PK),
EmpID (FK to EmployeeID),
ChangedBy NVARCHAR(30),
DateChanged SMALLDATETIME,
OldRate MONEY,
NewRate MONEY

Here's what I put together. There's no error but it's not creating lines in the ChangeLog. (I was under the impression that inserted = new data, deleted = old data). Ideally, the EmpID would match, ChangedBy would be the System user, DateChanged would be a timestamp, OldRate would be the pre-updated rate, and NewRate would be the post-updated rate.

CREATE TRIGGER PayRate_UPDATE
ON Employees
AFTER UPDATE
AS
BEGIN
DECLARE @EmpID INT
SELECT @EmpID = EmployeeID
FROM inserted

DECLARE @OldRate MONEY
SELECT @OldRate = PayPerHour
FROM deleted

DECLARE @DateChanged SMALLDATETIME
SELECT @DateChanged = GETDATE()

DECLARE @ChangedBy NVARCHAR(128)
SELECT @ChangedBy = suser_sname()

DECLARE @NewRate MONEY
SELECT @NewRate = PayPerHour
FROM inserted

IF UPDATE(PayPerHour)
    UPDATE ChangeLog
        SET EmpID = @EmpID,
            ChangedBy = @ChangedBy,
            DateChanged = @DateChanged,
            OldRate = @OldRate,
            NewRate = @NewRate
END;

Answer 1

You have a couple of problems in your code:

1. Triggers must handle a set of updates (not a single update)
2. Your trigger does an UPDATE not an INSERT to your ChangeLog

Your code should look more like:

CREATE TRIGGER PayRate_UPDATE ON Employees
AFTER UPDATE
AS
 BEGIN

    DECLARE @DateChanged SMALLDATETIME
    SELECT  @DateChanged = GETDATE()

    DECLARE @ChangedBy NVARCHAR(128)
    SELECT  @ChangedBy = SUSER_SNAME()

  IF UPDATE(PayPerHour) 
  INSERT  [ChangeLog]
          (
            EmpID,
            ChangedBy,
            DateChanged,
            OldRate,
            NewRate
          )
          SELECT
            i.EmployeeID,
            @ChangedBy,
            @DateChanged,
            d.PayPerHour,
            i.PayPerHour
          FROM
            INSERTED i
            LEFT JOIN DELETED d
              ON i.EmployeeID = d.EmployeeID

  END;

So, The revised trigger can now handle the situation where everyone gets a pay rise such as

UPDATE Employees SET PayPerHour = PayPerHour  * 1.1 -- everyone gets a 10% payrise YAY!

And Your changelog does keeps a history of every update to each employees pay rate.