Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    12

    Unanswered: 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?
    Nick

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...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!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •