SQL Server Trigger After Insert is Repeating old valus

Question

Can someone please explain why this trigger would start failing and insert the same record repeatedly? It seems as though there is something wrong with the variables. The purpose of the trigger is to copy the inserted record from the Employee table to the EmployeeHistory table. I set the trigger and it runs fine. But then when my coworker runs some insert scripts, it starts repeating the same old value from my last execution of an insert, instead of the new values that they are trying to insert.

I have already recoded this to not use variables, but I would still like to know why this doesn't work as expected.

CREATE TRIGGER [dbo].[triggerEmployee_AfterInsert]
       ON [dbo].[Employee]
AFTER INSERT
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @EmployeeID varchar(25)
       DECLARE @FirstName varchar(25)
       DECLARE @LastName varchar(50)
       DECLARE @FullName varchar(75)
       DECLARE @EmailAddress varchar(50)
       DECLARE @ManagerID varchar(15)
       DECLARE @JobTitle varchar(50)
       DECLARE @EmployeeStatus varchar(10)
       DECLARE @Office varchar(25)

 
       SELECT @EmployeeID = [dbo].[Employee].[EmployeeID]
            ,@FirstName = [dbo].[Employee].[FirstName]
            ,@LastName = [dbo].[Employee].[LastName]
            ,@FullName = [dbo].[Employee].[FullName]
            ,@EmailAddress = [dbo].[Employee].[EmailAddress]
            ,@ManagerID = [dbo].[Employee].[ManagerID]
            ,@JobTitle = [dbo].[Employee].[JobTitle]
            ,@EmployeeStatus = [dbo].[Employee].[EmployeeStatus]
            ,@Office = [dbo].[Employee].[Office]
       FROM [dbo].[Employee]
 
       INSERT INTO [dbo].[EmployeeHistory] (
            EmployeeID
            ,FirstName
            ,LastName
            ,FullName
            ,EmailAddress
            ,ManagerID
            ,JobTitle
            ,EmployeeStatus
            ,Office
       )
       VALUES (
            @EmployeeID
            ,@FirstName
            ,@LastName
            ,@FullName
            ,@EmailAddress
            ,@ManagerID
            ,@JobTitle
            ,@EmployeeStatus
            ,@Office
       )
END
GO

ALTER TABLE [dbo].[Employee] ENABLE TRIGGER [triggerEmployee_AfterInsert]
GO

Answer 1

Rewriting the SELECT statement to use the INSERTED table fixed the issue.

       SELECT @EmployeeID = [EmployeeID]
            ,@FirstName = [FirstName]
            ,@LastName = [LastName]
            ,@FullName = [FullName]
            ,@EmailAddress = [EmailAddress]
            ,@ManagerID = [ManagerID]
            ,@JobTitle = [JobTitle]
            ,@EmployeeStatus = [EmployeeStatus]
            ,@Office = [Office]
       FROM INSERTED