here's another test with the timestamp datatype:

/****** test script timestamp ******/

set nocount on

go

/*

@@DBTS returns the last-used timestamp value of the current database.

A new timestamp value is generated when a row with a timestamp column is inserted or updated.

*/

select @@DBTS

go

drop table dbo.t_test

go

print 'tabel dropped'

go

CREATE TABLE dbo.T_Test (

Sleutel int IDENTITY (1, 1) NOT NULL ,

Ms_Ts timestamp NOT NULL ,

Ms_Datetime datetime NOT NULL ,

Ms_Datetime_Last_Used datetimeNOT NULL ,

Ms_Char char (10) NOT NULL

)

GO

print 'Table reated'

go

ALTER TABLE dbo.T_Test WITH NOCHECK ADD

CONSTRAINT DF_T_Test_Ms_Datetime DEFAULT (getdate()) FOR Ms_Datetime,

CONSTRAINT DF_T_Test_Ms_Datetime_Last_Used DEFAULT (getdate()) FOR Ms_Datetime_Last_Used,

CONSTRAINT PK_T_Test PRIMARY KEY NONCLUSTERED

(

Sleutel

)

GO

print 'Constraints added'

go

--drop trigger TrU_Ms_Datetime_Last_Used

--go

CREATE TRIGGER TrU_Ms_Datetime_Last_Used ON T_Test

FOR UPDATE

AS

if not UPDATE(Ms_Datetime_Last_Used)

begin

update T_Test set Ms_Datetime_Last_Used = CURRENT_TIMESTAMP where sleutel = ( select sleutel from deleted )

end

go

print 'Trigger added'

insert into t_test (Ms_Char) values('a')

go

insert into t_test (Ms_Char) values('b')

go

Print 'Rows inserted'

go

select * from t_test

go

Print 'Update starts here...'

go

update t_test set ms_char = 'c' where sleutel = 1

go

Print 'Sleutel 1 updated ...'

go

select * from t_test

go

select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime

, CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime

from t_test

order by sleutel

go

update t_test set ms_char = 'D' where sleutel = 2

go

Print 'Sleutel 2 updated ...'

go

select * from t_test

go

select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime

, CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime

from t_test

order by sleutel

go