Trigger Magic? ROWVERSION and LastUpdateDate Trigger

• Jeffrey Williams

  SSC Guru

  Points: 90350

  More actions

  January 2, 2019 at 3:45 pm

  #2017807

  I am not following the reasoning for the issue - if you add the new column with a default value the previous rows will be NULL and only new rows added will have the new value.  Defining the new column with a default value allows that default value to populate the new column when those rows are inserted.

  The rowversion column will be updated during a normal update operations - the LastUpdateDate will be inserted with the default value.

  Use tempdb

   Drop Table dbo.TestTable;
  Go

  Create Table dbo.TestTable (
   id int
  , dataValue varchar(20)
  , rv rowversion
   );
  Go

  Insert Into dbo.TestTable (id, dataValue) Values (1, 'one');
Insert Into dbo.TestTable (id, dataValue) Values (2, 'two');
Insert Into dbo.TestTable (id, dataValue) Values (3, 'three');
Insert Into dbo.TestTable (id, dataValue) Values (4, 'four');
Insert Into dbo.TestTable (id, dataValue) Values (5, 'five');

  Select *
 From dbo.TestTable;
  Go

  Alter Table dbo.TestTable Add LastUpdateDate datetime2 Default sysutcdatetime();
  Go

  Create Trigger dbo.tr_TestTable
  On dbo.TestTable
After Update
  As
Begin
Update t
  Set t.LastUpdateDate = sysutcdatetime()
 From dbo.TestTable    t
Inner Join inserted    i On t.Id = i.Id;
  End
  Go

  Insert Into dbo.TestTable (id, dataValue) Values (11, 'one');
Insert Into dbo.TestTable (id, dataValue) Values (22, 'two');
Insert Into dbo.TestTable (id, dataValue) Values (33, 'three');
Insert Into dbo.TestTable (id, dataValue) Values (44, 'four');
Insert Into dbo.TestTable (id, dataValue) Values (55, 'five');

  Select *
 From dbo.TestTable;
  Go

  Update dbo.TestTable
  Set dataValue = 'one updated'
Where id In (1, 11);

  Select *
 From dbo.TestTable;
  Go
 

  The rowversion value may get updated twice - but since that value is only utilized to identify the row then that should not cause any problems.

  Am I missing something?

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• Rob Buecker

  SSChasing Mays

  Points: 639

  More actions

  January 3, 2019 at 6:35 am

  #2017845

  Jeffery, thanks for the reply and check on my logic.
  Through some iterations I lost track and conflated NOT NULL and DEFAULT value that was in the original iteration.
  The minor issue or problem is that rowversion gets updated twice.  Big picture, not really an issue; just not "clean" coding.  Seems like it is just a trade off and some technical debt on how and where the logic will reside.