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:

  1. Create an after trigger on the table for update
  2. 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).