Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005
    Posts
    5

    Unanswered: Automatically update datetime field in a database table.

    hi e'body:

    I have some database tables, and each one of them have a creation_date and modified_date in them. I was trying to figure out a way where when a row in one of these tables is changed using Enterprise Manager (Database -> Tables -> select table -> right click -> select all rows -> change a field in a row inside a table), is there a way apart from triggers, such that the "modified_date" column for that row get changed to 'getdate()' (rather picks up the current datetime).

    thanks in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is the generic trigger I use to record who modified a record and when:
    Code:
    CREATE TRIGGER TR_[TABLENAME]_U ON dbo.[TABLENAME]
    FOR UPDATE
    AS
    set nocount on
    update	[TABLENAME]
    set	Modified = getdate(),
    	Modifier = isnull(inserted.Modifier, (convert(nvarchar(50),suser_sname())))
    from	[TABLENAME]
    	inner join Inserted on TABLENAME.PKey = Inserted.PKey
    set nocount off
    Note that this trigger is for update only. For inserts, you should have default values defined on the table.
    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
  •