SQL Server 2008 Строка Вставка и обновление временных меток

Мне нужно добавить два столбца в таблицу базы данных в SQL Server 2008 R2:

createTS - дата и время, когда строка вставлена
updateTS - дата и время обновления строки

У меня есть несколько вопросов:

Какой тип данных столбца я должен использовать для каждого из них?
createTS должен быть установлен только один раз, когда строка вставлена. Когда я попробовал тип datetime для этого столбца и добавил значение по умолчанию или привязку getdate(), значение столбца было соответствующим образом установлено. Это лучший способ выполнить цель этой колонки? Я считал тип данных timestamp, но это, на мой взгляд, почти неверное слово!
updateTS необходимо установить дату и время момента обновления строки. В SQL Server нет ON UPDATE CURRENT_TIMESTAMP (как в MySQL), поэтому похоже, что мне приходится прибегать к использованию триггера. Правильно ли это и как я буду это делать?

Итак, для всех, кто хотел бы ответить на этот вопрос, есть отправная точка, вот таблица create 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]

Ответ 1

попробовать

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

)

PS Я думаю, что smalldatetime достаточно хорош. Вы можете решить по-другому.

Не можете ли вы сделать это в "момент удара"?

В Sql Server это обычное явление:

Update dbo.MyTable 
Set 

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

Sql Server имеет тип данных "timestamp".

Но это может быть не то, что вы думаете.

Вот ссылка:

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

Вот пример RowVersion (timestamp):

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]

Соответствие по значению "Имя", вероятно, неразумно.

Попробуйте этот более обычный пример с помощью 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]

Ответ 2

В качестве альтернативы использованию триггера вам может потребоваться создать хранимую процедуру для обработки INSERT, которая занимает большую часть столбцов в качестве аргументов и получает CURRENT_TIMESTAMP, который он включает в окончательный INSERT, чтобы базы данных. Вы можете сделать то же самое для CREATE. Вы также можете настроить все, чтобы пользователи не могли выполнять инструкции INSERT и CREATE, кроме как через хранимые процедуры.

Я должен признать, что я на самом деле не сделал этого сам, поэтому я совсем не уверен в деталях.