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).


 


 

Comments (4)

  1. Unless the data is pure eye candy, you might also want to consider using getutcdate rather than getdate. 😉

  2. Brian Dukes says:

    the update should include

    <b>where PrimaryKey in (select PrimaryKey from inserted)</b>

    if you want to keep track of each rows changes.

  3. Oleg says:

    and don’t forget to join to the INSERTED table on the PK, otherwise you’ll update ALL the rows in Table1 …  N sure that’s the effect you were going for 😉

    your trigger should also handle "inserts" to make sure users don’t specify non-default values for your audit columns

  4. irenake says:

    Sure thing…  Thanks!

Skip to main content