SQL Server 2008 Row Insert and Update timestamps

Question

I need to add two columns to a database table in SQL Server 2008 R2    createTS - date and time when row is inserted updateTS - date and time when row is updated   I have a few questions    What column data type should I employ for each of these  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  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

User · Accepted Answer

try  CREATE TABLE  dbo   Names         Name   nvarchar  64  NOT NULL       CreateTS   smalldatetime  NOT NULL CONSTRAINT CreateTS DF DEFAULT CURRENT TIMESTAMP       UpdateTS   smalldatetime  NOT NULL      PS I think a smalldatetime is good enough   You may decide differently   Can you not do this at the  moment of impact     In Sql Server  this is common   Update dbo MyTable  Set   ColA    SomeValue    UpdateDS   CURRENT TIMESTAMP Where              Sql Server has a  timestamp  datatype   But it may not be what you think   Here is a reference   http   msdn microsoft com en-us library ms182776 v sql 90  aspx  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   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

User · Answer

As an alternative to using a trigger  you might like to consider creating a stored procedure to handle the INSERTs that takes most of the columns as arguments and gets the CURRENT TIMESTAMP which it includes in the final INSERT to the database  You could do the same for the CREATE  You may also be able to set things up so that users cannot execute INSERT and CREATE statements other than via the stored procedures   I have to admit that I haven t actually done this myself so I m not at all sure of the details

[sql-server] SQL Server 2008 Row Insert and Update timestamps

Examples related to sql-server

Examples related to timestamp