Often, developers add LastUpdatedDataTime (or LastUpdatedTimestamp) and LastUpdatedByUser columns to tables in relational databases.
And, as one might guess, these fields are often created to be not nullable with default values set by using SQL functions – getdate() and suser_name() – correspondingly.
The problem is that the default values only work on inserts, so, if the caller of your stored procedure (or TSQL statement) did not specify those columns in the UPDATE statement, then you’ll have stale (i.e. incorrect) data.
Here is what I do in such situation:
- Create an after trigger on the table for update
- Override those fields with the right values
CREATE trigger [Table1_UpdateTrigger]
on [dbo].[Table1] for update
— Make sure the timestamp and SQL user name are correct
set LastUpdatedTimeStamp = getdate(),
LastUpdatedBySQLUser = suser_name()
where pk_id in (select pk_id from inserted)
NOTE: If your SQL user is not same as the application end user, you may want to have two LastUpdatedBy columns – LastUpdatedBySQLUser and LastUpdatedByAppUser (must be passed in from the tier where the end user identity is known).