SYSK 345: How To Assure Correct LastUpdatedDateTime Value in Your SQL Tables
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
For example:
CREATE trigger [Table1_UpdateTrigger]
on [dbo].[Table1] for update
as
begin
-- Make sure the timestamp and SQL user name are correct
update dbo.Table1
set LastUpdatedTimeStamp = getdate(),
LastUpdatedBySQLUser = suser_name()
where pk_id in (select pk_id from inserted)
end
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).
