'Create automatically generated timestamp column in table?

How to create a automatically generated timestamp column in table in Microsoft SQL Server 2019? Timestamp column should be automatically generated when I insert or update table.

In IBM Db2 database the syntax is the following:

    create table myschema.mytable (col1 int, col2 timestamp not null generated always for each row on update as row change timestamp
    insert into myschema.mytable (col1) values (1)
    update myschema.mytable set col1 = 2

After insert/update of column col1, column col2 is automatically generated as current timestamp.

sqlsql-servertsql


Solution 1:[1]

In Microsoft SQL Server you can try this code:

CREATE TABLE myschema.mytable
(
    col1 int, 
    col2 datetime not null default(current_timestamp)
)

INSERT INTO myschema.mytable(col1) VALUES (1)

UPDATE myschema.mytable SET col1 = 2

SELECT * FROM myschema.mytable

Update: Let's create temporary table for test

DECLARE @mytable TABLE
(
    col1 int, 
    col2 datetime not null default(current_timestamp)
)

INSERT INTO @mytable(col1) VALUES (1)
SELECT * FROM @mytable

UPDATE @mytable SET col1 = 2
SELECT * FROM @mytable

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions