SqlServer如何记录时间变迁-猿码集

SqlServer如何记录时间变迁

在开发过程中，我们经常需要记录数据的时间变迁，比如记录某个实体的修改时间，某个属性的值变化时间等等。这时候我们就需要在数据库中添加相应的字段来记录这些时间变化了，而 SqlServer 提供了多种方式来记录时间变迁。

一、使用触发器

使用触发器是一种比较常见的记录时间变迁的方式。我们可以在表中添加两个字段，一个字段用来记录实体的最后更新时间，一个字段用来记录属性的最后更新时间。每次更新操作都会触发相应的触发器来更新这些字段的值。

下面是一个示例，假设我们需要记录一个 User 实体的更新时间以及其属性 Name 的更新时间：


CREATE TABLE [dbo].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [LastUpdateTime] [datetime] NOT NULL DEFAULT(GETDATE()),
    [NameUpdateTime] [datetime] NOT NULL DEFAULT(GETDATE()),
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
))
GO
CREATE TRIGGER [dbo].[UserUpdateTimeTrigger]
ON [dbo].[User] AFTER UPDATE
AS
BEGIN
    IF UPDATE (Name)
    BEGIN
        UPDATE [dbo].[User]
        SET [NameUpdateTime] = GETDATE()
        FROM [dbo].[User] INNER JOIN inserted ON [dbo].[User].[Id] = inserted.[Id]
    END
    UPDATE [dbo].[User]
    SET [LastUpdateTime] = GETDATE()
    FROM [dbo].[User] INNER JOIN inserted ON [dbo].[User].[Id] = inserted.[Id]
END
GO

每次更新操作都会触发 UserUpdateTimeTrigger 触发器，在触发器内部根据 UPDATE (Name) 判断是否需要更新 NameUpdateTime 字段。

二、使用历史表

使用历史表是另一种记录时间变迁的方式。我们可以在每个表对应一个历史表，用来记录每次更新操作的具体信息。每次更新操作时，将更新前的数据插入到历史表中。

下面是一个示例，假设我们需要记录 User 实体的更新历史：


CREATE TABLE [dbo].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
))
CREATE TABLE [dbo].[UserHistory](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [UpdateTime] [datetime] NOT NULL DEFAULT(GETDATE()),
 CONSTRAINT [PK_UserHistory] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
))
CREATE TRIGGER [dbo].[UserHistoryTrigger]
ON [dbo].[User] AFTER UPDATE
AS
BEGIN
    INSERT INTO [dbo].[UserHistory] ([UserId], [Name])
    SELECT [Id], [Name]
    FROM deleted
END
GO

每次更新操作都会触发 UserHistoryTrigger 触发器，将更新前的数据插入到 UserHistory 表中。

三、使用 Temporal 表

SqlServer 2016 开始支持 Temporal 表，用于记录版本历史。

下面是一个示例，假设我们需要记录 User 实体的更新历史：


CREATE TABLE [dbo].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]),
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[UserHistory]))
GO
SELECT * FROM [dbo].[User]
SELECT * FROM [dbo].[UserHistory]
GO
INSERT INTO [dbo].[User] ([Name])
VALUES ('User1')
WAITFOR DELAY '00:00:01'
UPDATE [dbo].[User]
SET [Name] = 'User1_Updated'
WHERE [Id] = 1
WAITFOR DELAY '00:00:01'
UPDATE [dbo].[User]
SET [Name] = 'User1_Updated2'
WHERE [Id] = 1
SELECT * FROM [dbo].[User]
SELECT * FROM [dbo].[UserHistory]
GO

每次更新操作都会在 UserHistory 表中自动记录版本历史。

总结

以上是 SqlServer 中记录时间变迁的三种方式，分别是使用触发器、使用历史表、使用 Temporal 表。根据具体需求来选择合适的方式，来记录数据的时间变迁。

SqlServer如何记录时间变迁