CODEWITHSUNDEEP
sqltimestamprowversion
scorpiophd
scorpiophd

Reputation: 105

timestamp, rowversion and datetime - I need a mark when a row is inserted or updated

I have the following requirement:

I have a table with 1 unique auto-incremental Int ID and data columns. I need the following: Every time a row is inserted into that table, a column at the right end of the table must hold the full datetime of that insert. Also, if a row is updated I need that column that holds the full datetime of the insert of that row into the table, to be updated to hold the update time for that row.

Now the obvious and very straightforward way to do this is: you create your table:

create table My_Test_Table
(my_id int identity not null,
my_data nvarchar(max));

you alter your table adding the datetime column and a Default constraint on it:

ALTER TABLE My_Test_Table
ADD [timestamp] datetime;
ALTER TABLE My_Test_Table
ADD CONSTRAINT DF_My_Test_Table_timestamp DEFAULT GETDATE() FOR [timestamp];

then you make a nice trigger for update, like so:

CREATE TRIGGER DML_Trigger_update_My_Test_Table
ON My_Test_Table
FOR UPDATE
AS
IF @@ROWCOUNT <> 0
BEGIN
IF EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED)
BEGIN
UPDATE My_Test_Table
SET [timestamp] = GETDATE()
FROM INSERTED
WHERE My_Test_Table.my_id = INSERTED.my_id;
END
END

Now the tricky part is: I want, for reasons that are beyond scope here, to implement this exact thing as above but without a Trigger! Is it possible? I do not want to use the SQL type timestamp or rowversion, this won't work for me, I need the date, time down to the milliseconds to be clearly stored in that column. Any ideas would be much appreciated.

Upvotes: 0

Views: 1231

Answers (1)

gbn
gbn

Reputation: 432311

You don't need a trigger

You can use the DEFAULT keyword as the source value in the UPDATE statement to use, well, the DEFAULT constraint defined on that column

UPDATE
   MyTable
SET
   foo = ...,
   bar = ...,
   ChangedDateTime = DEFAULT
WHERE
   ...;

I wouldn't use a column called timestamp because this has meaning on SQL Server, as a synonym for rowversion. For SQL Server 2008+ use datetime2(3) to accurately record milliseconds. The "old" datetime is accurate to a rounded 3.33 milliseconds only

Upvotes: 4

Related Questions