Freigeben über

SYSK 345: How To Assure Correct LastUpdatedDateTime Value in Your SQL Tables

  • Artikel

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

  • Anonymous
    May 08, 2007
    Unless the data is pure eye candy, you might also want to consider using getutcdate rather than getdate. ;)

  • Anonymous
    May 08, 2007
    the update should include <b>where PrimaryKey in (select PrimaryKey from inserted)</b> if you want to keep track of each rows changes.

  • Anonymous
    May 08, 2007
    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

  • Anonymous
    May 08, 2007
    Sure thing...  Thanks!