sql - 更新的记录插入历史表而不是旧记录

Question

我有两个表 Test 和 TestHistory

CREATE TABLE [dbo].[TEST](
    [ID] [int] NULL,
    [Name] [varchar](10) NULL,
    [Status] [char](1) NULL,
    [CreatedDate] [datetime] NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Test_History](
    [ID] [int] NULL,
    [Name] [varchar](10) NULL,
    [Status] [char](1) NULL,
    [CreatedDate] [datetime] NULL
) ON [PRIMARY]

GO

INSERT INTO TEST ([ID],[Name],[Status],[CreatedDate])values (1,'Mohan','A',GETDATE())

创建触发器:

ALTER TRIGGER [dbo].[trg_Test] 
     ON [dbo].[TEST]
     FOR UPDATE 
     AS

     Declare @ID INT;
     Declare @Name varchar(10);
     Declare @Status CHAR(2);
     Declare @CreatedDate DATETIME;


     Select @ID = I.ID from INSERTED I
     Select @Name = I.Name from INSERTED I
     Select @Status = I.Status from INSERTED I
     Select @CreatedDate = I.CreatedDate from INSERTED I


     INSERT INTO [dbo].[Test_history]
           ([ID]
           ,[Name]
           ,[Status]
           ,[CreatedDate]
           )

          SELECT @ID,
                 @Name,
                 @Status,
                 GETDATE()

                    FROM INSERTED I

           WHERE @ID = [ID] 

当我像

一样更新记录时

Update [TEST] SET Status = 'I' 然后应该插入带有 Status = 'A' 的旧记录，但我更新的内容一直是插入 Testhistory 表而不是旧记录 我做错了什么以及如何插入旧值

如果我更新 Status = 'I' 并且在历史表中应该插入 Status = 'A'

Answer 1

您需要从 DELETED 而非 INSERTED 中 INSERT。

在此处查看示例 Understanding SQL Server inserted and deleted tables for DML triggers .