Update the Created and Modified columns

Nov 11, 2010 at 3:47 PM

If BaseTableDDL = 1 the update trigger takes on responsibility for updating the Created/Modified records.

 

 -- Update the Created and Modified columns
 IF @IsDirty = 1 AND @@NestLevel = 1
   UPDATE [Tracking].[Project]
     SET Created  = Deleted.Created, 
         CreatedBy = Deleted.CreatedBy, 
         Modified = @AuditTime, 
         ModifiedBy  = COALESCE(@UserName, Suser_SName()), 
        [RowVersion] = [Project].[RowVersion] + 1 
     FROM [Tracking].[Project]
       JOIN Inserted
         ON [Project].[Project_Id] = Inserted.[Project_Id]
       JOIN Deleted
         ON [Project].[Project_Id] = Deleted.[Project_Id]

 

There is a problem that arises when you have a stored procedure that calls an UPDATE on the table because then the @@NESTLEVEL would be 2 so the columns never get updated. If we remove the @@NESTLEVEL = 1 check and leave it with only @IsDirty = 1 everything should work as expected because @IsDirty is only set if any of the non-BaseTableDDL columns are modified.

Any thoughts ?