what type in SQL Server to use as a writeable timestamp?

Question

I have a table in SQL Server, tblMain. There's a trigger that when a row changes, it basically does a SELECT * from tblMain and inserts the changed row into tblHistory. tblHistory is a duplicate of tblMain (only much taller) and it has one extra field for a unique id. I recently added a field of type TimeStamp (which I now understand is being deprecated, but I'll deal with that later) in order to avoid a Write Conflict problem in Microsoft Access 2007.

Obviously the trigger copies every field in tblMain to tblHistory. It's doing a Select *. However, if I put a field of type timeStamp in the history table to receive the field from tblMain, the trigger will fail, obviously. What type should I use in the history table to accept a TimeStamp source?

Answer 1

From the docs:

A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

This works:

-- //Main table, with TIMESTAMP column
CREATE TABLE Main ( id INT, TIMESTAMP )

-- //Some values
INSERT Main VALUES ( 8, DEFAULT )
INSERT Main VALUES ( 4, DEFAULT )
INSERT Main VALUES ( 2, DEFAULT )
INSERT Main VALUES ( 7, DEFAULT )
INSERT Main VALUES ( 0, DEFAULT )

-- //See the values
SELECT * FROM Main

-- //History table
-- //with VARBINARY(8) to store a nullable TIMESTAMP
CREATE TABLE History (id INT, ts VARBINARY(8))

-- //Populate History table
INSERT History
SELECT * FROM Main

-- //See the values
SELECT * FROM History