SQL Server 2008 行插入和更新时间戳 [英] SQL Server 2008 Row Insert and Update timestamps

问题描述

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

I need to add two columns to a database table in SQL Server 2008 R2:

• createTS - 插入行的日期和时间
• updateTS - 更新行的日期和时间

我有几个问题:

1. 我应该为每个列使用哪种列数据类型?
2. createTS 只需在插入行时设置一次.当我为此列尝试 datetime 类型并添加 getdate() 的 默认值或绑定 时，列值已正确设置.这是实现本专栏目的的最佳方式吗?我考虑过 timestamp 数据类型，但在我看来，这几乎是用词不当！
3. updateTS 需要设置为该行更新的那一刻的日期和时间.在 SQL Server 中，没有 ON UPDATE CURRENT_TIMESTAMP (如在 MySQL 中)，所以看起来我不得不求助于使用触发器.这是正确的，我将如何去做?

1. What column data type should I employ for each of these?
2. createTS needs to be set only once, when the row is inserted. When I tried the datetime type for this column and added a Default Value or Binding of getdate(), the column value was appropriately set. Is this the best way to fulfill the purpose of this column? I considered timestamp data type, but that is, in my opinion, nearly a misnomer!
3. updateTS needs to be set to the date and time of the moment when the row is updated. In SQL Server, there is no ON UPDATE CURRENT_TIMESTAMP (as in MySQL), so it looks like I have to resort to using a trigger. Is this right and how would I go about doing that?

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

So there is a starting point for anyone who would like to answer this question, here is the create table script:

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

)

附注我认为 smalldatetime 就足够了.你可以做出不同的决定.

PS I think a smalldatetime is good enough. You may decide differently.

你不能在影响的时刻"这样做吗?

Can you not do this at the "moment of impact" ?

在 Sql Server 中，这是常见的:

In Sql Server, this is common:

Update dbo.MyTable 
Set 

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

Sql Server 有一个时间戳"数据类型.

Sql Server has a "timestamp" datatype.

但这可能不是你想的那样.

But it may not be what you think.

这是一个参考:

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

这里有一点RowVersion (时间戳的同义词) 示例:

Here is a little RowVersion (synonym for timestamp) example:

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]

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

Maybe a complete working example:

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]

匹配名称"值可能不明智.

Matching on the "Name" value is probably not wise.

用 SurrogateKey 试试这个更主流的例子

Try this more mainstream example with a SurrogateKey

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]

这篇关于SQL Server 2008 行插入和更新时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！