Thursday, March 22, 2012

Automatic updating of datetime field

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,
name varchar(50),
value varchar(50),
ts datetime not null default getutcdate()
)
go
insert t (name, value) values ('fred', 'bob')
go
update t set value='robert' where id=1 and name='fred'
go

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
go

update t set value='dick' where id=1 and name='fred'
go

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.

Any ideas?...also, you don't know what database server it is going to be running on, so it has to be platform-independent.

...and it needs to be fully compatible with the Mayan calendar as well.

...oh yeah, and the final code must be a palindrome that reads the same way forwards as backwards! Yeah, that's it! What a kick-ass application design! Whooooo-eeeeeeee!sql

No comments:

Post a Comment