--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([ID] int,[date] datetime,[checkdate] datetime)
insert [test]
select 1,'2010-08-10 00:00:00.000',null union all
select 2,'2010-08-10 00:00:00.000',null union all
select 3,'2010-08-10 00:00:00.000',null


go
if OBJECT_ID('tri_test')is not null
drop trigger tro_test
go
create trigger tri_test on test
for update
as
update test
set [checkdate]=getdate() from inserted a where a.id=test.id

--测试
update test
set [date]='2012-03-21 00:00:00.000' where id=1

select * from test
/*
ID    date    checkdate
1    2012-03-21 00:00:00.000    2012-04-30 10:37:50.420
2    2010-08-10 00:00:00.000    NULL
3    2010-08-10 00:00:00.000    NULL
*/

------解决方案--------------------

SQL code

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([ID] int,[date] date,[checkdate] datetime)
insert [test]
select 1,'2010-08-10',null union all
select 2,'2010-08-10',null union all
select 3,'2010-08-10',null


go
if OBJECT_ID('tri_test')is not null
drop trigger tro_test
go
create trigger tri_test on test
for insert,update
as
update test
set [checkdate]=getdate() from inserted a where a.id=test.id

--测试
update test
set [date]='2012-03-21 00:00:00.000' where id=1
insert test select 4,getdate(),null
update test set [date]=getdate()
select * from test
/*
ID    date    checkdate
1    2012-04-30    2012-04-30 10:42:19.343
2    2012-04-30    2012-04-30 10:42:19.343
3    2012-04-30    2012-04-30 10:42:19.343
4    2012-04-30    2012-04-30 10:42:19.343
*/

------解决方案--------------------
1楼的方法最简单，顶一下。2、3楼也行。