Create automatically generated timestamp column in table?

Question

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.

Assuming by timestamp you mean the current datetime, then `mycolumn datetime not null default(current_timestamp)` — Dale K, Jul 15 '20 at 07:29
If you mean a version field have a look at [rowversion](https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-ver15) this is updated on every row change, but doesn't represent an actual date — Preben Huybrechts, Jul 15 '20 at 07:30
If you need an actual datetime column (as opposed to a `rowversion`) you either need a trigger, or to run your update through a SP which adds it. There is no automated way other than `rowversion`. — Dale K, Jul 15 '20 at 07:57
I need to update timestamp column (datetime in MS-SQL language) also after every UPDATE statement. I know I can write trigger, but I would like to avoid additional complexity if possible. — folow, Jul 15 '20 at 08:56
@folow Please specify your decision as an update/addition to your question. — timnavigate, Jul 15 '20 at 12:40

timnavigate · Answer 1 · 2020-07-15T08:56:35.103

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

edited Jul 15 '20 at 08:56

answered Jul 15 '20 at 07:37

timnavigate

741
4
12
23

1

@DaleK did not want to assign your comment in your answer, sorry! I hope I didn’t offend you. What should I do? – timnavigate Jul 15 '20 at 07:44
2

Keep in mind a default doesn't update on `updates` that is requested in the question – Preben Huybrechts Jul 15 '20 at 07:44
@PrebenHuybrechts you mean `update` code line without `where` clause? – timnavigate Jul 15 '20 at 07:45
@timnavigate I mean the `col2` in your example will always have it's default value from the time it was inserted. When the row is updated, the default is not triggered, since there is already a value in col2 – Preben Huybrechts Jul 15 '20 at 07:47
@PrebenHuybrechts Yes that's right. In col2 will be set new value of current timestamp for each `insert` or `update` query. Everything is as it was in the question - `timestamp not null generated always for each row on update as row change timestamp`. – timnavigate Jul 15 '20 at 07:50
@PrebenHuybrechts I checked my example by creating a temporary table, six lines of `insert` queries and single line of `update` query - row values of `col2` always updated with every request. Or am I wrong about something? – timnavigate Jul 15 '20 at 07:54
@timnavigate - all good - carry on - its rare for people to use `current_timestamp` as most people use `getdate()` - which is what made me notice. – Dale K Jul 15 '20 at 07:54
1

Ar yes, OP wants the timestamp column to update on update (as well as insert). – Dale K Jul 15 '20 at 07:55
1

Yes, why I copied the version of @DaleK to my answer. `current_timestamp` is ANSI, same as in question. https://stackoverflow.com/a/24226775/5309660 – timnavigate Jul 15 '20 at 07:57
1

@timnavigate, in your example col2 timestamp field is only generated after INSERT, but I also need to change col2 timestamp field on every UPDATE. To simplify for every INSERT or UPDATE timestamp column col2 should be changed. – folow Jul 15 '20 at 08:48
@folow I updated the answer: add test script to create a temporary table. Please try using this and let me know if this is right for you or not? – timnavigate Jul 15 '20 at 08:57
1

Tested this independently - like I thought, timestamp will written only while inserting row. Add some delay (`waitfor delay '00:00:03'`) between insert and update and you can see itself. – Arvo Jul 15 '20 at 09:24
@timnavigate Are you sure that you tried it in MS SQL server? It is not working when I tried. I tried in 2019 version. Same as what arvo said.. – promaxdev Aug 26 '21 at 11:48
@RamanSrinivasan Unfortunately, I do not have a Windows instance of MSSQL Server installed, but try to use [interactive test](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b947fe5ad6e4caac36a521be50c43c0e) in web – timnavigate Aug 26 '21 at 12:27
@timnavigate I checked in the fiddle. The timestamp is not changed after update. I tried with a 'waitfor' also with 5 seconds delay. Still the timestamp didnt change. Unfortunately i cant attach a screenshot here. But here is the https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8d42bbe6e1fa944d8c2589b5336f266f – promaxdev Aug 31 '21 at 10:27
I have tried this for "SQL Server 2019" on linux - column is not updated if I do UPDATE – Evgeny Gorbovoy Apr 21 '22 at 13:19

Create automatically generated timestamp column in table?

1 Answers1