Article Link.

Isn't it there's a special data type called "timestamp" ? Maybe you could create a field called DateUpdated and make it a "timestamp" data type?

This is a special value that is unique within a given database. The value is set by the database itself automatically every time the record is either inserted or updated - even if the timestamp column wasn't referred to by the UPDATE statement. - From SQL Server 7.0 Programming.

Ruel
ruel@web10hosting.com

you can just make a datetime field in the tables, and use a default value of getdate() (You do this in the design window or in your t-sql code).

when you insert records into it you don't need to specify the field (and shouldn't) and the datetimes will be put there automatically. if there is existing data in the tables then this will leave them null.

the problem with this approach is that the datetime values are not guaranteed to be unique, where a timestamp is guaranteed to be unique database wide.

it's really up to you.

This could be potentially dangerous so i left out the call that will actually alter the tables, but you can see the output via Query Analyzer and run it without harming anything.

This would generate a datetime field in all of your tables and assign a default value of getdate.

Just use your noggin here, definetly don't cut and paste this and run it until you understand what it would do but hopefully this gives you some ideas.

You can easily generate the code needed to write all the update triggers as well.

There is prolly a MUCH better way of accomplishing this fyi!

--

set nocount on
use pubs
declare @table varchar(200)
declare @dynamicsql nvarchar(4000)
declare testcur cursor for

select name from sysobjects where xtype = 'u'

open testcur

fetch next from testcur into @table

while @@fetch_status =0
begin
set @dynamicsql = 'alter table [' + @table + '] ADD DATEFIELD datetime null DEFAULT Getdate()'
print @dynamicsql
-- exec(@dynamicsql) -- if you uncomment this line, your database will be changed!!!
fetch next from testcur into @table
end

close testcur
deallocate testcur