编辑。因为已经有了一个存储过程，所以您可以将类似的逻辑嵌入到sp中。我创建了一个示例存储过程，它将获取员工姓名，更新部门，然后实现您的业务逻辑。同样，将一个真正的主键字段（而不是雇员姓名）作为参数，否则可能会更新多个记录。

ALTER PROCEDURE UpdateDepartment @EmpName VARCHAR(20), @Dept VARCHAR(20)
AS
BEGIN
    DECLARE @Changed TABLE
    (
        EmpName VARCHAR(20),
        OldDepartment VARCHAR(20),
        NewDepartment VARCHAR(20),
        StartDate DATETIME2,
        EndDate DATETIME2
    )

    UPDATE TempEmployees
    SET
        Department = @Dept
    output 
        inserted.EmpName, 
        deleted.Department, 
        inserted.Department, 
        inserted.StartDate,
        inserted.EndDate
    into @Changed
    WHERE EmpName = @EmpName

    DECLARE @OldDept VARCHAR(20);

    SELECT @OldDept = OldDepartment FROM @Changed

    UPDATE TempEmployees 
    SET 
        EndDate = CURRENT_TIMESTAMP,
        Department = @OldDept
    WHERE EmpName = @EmpName;

    INSERT INTO TempEmployees 
    SELECT
        EmpName,
        NewDepartment,
        DATEADD(DAY, 1, CURRENT_TIMESTAMP),
        '99991231'
    FROM @Changed

END

一种选择是使用触发器，并利用update语句生成的输出表。当 UPDATE 语句运行时 inserted 和一个 deleted 生成输出表，详细说明记录的前状态和后状态。
请参阅下面的代码，并尝试为您的应用程序修改它。请注意，当前版本的触发器使用雇员姓名作为更新 predicate 的一部分。如果一个特定的员工姓名有多个记录，则所有记录都将被更新。因此，在表中使用一个真正的主键字段，以确保只更新一条记录，这样做更有意义。

CREATE TRIGGER TempEmployeeMovedDepartment
ON TempEmployees
AFTER UPDATE 
AS
BEGIN
    SET NOCOUNT ON;

    IF UPDATE(Department)
    BEGIN
        --Update the End Date for the Current Record. In this case, I'm using the EmpName
        --field to denote the record to update. Presumably, you'd have an actual Primary Key
        DECLARE @PK VARCHAR(20);
        DECLARE @OldDept VARCHAR(20);

        SELECT @PK = EmpName, @OldDept = Department FROM deleted;

        UPDATE TempEmployees 
        SET 
            EndDate = CURRENT_TIMESTAMP,
            Department = @OldDept
        WHERE EmpName = @PK;

        INSERT INTO TempEmployees 
        SELECT
            EmpName,
            Department,
            DATEADD(DAY, 1, CURRENT_TIMESTAMP),
            '99991231'
        FROM inserted
    END
END