How to Create a table with Auto Update Column

• Barnaad

  SSC Enthusiast

  Points: 192

  More actions

  May 15, 2009 at 4:39 am

  #200544

  Hi,

  The Below query is used to create the table with auto update column in mysql

  CREATE TABLE TableAutoUpdate(a int DEFAULT 0,b TIMESTAMP ON UPDATE CURRENT_TIMESTAMP).

  can any one suggest how to write this type of query for creating the table in SQLServer.

  Thanks

  Barnaad.

• Samuel Vella

  SSCrazy Eights

  Points: 8055

  More actions

  May 15, 2009 at 6:13 am

  #995223

  Try this example of computed columns:

  CREATE TABLE MyTable

  (

  Col1 varchar(50) NULL,

  Col2 varchar(50) NULL,

  Col3 AS getdate(),

  Col4 AS col1+col2

  )

• Barnaad

  SSC Enthusiast

  Points: 192

  More actions

  May 15, 2009 at 6:30 am

  #995244

  Hi Samuel,

  Thanks for your reply.

  But, I want to test SQLSpecialColumns ODBC API, For the i need Auto update columns. Here is the API,

  SQLSpecialColumns(hstmt, SQL_ROWVER, NULL, 0, NULL,0,(SQLCHAR*)"MyTable", SQL_NTS, 0, SQL_NULLABLE);

  When is used the above ( please see the first post) mySQL query is working fine. after execution of this API, it return the Column Name 'b'. But when i tested with table which i created, it will return an empty set.

  Please can any one suggest how to create a table in sqlserver for testing this API.

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  May 15, 2009 at 9:01 am

  #995409

  Hi

  I'm not sure if I got you but maybe you're looking for a TIMESTAMP (not DATETIME) column. Try this in SSMS:

  CREATE TABLE TestTS (Id INT, TS TIMESTAMP)

  INSERT INTO TestTS (Id)

  SELECT 1

  SELECT * FROM TestTS

  UPDATE TestTS SET Id = 2

  SELECT * FROM TestTS

  Greets

  Flo