Unanswered: Automatically update datetime field in a database table.
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).
Here is the generic trigger I use to record who modified a record and when:
CREATE TRIGGER TR_[TABLENAME]_U ON dbo.[TABLENAME]
set nocount on
set Modified = getdate(),
Modifier = isnull(inserted.Modifier, (convert(nvarchar(50),suser_sname())))
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.