SQL Server 2008 行插入和更新时间戳

我需要向 SQL Server 2008 R2 中的数据库表添加两列：

• 创建TS-插入行的日期和时间
• 更新TS-更新行的日期和时间

我有几个问题：

1. 我应该为其中的每一个使用什么列数据类型？
2. createTS只需在插入行时设置一次。当我尝试datetime键入此列并添加默认值或绑定 of getdate()，适当设置列值。这是实现本专栏目的的最佳方式吗？我考虑过timestamp数据类型 https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms182776(v=sql.90)，但在我看来，这几乎是一个用词不当！
3. updateTS需要设置为更新行时的日期和时间。在 SQL Server 中，没有更新 CURRENT_TIMESTAMP 时（就像在 MySQL 中一样），所以看来我必须求助于使用触发器。这是正确的吗？我该怎么做？

因此，对于任何想回答这个问题的人来说，都有一个起点，这里是创建表脚本：

CREATE TABLE [dbo].[names]
(
    [name] [nvarchar](64) NOT NULL,
    [createTS] [datetime] NOT NULL CONSTRAINT [DF_names_createTS]  DEFAULT (getdate()),
    [updateTS] [datetime] NOT NULL,
    CONSTRAINT [PK_names] PRIMARY KEY CLUSTERED 
    (
        [name] ASC
    )
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

try

CREATE TABLE [dbo].[Names]
(
    [Name] [nvarchar](64) NOT NULL,
    [CreateTS] [smalldatetime] NOT NULL CONSTRAINT CreateTS_DF DEFAULT CURRENT_TIMESTAMP,
    [UpdateTS] [smalldatetime] NOT NULL

)

聚苯乙烯 我认为小日期时间就足够了。您可能会做出不同的决定。

难道在“冲击的那一刻”就不能这样做吗？

在 Sql Server 中，这是常见的：

Update dbo.MyTable 
Set 

ColA = @SomeValue , 
UpdateDS = CURRENT_TIMESTAMP
Where...........

Sql Server 有一个“时间戳”数据类型。

但它可能不是你想的那样。

这是一个参考：

http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx

这里有一点行版本 https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017 (时间戳的同义词 https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms177566%28v%3dsql.90%29） 例子：

CREATE TABLE [dbo].[Names]
(
    [Name] [nvarchar](64) NOT NULL,
    RowVers rowversion ,
    [CreateTS] [datetime] NOT NULL CONSTRAINT CreateTS_DF DEFAULT CURRENT_TIMESTAMP,
    [UpdateTS] [datetime] NOT NULL

)


INSERT INTO dbo.Names (Name,UpdateTS)
select 'John' , CURRENT_TIMESTAMP
UNION ALL select 'Mary' , CURRENT_TIMESTAMP
UNION ALL select 'Paul' , CURRENT_TIMESTAMP

select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]

Update dbo.Names Set Name = Name

select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]

也许是一个完整的工作示例：

DROP TABLE [dbo].[Names]
GO


CREATE TABLE [dbo].[Names]
(
    [Name] [nvarchar](64) NOT NULL,
    RowVers rowversion ,
    [CreateTS] [datetime] NOT NULL CONSTRAINT CreateTS_DF DEFAULT CURRENT_TIMESTAMP,
    [UpdateTS] [datetime] NOT NULL

)

GO

CREATE TRIGGER dbo.trgKeepUpdateDateInSync_ByeByeBye ON dbo.Names
AFTER INSERT, UPDATE
AS

BEGIN

Update dbo.Names Set UpdateTS = CURRENT_TIMESTAMP from dbo.Names myAlias , inserted triggerInsertedTable where 
triggerInsertedTable.Name = myAlias.Name

END


GO






INSERT INTO dbo.Names (Name,UpdateTS)
select 'John' , CURRENT_TIMESTAMP
UNION ALL select 'Mary' , CURRENT_TIMESTAMP
UNION ALL select 'Paul' , CURRENT_TIMESTAMP

select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]

Update dbo.Names Set Name = Name , UpdateTS = '03/03/2003' /* notice that even though I set it to 2003, the trigger takes over */

select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]

匹配“Name”值可能并不明智。

使用代理键尝试这个更主流的示例

DROP TABLE [dbo].[Names]
GO


CREATE TABLE [dbo].[Names]
(
    SurrogateKey int not null Primary Key Identity (1001,1),
    [Name] [nvarchar](64) NOT NULL,
    RowVers rowversion ,
    [CreateTS] [datetime] NOT NULL CONSTRAINT CreateTS_DF DEFAULT CURRENT_TIMESTAMP,
    [UpdateTS] [datetime] NOT NULL

)

GO

CREATE TRIGGER dbo.trgKeepUpdateDateInSync_ByeByeBye ON dbo.Names
AFTER UPDATE
AS

BEGIN

   UPDATE dbo.Names
    SET UpdateTS = CURRENT_TIMESTAMP
    From  dbo.Names myAlias
    WHERE exists ( select null from inserted triggerInsertedTable where myAlias.SurrogateKey = triggerInsertedTable.SurrogateKey)

END


GO






INSERT INTO dbo.Names (Name,UpdateTS)
select 'John' , CURRENT_TIMESTAMP
UNION ALL select 'Mary' , CURRENT_TIMESTAMP
UNION ALL select 'Paul' , CURRENT_TIMESTAMP

select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]

Update dbo.Names Set Name = Name , UpdateTS = '03/03/2003' /* notice that even though I set it to 2003, the trigger takes over */

select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]