MS SQL Server 2017 How to Write a Trigger that moves the updated value and the old value to a new table

Question

When the pay is updated in the Employees table, I want the EmpID, oldrate and newrate to be moved to the changeLog table.

When I run the code below it moves all employee ID and gives me Null for oldRate and some newrate

ALTER TRIGGER trg_PayChange
ON Employees 
AFTER Update
AS
    IF NOT UPDATE(pay)
       DECLARE @oldRate VARCHAR(100)

    DECLARE @newRate VARCHAR(100)

    BEGIN
        INSERT INTO ChangeLog (EmpID)
            SELECT EmployeeID 
            FROM Employees

        INSERT INTO ChangeLog (OldRate)
            SELECT OldRate = (SELECT Pay FROM Deleted)

        INSERT INTO ChangeLog (NewRate)
            SELECT NewRate = (SELECT Pay FROM Inserted)
END
GO

Perhaps a stored procedure is more suitable? What's the input side like? I would execute a stored procedure on button submit. Set the new value in a parameter, select the employee and their old value from the table and insert it into the table alongside the parameter, then update the original table. Or is this set on a trigger? — Simon, Nov 15 '17 at 00:55
You might want to specify the database engine your are using. For example, on MySQL, you might be able to find answers here: https://stackoverflow.com/a/6298881/4206925 — MewX, Nov 15 '17 at 00:56
This syntax looks like Transact-SQL (Microsoft SQL Server) not MySQL. Maybe this was meant to to be tagged with **sql-server** not **mysql**. — spencer7593, Nov 15 '17 at 00:58
You are correct. I fixed it, thanks spencer7593. This is an assignment and a trigger must be used. — Houston Carpenter, Nov 15 '17 at 01:02

score 1 · Answer 1 · answered Nov 15 '17 at 01:27

Assuming EmployeeId is the PRIMARY KEY or a unique key on the Employees table, and the value of the EmployeeId wasn't updated:

IF NOT UPDATE(EmployeeId)

   INSERT INTO ChangeLog (EmpId, OldRate, NewRate)
   SELECT d.EmployeeId
        , d.Pay
        , i.Pay
     FROM deleted d
     JOIN inserted i
       ON i.EmployeeId = d.EmployeeId
   ;

Note that if the value of EmployeeId is changed, this insert wouldn't necessarily insert a row to the ChangeLog table. Consider a query of the form:

UPDATE Employees SET Pay = 7, EmployeeId = 757 WHERE EmployeeId = 42

If that is successful, we expect the deleted table will contain

EmployeeId  Pay  somecol ...
----------  ---  ------- ---
        42    4  someval

and inserted table will contain

EmployeeId  Pay  somecol ... 
----------  ---  ------- ---
       757    7  someval ...

A couple of queries using an anti-join pattern would pick up those changes which would be missed by our first query...

  -- deleted with no matching inserted
  INSERT INTO ChangeLog (EmpId, OldRate, NewRate)
  SELECT d.EmployeeId
       , d.Pay
       , NULL
    FROM deleted d
    LEFT
    JOIN inserted i
      ON i.EmployeeId = d.EmployeeId
   WHERE i.EmployeeId IS NULL
  ;

  -- inserted with no matching deleted
  INSERT INTO ChangeLog (EmpId, OldRate, NewRate)
  SELECT i.EmployeeId
       , NULL
       , i.Pay
    FROM inserted i
    LEFT
    JOIN deleted d
      ON d.EmployeeId = i.EmployeeId
   WHERE d.EmployeeId IS NULL
  ;

But... that falls down if we "swap" EmployeeId values. Consider rows with EmployeeId values of 757 and 42 exist in the table, and we issue a statement like this:

 UPDATE Employees
    SET EmployeeId = CASE WHEN EmployeeId = 42  THEN 757 
                          WHEN EmployeeId = 757 THEN 42
                     END
      , Pay        = CASE WHEN EmployeeId = 42  THEN 4
                          WHEN EmployeeId = 757 THEN 7
                     END
 WHERE EmployeeId IN (42,757)

With that, the rows in the inserted and deleted tables will be "cross wired"... EmployeeId values will match, but will match a different row. So we probably want to test whether an UPDATE statement attempted to assign a new value to EmployeeId column, so we can take the appropriate action.

We might be better off just recording the changes...

INSERT INTO ChangeLog (action, empid, pay )
SELECT 'update old', EmployeeId, Pay
  FROM deleted
;
INSERT INTO ChangeLog (action, empid, pay )
SELECT 'update new', EmployeeId, Pay 
  FROM inserted
;

MS SQL Server 2017 How to Write a Trigger that moves the updated value and the old value to a new table

1 Answers1