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:
EmployeeID INT (PK), PayPerHour MONEY
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;