I need to automatically update a datetime field for a record to the current time whenever the record is updated.
create table t (
id bigint identity(1,1) not null primary key,
ts datetime not null default getutcdate()
insert t (name, value) values ('fred', 'bob')
update t set value='robert' where id=1 and name='fred'
One option would be to use an instead of update trigger.
create trigger update_t on t
instead of update as
update t set ts=getutcdate(),name=inserted.name, value=inserted.value from t inner join inserted on t.id=inserted.id
update t set value='dick' where id=1 and name='fred'
Sounds like I've solved my own problem, heh? Well, here's the catch ... you can't know the names of the other columns at the time you write the trigger. I.e. you only know that there is a ts field that needs to be updated internally, otherwise you want the update to do the same thing it would normally do.