SQL update inserted row with trigger

Question

I'm trying to update a just inserted row in SQL with a table trigger. Below is the trigger. It seems this only updates the already inserted rows and NOT the just inserted row. What am I doing wrong?

ALTER TRIGGER [dbo].[TRG_DIM_Employee]
ON [dbo].[DIM_Employee]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON
    UPDATE DIM_Employee
    SET ParentEmployeeKey = i.EmployeeKey
    FROM INSERTED i
    WHERE DIM_Employee.EmployeeId = i.EmployeeId
END

Answer 1

Your update statement is not quite right. You need to JOIN your table with the inserted table rows.

ClientID int,
ALTER TRIGGER [dbo].[TRG_DIM_Employee]
ON [dbo].[DIM_Employee]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON

    UPDATE emp
    SET emp.ParentEmployeeKey = i.EmployeeKey
    FROM  DIM_Employee emp
    JOIN INSERTED i on emp.EmployeeId = i.EmployeeId
END

More examples of doing an update join here.

Answer 2

As far as I can recognize, this is old join syntax but is functionally equivalent. I set up a quick test but can't repro this issue, seems to work as expected.

FYI you should move to using proper JOIN syntax, this old ANSI-89 standard is already discontinued in some forms in SQL 2012.